Uncategorized

Powershell to drop out create table scripts from a text file

So you’ve been handed a list of tables that need to be migrated

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

SQL Server, SFTP and SCP

A common requirement for SQL Server DBAs is sending or fetching data from remote unix/linux systems. And quite rightly the customers want their data transmitting securely.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Powershell script to build up restore commands for SQL Server

I often need to restore a standby server up to the latest production restore, either for testing or because I to get it ready for mirroring. As it’s on remote server there’s no easy list to click on in SQL Server Management Studio. So it’s time to look at the directory listing, and then play around with it in a text editor. With transaction logs every 5 minutes just getting close to completing the editing in time is a pain in the proverbial. So deciding to go the easy route (always my preference), here’s a powershell script that’ll build the statements for you:

$dbname=$args[0]
$folder=$args[1]
$start="restore database $dbname from disk='$folder"
$end="' with norecovery"

$holder = dir $folder *.bak
$tholder = dir $folder *.trn
"$start{0}$end" -f $holder[-1].name
$tholder | where {$_.lastwritetime -gt $holder[-1].lastwritetime} | foreach {"$start$_$end"}

Nothing too fancy there. I tend to keep more than a days worth of backups on disk if possible, so it looks in the specified folder for the oldest .bak file, and then lists all the .trn files created since then.

I normally just call it as:

./restore.ps1 ProdDB \\backupserver\backupshare$\proddb_backups > out.txt

If you wanted to be brave you could pipe the output into osql to avoid a cut ‘n’ paste, but I’m not usually that keen. For anything of an interesting size or backup frequency you’ll probably have to run it a couple of times to get up to date.

I’ve one in the works that talks to a SQL instance to get data file locations and the puts in the moves if needed. But it’s not quite ready for public eyes yet

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

End of working 2011

Well, just about to wrap up working for 2011. For the first time in years I’ve actually got a completely clear christmas break with no planned work. And hopefully the last year’s work will help ensure no unplanned work callouts either.

Looking forward to 2012; I’ve just sorted my registration for all 3 days of SQLBits in London at the end of March, and it’s looking likely that I’ve scored some Dynamics CRM 2011 training off the back of a project.

Sort of dreading having to look at rebuying my ‘work’ library next year as tle SQL 2012 books start hitting the shelves. There is a temptation to look at an E-reader to save on space/cost, but the Kindle has never really impressed, and initial reports on the Kindle Fire for technical documents aren’t promising, this could finally push me over the edge in iPad ownership.

One of the last jobs for this year was setting up mirroring for an application between 2 geo-clustered SQL installs. Really looking forward to the AlwaysOn availability groups coming in SQL Server 2012 as it looks like it will remove a lot of the pain and overhead involved in these sort of scenarios, (In fact I’ve picked this session “AlwaysOn: Maximizing High Availability with SQL Server 2012” by Allan Hirt for my Thursday session at SQLBits.

About