SQL-Server database migration

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.

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.

1. Users and permissions on objects
2. Extented properties
3. Fulltext search settings

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   
#>

.\Migrate-Database.ps1 -backup -restore -zip -cleanup -dbname "dbname" -baseDir "X:\sqlbackup" -targetServer "2008Server" -helperToolDir "X:\tools\SQLAzureMigration"