Stuart Moore

Musings of a Data professional

Stuart Moore

Tag: powershell

Migrating Databases From SQL Server 2000 to SQL Server 2012 using PowerShell

By

On August 14, 2013

In powershell, sql server

PowerShell IconI seem to have a spate of 3rd party applications finally moving from SQL Server 2000 to a version that’s a lot newer (SQL Server 2012). This means a lot of database migration, and as they want to keep the applications up while they test the new versions this means using backup and restore.

As Microsoft is only supporting restoring backups from the previous 3 versions (SQL Server 2005, 2008 and 2008R2) with SQL Server 2012 this the process actually has to go like this:

  1. Backup the SQL Sever 2000 database
  2. Restore the database onto an instance of SQL Sever 2005, 2008 or 2008R2
  3. “Upgrade” database by setting Compatibility Level to new Server version
  4. Backup new database
  5. Restore database onto SQL Server 2012 instance
  6. “Upgrade” database by setting Compatibility Level to new Server version

Which to me is a lot of manual handling for quite a lot of databases. And did I mention that steps 2 and 5 will probably also mean relocating data and log files (potentially multiples of each), and some full text indexes as well for good measure? All of which makes this an unappetising prospect to do manually.

So time for some PowerShell automation. First off we import our good friend the SQLPS module, and then define 2 simple functions:

Import-Module SQLPS -DisableNameChecking

set-location c:\

function DataBase-Restore
{
param([String]$SQLServerName, [String]$DataBaseName, [String]$BackupFilePath, [String]$RestorePath, [Boolean]$Upgrade=$FALSE)

    $sqlsvr_rest = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server ($SQLServerName)
    $restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
    $devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

    $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($BackupFilePath,$devicetype)
    $restore.Database = $DataBaseName
    $restore.replacedatabase = $false

    $restore.Devices.add($restoredevice)

    $restore.Devices.add($restoredevice)
    if ($RestorePath.Length -gt 0){
        $restorefiles = $restore.ReadFileList($sqlsvr_rest)
        foreach($file in $restorefiles){
	        $rf = new-object -typename Microsoft.SqlServer.Management.Smo.RelocateFile
	        $rf.LogicalFileName = $file.logicalname
	        $rf.PhysicalFileName = $RestorePath+(split-path $file.PhysicalName -Leaf)
	        $restore.RelocateFiles.Add($rf)
	        Remove-Variable rf
        }
    }

    $restore.sqlrestore($sqlsvr_rest)
    $restore.Devices.Remove($restoredevice)
    if ($Upgrade -eq $TRUE){
        $sqlsvr_rest.Databases.Item($DataBaseName).CompatabilityVersion = $sqlsvr_rest.Databases.Item("Model").CompatabilityVersion
    }
}

function Database-Backup
{
param([String]$SQLServerName, [String]$DataBaseName, [String]$BackupFilePath)
    $sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($SQLServerName)
    $backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
    $backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
    $backup.Database = $DataBaseName
    $backupname = $BackupFilePath

    $devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

    $backupdevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $backupname,$devicetype
    $backup.Devices.Add($backupdevice)
    $backup.SqlBackup($sqlsvr)
    $backup.Devices.Remove($backupdevice)

}

The eagle eyed amongst you will note Set-Location c:\ just after the import-module. That’s because the last thing SQLPS does is Set-Location sqlserver:\ to move to the SQLSERVER drive, which will make some of the later file copying much trickier, so we just make sure we’re back on a file drive.

First function Database-Restore takes the following parameters:

  • SQLServer Name – string of Server Name (and Instance Name if it’s a Named Instance)
  • DatabaseName – string of your Database Name
  • BackupFilePath – string of where the backup you want to restore is held
  • RestorePath – the path you want to move restored files to
  • Upgrade – optional boolean parameter to determine whether to upgrade the restored database to the server’s current compatibilitylevel

Nothing too unusual in the function, going through it quickly we perform the following operations:

  • Create a connection to SQL Server
  • Create a restore object, set it’s database, build a restore device and add our backup file to it. We set the option not to overwrite an existing database, just in case…..
  • If we’ve passed in a folder to move them to, then Read a list of files from the backup file, then loop through it creating a File Relocation object for every one of them, and adding them to our Restore object
  • Perform the actual restore (line 31)
  • Then if we’ve set Upgrade to TRUE we set the compatibility level equal to that of Model, and then call AlterDB() to write it back

