Copy MSSQL database to another server

Copying database from one environment to another is one repetitive task I have to do all the time. It’s always a long task for some reason  and usually goes through the following.

  1. SQL Management studio to source server
  2. Create a backup and put it somewhere locally
  3. Copy that bak file to the remote server
  4. Log to the remote server using mgmt. studio.
  5. Check the correct options for restore (Overwrite existing backup, set database in single user mode, etc..)
  6. Restore the database.

No matter between which environment I need to copy database, the steps are always the same. So I Wrote this power shell script to automate the process. And created a ps1 file for each environment I usually do migrate from / to.

Dev_to_preprod.ps1

.\sqlClone.ps1 -sourceCstr “Data Source=pgidev;Initial Catalog=Pgi;User ID=sa;Password=saPa55” -sourceDatabase “SomeDB” -sharedUNCPath “\\SomeSRV\SharedFolder$\” -destDatabase “Pgi” -destCstr “Data Source=server\SQLEXPRESS;Initial Catalog=MyDb;User ID=sa;Password=password!”

You need to set up a shared network drive available from your source and destination sql server database.

 

Enjoy one click database copy !

##############################################
# SQL Clone utility 
##############################################
# Version : 0.1
# Date : 2016-07-06
# Created by : David Berube
# Contact : david@newsoft.io
# Licence : MIT License
##############################################
# 
# This utility will create a backup (.bak) of a given MSSQL database to a path (UNC, Mapped drive or local)
# And restore the .bak to a given database. 
# 
# You can restore from two different SQL Server instance, as long as both has access to the provided shared path
#
# Usage : 
# .\sqlCopy.ps1 -sourceCstr "Data Source=SourceDB;Initial Catalog=SourceDB;User ID=sa;Password=******" -sourceDatabase "SourceDB" -sharedUNCPath "\\BackupServer\pgi$\" -destDatabase "DestDB" -destCstr "Data Source=DestDB;Initial Catalog=DestDB;User ID=sa;Password=******"
##############################################
Param(
    [string]$sharedUNCPath,
    [string]$sourceCstr,
    [string]$sourceDatabase,
    [string]$destCstr,
    [string]$destDatabase,
    [bool]$closeExistingConnections=1,
    [string]$backupFileName,
    [boolean]$backupTargetDatabase=0
)

# Initializing options
$options = [CloneOptions]::new()
$options.sharedUNCPath = $sharedUNCPath
$options.sourceCstr= $sourceCstr
$options.sourceDatabase = $sourceDatabase
$options.destCstr = $destCstr
$options.destDatabase = $destDatabase
$options.closeExistingConnections=1
$options.backupFileName = $backupFileName

# Check and default values 
if($options.backupFileName.Equals("")) 
{
    $fileDateTime = Get-Date -Format FileDateTime
    $backupFileName = $options.sourceDatabase + $fileDateTime + ".bak"
}elseif (!$backupFileName.EndsWith(".bak"))
{
    throw "Backup should end with .bak extenstion"
}

if($options.sharedUNCPath.Equals(""))
{
    throw "Specify a shared UNC path OR mapped drive, common to both SQL Instances"
}
if(!$options.sharedUNCPath.EndsWith("\"))
{
    $options.sharedUNCPath = $options.sharedUNCPath + "\"
}

$options.fullBackupPath = $options.sharedUNCPath + $backupFileName


#TODO: Add option to backup target database
if($options.backupTargetDatabase -eq 1)
{
    #[CloneHelper]::Backup($options.destCstr,$options.destDatabase);

}

[CloneHelper]::Backup($options)

[CloneHelper]::Restore($options)

class CloneOptions
{
    [string]$sharedUNCPath
    [string]$sourceCstr
    [string]$sourceDatabase
    [string]$destCstr
    [string]$destDatabase
    [bool]$closeExistingConnections=1
    [string]$backupFileName
    [string]$fullBackupPath
    [boolean]$backupTargetDatabase=0
    [boolean]$copyDatabaseSchema=0
}

class CloneHelper 
{
    static [Void] Backup([CloneOptions]$options) {
        $sqlCommand = [CloneHelper]::BuildBackupSql($options)
        [CloneHelper]::ExecuteSql($options.sourceCstr,$sqlCommand)
    }

    static [bool] ExecuteSql([string]$cStr,[string]$sqlCommand)
    {
        $connection = new-object system.data.SqlClient.SQLConnection($cStr)
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Host "$($_)"}
        $connection.add_InfoMessage($handler)

        $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
        $connection.Open()
        if ($command.ExecuteNonQuery() -ne -1)
        {
            echo "Failed";
            return 0;
        }
        $connection.Close();
        return 1;

    }
    static [string] BuildBackupSql([CloneOptions]$options)
    {
      $sqlCmd = "BACKUP DATABASE [" + $options.sourceDatabase +"] TO DISK = N'" + $options.fullBackupPath + "' WITH NOFORMAT, NOINIT,  NAME = N'" + $options.backupFileName  +"-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"

      Write-Host "Backup command : " $sqlCmd
      
      return $sqlCmd
    }

    static [Void] Restore([CloneOptions]$options) {
        $sqlCommand = [CloneHelper]::BuildRestoreSql($options)
        [CloneHelper]::ExecuteSql($options.destCstr,$sqlCommand)
    }

    static [string] BuildRestoreSql([CloneOptions]$options)
    {
      $sqlCmd =  "USE [Master]; "
      if($options.closeExistingConnections -eq 1){
        $sqlCmd += "ALTER DATABASE [" + $options.destDatabase + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; " 
      }
      $sqlCmd +=  "RESTORE DATABASE [" + $options.destDatabase + "] FROM  DISK = N'" + $options.fullBackupPath + "' WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5;"
      
      if($options.closeExistingConnections -eq 1){
        $sqlCmd +=  "ALTER DATABASE [" + $options.destDatabase + "] SET MULTI_USER"
      }
      Write-Host "Restore command : " $sqlCmd
      
      return $sqlCmd
    }
}

Submit a Comment

Your email address will not be published. Required fields are marked *

Share This