SQL Clone powershell script

Here’s a simple powershell sccript that I use to copy one database to an sql environement to another (for example Prod to preprod).

To use you must have a shared network drive accessible from both of your SQL instances.

 

##############################################
# 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=1,
    [string]$theTime

)

# 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
$options.backupTargetDatabase = 1
$options.RT = Get-Date -UFormat "%Y%m%d%H%M%S"
$options.theTime = ""

$options.theTime = ($options.RT).ToString()
   

# Check and default values 
if($options.backupFileName.Equals("")) 
{

    $Server = [CloneHelper]::ServerInfo( $options.sourceCstr, $options);
    $fileDateTime = $theTime
}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 + ($options.sourceDatabase + $options.theTime +  [CloneHelper]::ServerInfo( $options.sourceCstr, $options) + "" + ".bak")


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


[CloneHelper]::Backup($options, $options.sourceCstr);

[CloneHelper]::Restore($options);

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

class CloneHelper 
{
    static [Void] Backup([CloneOptions]$options, [string] $cstr ) {
        
         $Server = [CloneHelper]::ServerInfo( $cstr, $options);
         $sqlCommand = [CloneHelper]::BuildBackupSql($options, $options.sourceDatabase, $cstr,  $Server)
  
          Write-Host "ICI SRC" $cstr;

          if ($cstr -eq $options.sourceCstr){
    
    
            [CloneHelper]::ConnectInfo($options.sourceCstr,$sqlCommand)
            [CloneHelper]::ExecuteSql($options.sourceCstr,$sqlCommand)
      
         }else{
            [CloneHelper]::ConnectInfo($options.destCstr,$sqlCommand) 
            [CloneHelper]::ExecuteSql($options.destCstr,$sqlCommand)
           
         }

    }

     static [string] ServerInfo([string]$cStr,[string]$sqlCommand){
        $connection = new-object system.data.SqlClient.SQLConnection($cStr);
          Write-Host "DS :"  $connection.DataSource; 
        return $connection.DataSource;

    }



    static [string] ConnectInfo([string]$cStr,[string]$sqlCommand){
        $connection = new-object system.data.SqlClient.SQLConnection($cStr);
          Write-Host "connected : " $connection.ConnectionString;
        return $connection.ConnectionString;

    }

    static [bool] ExecuteSql([string]$cStr,[string]$sqlCommand)
    {
        Write-Host "ICI SRC" $cstr;
        $connection = new-object system.data.SqlClient.SQLConnection($cStr)
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Host "$($_)"}
        $connection.add_InfoMessage($handler)
        $connection.DataSource
        $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
         Write-Host " "connection = " + $connection + "CDS" + $connection.Datasource";
        Write-Host "$command";
        $connection.Open()
        if ($command.ExecuteNonQuery() -ne -1)
        {
            echo "Failed";
            return 0;
        }
        $connection.Close();
        return 1;

    }
    static [string] BuildBackupSql([CloneOptions]$options, [string] $dbini, [string] $cstr, [string] $Server)
    {
      $fileDateTime = $options.theTime 
      $nom = $options.backupFileName  
      $nom = ($fileDateTime +  $Server + "")
      $nomcomplet = ($nom + ".bak")
      $sqlCmd = "BACKUP DATABASE [" + $dbini +"] TO DISK = N'" + ($options.sharedUNCPath + $options.sourceDatabase + $nomcomplet  )  + "' WITH NOFORMAT, NOINIT,  NAME = N'" +   $nomcomplet +"-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
    }
}

Then use a these arguments to call your database clone.

.\sqlClone.ps1 -sourceCstr "..." -sourceDatabase "..." -sharedUNCPath "\\someServer\share1$\" -destDatabase "..." -destCstr "Data Source=..."

Submit a Comment

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

Share This