The second function Database-Backup takes the following parameters:

  • SQLServer Name – string of Server Name (and Instance Name if it’s a Named Instance)
  • DatabaseName – string of your Database Name
  • BackupFilePath – string of where to put the backup.

So how are these going to help us migrate our databases? Simple, we can call them repeatedly and leave them running while we get on with something more interesting. If you’re lucky enough to have a shared backup drive that all your SQL Server Instances can read and write to then the upgrade is as easy as:

Database-Backup -SQLServerName "SQL2000\InstanceA" -DatabaseName "App1" -BackupFilePath "\\shared\backupdrive$\app1-2000.bak"
Database-Restore -SQLServerName "SQL2008" -DatabaseName "App1" -BackupFilePath "\\shared\backupdrive$\app1-2000.bak" -RestorePath "e:\dbs" -Upgrade $TRUE
Database-Backup -SQLServerName "SQL2008" -DatabaseName "App1" -BackupFilePath "\\shared\backupdrive$\app1-2005.bak"
Database-Restore -SQLServerName "SQL2012\InstanceB" -DatabaseName "App1" -BackupFilePath "\\shared\backupdrive$\app1-2005.bak" -RestorePath "f:\dbs" -Upgrade $TRUE

If not, then you’ll have to do some file moving yourself like so:

Database-Backup -SQLServerName "SQL2000\InstanceA" -DatabaseName "App1" -BackupFilePath "g:\backups\app1-2000.bak"
Copy-Item "\\SQL2000\g$\backups\app1-2000.bak" "\\SQL2008\f$\backups\app1-2000.bak"
Database-Restore -SQLServerName "SQL2008" -DatabaseName "App1" -BackupFilePath "f:\backups\app1-2000.bak" -RestorePath "e:\dbs" -Upgrade $TRUE
Database-Backup -SQLServerName "SQL2008" -DatabaseName "App1" -BackupFilePath "f:\backups\app1-2008.bak"
Copy-Item "\\SQL2008\f$\backups\app1-2005.bak" "\\SQL2012\f$\backups\app1-2005.bak"
Database-Restore -SQLServerName "SQL2012\InstanceB" -DatabaseName "App1" -BackupFilePath "f:\backups\app1-2005.bak" -RestorePath "f:\dbs" -Upgrade $TRUE

When placing the files remember that the script is running as the Windows account calling it, but the SQL Sever backups and restores will be running as the Database Engine Account.

Now I can happily migrate those databases to a happier place repeatedly without having to take time out from other larger projects. Win for me, and win for the people I’m working for.

Downloads for “Automating SQL Server Backups and Restores with PowerShell” presentation from Southampton SQL Sever User Group 07/08/2013

By

On August 9, 2013

In community, powershell, speaking, sql server

Hi, if you’ve just landed here you might be interested in the series of posts >31 days of SQL Server Backup and Restore with PowerShell where I’ll be providing more information about the concepts and scripts in this presentation

Scripts and Demos for PowerShell SQL Server Backup and Restore presentation given at Southampton SQL Server User Group 7th August 2013

Includes the presentation as a pdf, all the PowerShell scripts used during the presentation, the SQL scripts to build the demo databases, plus the backups of a couple of the databases to speed things up.

Readme.txt gives a quick overview of each script and the order to run through them in.

Any problems or Questions, please get in touch.

Powershell to move Windows Roles and Features between servers

By

On July 26, 2013

In powershell, Uncategorized

I’ve been working a lot with building some test and development environments for systems that didn’t have them. And as those ‘unimportant’ features have been missed out, it wasn’t too suprising to find that the Build documentation for the servers was less than stellar.

So, how could I make sure that the test and dev boxes had the same Windows Roles and Features without wasting too much time. Well, PowerShell is quickly becoming my default answer to anything like this. And it turns out there’s a nice 1 liner you can put together to do it as well (well, 1 liner if you’ve already got the module imported 🙂 )

 

