Category: powershell Page 6 of 7

I 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.