SQL-Server database migration

The problem

Recently I upgraded my main development machine to SQL-Server 2012, however my old back dev-box still has Sql-Server 2008 R2. In case my main box fails, I like to be able to use the backup-box right away.

One problem would be to restore the backups of my current databases. We know we can not restore 2012 bak files onto a 2008R2 or any older version. So I could set up a temp instance of 2012, restore my backups and then copy them over to the 2008 R2 server. This would be a lot of work and I wanted something more automated.

A tool to script the database

I looked around for some solutions to script my databases and then rebuild them on the older server.
There are several tools, some closed source but also several on CodePlex or GitHub. As usual some of them were abandoned and others did not supported all the features I need.

I settled for the 'SQL Database Migration Wizard' is primarily use is to move a on-premise database to Azure SQL Database.
It is actively maintained by Microsoft and the source code is available. A very nice feature is that it also migrates the data, not just the schema.

I downloaded the two SQLAzureMWBatch binaries, one for backup and one for upload (retore). Just extract them both into a new directory.

You may want to change some of the settings in the *.config files.
SQLAzureMWBatchBackup.exe creates a *.sql with with the schema and in the same directory n files with the data of all the tables.
SQLAzureMWBatchUpload.exe takes these files and applies them against another database. As long as you don't use new features specific to 2012, you can apply these files against a 2008 (R2) server.

What's missing?
There are three areas which are not covered by the tool (There may be more, but these are the ones I noticed).
  1. Users and permissions on objects
  2. Extented properties
  3. Fulltext search settings
One option would be to edit the source code of the tool and add these features, but I just used some SQL to create scripts which I also need to apply when 'restoring' the database.
A PowerShell script to combine all tasks
The script can be used to backup or restore a database, or both in the same process. It also zips up all the files so you can archive them without restoring.
It create the additional scripts, applies them and also cleans up after itself.
param(
[parameter(Position=0, Mandatory=$true, HelpMessage="The name of the database to process")]
[string]$dbName,
[parameter(HelpMessage="Directory under which all temp files are stored")]
[string]$baseDir = "$env:temp",
[parameter(HelpMessage="The SQL server with the source database")]
[string]$sourceServer = ".",
[parameter(HelpMessage="The SQL server to restore to")]
[string]$targetServer,
[parameter(Mandatory=$true, HelpMessage="The directory the sql azure tools are located")]
[string]$helperToolDir,
[parameter(HelpMessage="Run the backup portion of the script")]
[switch]$backup,
[parameter(HelpMessage="Run the restore portion of the script")]
[switch]$restore,
[parameter(HelpMessage="Delete the directory with script/data files when done")]
[switch]$cleanup,
[parameter(HelpMessage="Zip the script/data files into one file")]
[switch]$zip
)

[string]$outputDir = $baseDir + "\" + $dbName

$createSQL = @"

if db_id('$dbName') is not null 
BEGIN;
    DROP DATABASE [$dbName];
END;
GO
CREATE DATABASE [$dbName]; 
GO
"@

$permSQL = @"
    SET NOCOUNT ON;
    SELECT 'GRANT ' + dp.permission_name collate latin1_general_cs_as
    + ' ON ' + s.name + '.' + o.name + ' TO ' + dpr.name 
    FROM sys.database_permissions AS dp
    INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
    INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
    INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
    WHERE dpr.name NOT IN ('public','guest');
"@

$propSQL = @"
SET NOCOUNT ON;
SELECT 'EXECUTE sp_addextendedproperty @name = N''MS_Description'', @value = N''' + CAST(ep.Value AS nvarchar(500)) + ''', @level0type = N''SCHEMA'', @level0name = N''' + s.name + ''', @level1type = N''TABLE'', @level1name = N''' + OBJECT_NAME(ep.major_id) + ''', @level2type = N''COLUMN'', @level2name = N''' + c.Name + ''';'
FROM sys.extended_properties AS ep
INNER JOIN [sys].[tables] as t ON ep.major_id = t.object_id 
INNER JOIN sys.schemas as s ON s.schema_id = t.schema_id 
INNER JOIN sys.columns as c ON ep.major_id = c.object_id and ep.minor_id = c.column_id 
WHERE ep.name = 'MS_Description' and ep.class = 1
ORDER BY OBJECT_NAME(ep.major_id) 
"@

$SetPermissionsFile = $outputDir + "\SetPermissions.sql"
$SetPropertiesFile = $outputDir + "\SetProperties.sql"
$NewDatabaseFile = $outputDir + "\NewDatabase.sql"

# script permissions and extended properties

if ($backup)
{
    if (Test-Path $outputDir)
    {
        Get-ChildItem $outputDir -Recurse | Remove-Item -Force
    }
    else
    {
       md $outputDir 
    }
    
    sqlcmd.exe -E -S $sourceServer -d $dbName -Q $permSQL -o $SetPermissionsFile -W -h -1 -w 1000       
    sqlcmd.exe -E -S $sourceServer -d $dbName -Q $propSQL -o $SetPropertiesFile -W -h -1 -w 1000    
    $createSQL | Set-Content -Path $NewDatabaseFile

    # use Azure Migration tool to export basic schema and data
    pushd $helperToolDir
     & .\SQLAzureMWBatchBackup.exe -D $dbName -O $outputDir -a false
    popd

    if ($zip)
    {
        [System.Reflection.Assembly]::LoadWithPartialName("System.IO.Compression.FileSystem") | Out-Null
        $compressionLevel = [System.IO.Compression.CompressionLevel]::Optimal
        $pathToZip = $baseDir + "\" + $dbName + "_" + (Get-Date).ToString("yyyyMMdd-HHmmss") + ".zip"
        [System.IO.Compression.ZipFile]::CreateFromDirectory($outputDir,$pathToZip,$compressionLevel,$true)
    }
}

if ($restore)
{
    if (!(Test-Path $outputDir))
    {
        Write-Warning "Directory $outputDir not found"
        exit 1
    }

    # run the create file
    sqlcmd.exe -E -S $targetServer -d "master" -i $NewDatabaseFile

    $restoreResultsFile = $outputDir + "\restorerestults.txt"
    $inputFile = $outputDir + "\" + $dbName  + ".sql"

    # run azure tools
    pushd $helperToolDir
     & .\SQLAzureMWBatchUpload.exe -D $dbName -i $inputFile -T -S $targetServer -o $restoreResultsFile
    popd

    # appy additional sql
    sqlcmd.exe -E -S $targetServer -d $dbName -i $SetPermissionsFile
    sqlcmd.exe -E -S $targetServer -d $dbName -i $SetPropertiesFile
}

if ($cleanup)
{
    Get-ChildItem $outputDir -Recurse | Remove-Item -Force
    Remove-Item $outputDir
}

<# 
   .SYNOPSIS
   Script to backup and restore SQL Server databases
   
   .DESCRIPTION
   Copy databases from a newer to an older version of SQL Server. Requires SQL Azure Migration tools
   and dot.NET Framework 4.5   
#>
Save the script to Migrate-Database.ps1 in your scripts directory and call it like this:
.\Migrate-Database.ps1 -backup -restore -zip -cleanup -dbname "dbname" -baseDir "X:\sqlbackup" -targetServer "2008Server" -helperToolDir "X:\tools\SQLAzureMigration"

Pages in this section