Import-Module ServerManager
Get-WindowsFeature | Where-Object {$_.Installed -eq $True} | Install-WindowsFeature -computername devbox

First up we import the ServerManager module, it’s loaded by default if you’re running Windows Server 2012 but it’s always good practice to make sure you’ve got it.

Get-WindowsFeature returns a list of all the available Roles and Features on the server, we filter that with Where-Object so we only have those roles and features that are installed. Then we pass the whole lot to Install-WindowsFeature and tell it to install it onto the remote box.

And the nice feature of Install-WindowsFeature is that it will only install anything that’s missing, so this snippet can be used to check for missing sub features

SQL Southwest User Group presentation on Automating SQL Sever backups, restores and verifications with PowerShell

By

On July 19, 2013

In community, powershell, speaking, sql server


Just a quick post to put up the slides from a presentation I did at SQL Southwest. Also here are the demo scripts to go along with the presentation – Demo scripts from powershell backup presentation July 2013

Hopefully everything’s self explanatory, but if you want to get in touch with any questions then please do,

Add-WindowsFeature cmdlet fails with the error code 0x800f0922 adding IIS role/feature

By

On July 7, 2013

In powershell, Uncategorized

add-windowsfeature-error

I was working on trying install SSI (Server Side Includes) on an Windows 2012 IIS8 box, set up by someone else. Doing everything the usual way, I was using PowerShell (as I do for all server admining this days, just so much faster) like so:

import-module ServerManager
Add-WindowsFeature web-includes

Only to get the wonderful response shown above in the screenshot. Not very informative really. So I double checked the syntax, but that looked fine. Tried going through GUI to do it, same error just took longer to pop up. Spun up a Windows 2012 VM I had kicking around on my laptop and ran the same snippet, success. So what could it be. Googling showed some MS notes that didn’t offer much information that seemed relevant to this particular case, and in once case seemed to suggest a complete rebuild as the error was due to a corrupted system/iis config issue.

Not fancying a rebuild I thought I’d delve deeper into the IIS configuration to see if there was anything that could be manually fixed. Doing this I discovered that the box had once been part of a shared config setup across a number of boxes, and was now the only one left. BUT, it was still set up with a shared config folder. That was quickly removed and the config exported locally. Reran the Add-WindowsFeature, and this time everything added itself properly.

So it looks quite likely that the error is a catch all (for IIS at least) when the process can’t read or get a lock on the IIS config for one reason or another. So if it crops up checking through the config files for anything funny is probably a very good first step.

Calling a PowerShell function in a Start-Job script block when it’s defined in the same script

By

On June 30, 2013

In powershell

While writing some scripts some upcoming SQL Server User Group presentations I had to remind myself of how to do something in PowerShell, and thought I’d put a reminder up here for myself and for anyone else who needs it.

The problem was that I wanted to show firing off multiple SQL Server backup jobs asynchronously from within a PowerShell script. Sounds nice and simple, write some custom modules, and then fire them off with Start-Job as required. And while that’s the best way to do things (PowerShell rule1: Write functions, not scripts), in this case I wanted to keep everything in one script as it would make it easier when presenting

So the basics set up is:

function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}

while($i>$x){
    Start-Job -ScriptBlock <WhatDoIPutHere?>
}

Normally I’d just add the other script or module in there, but what about the function? Well, the trick is to pass the function in as a variable to InitializationScript, and then call it in ScriptBlock like so:

$func = {function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}
}

while($i>$x){
    Start-Job -ScriptBlock {backup_db_id} -InitializationScript $func
}

So that’s the first step. But I also want to pass through the 2 variables, so I’ll need to parametise the function pass. Which means stacking things like this:

$func = {function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}
}

while($i>$x){
    Start-Job -ScriptBlock {param($tm,$ti) backup_db_id $tm $ti } -InitializationScript $func -ArgumentList($x,$i)
    $i++
}

And now everything is passed through happily.

The other thing to note from the function definition here, is that I’ve had to include the Import-Module to load the SQLPS extensions as PowerShell will be starting up a new instance for every Start-Job and that instance won’t have the SQLPS extensions loaded up (unless you’ve set it as a system wide default, but I don’t always want to rely on that).

Page 5 of 5

Previous

Powered by WordPress & Theme by Anders Norén