Thursday, 13 June 2013

PowerShell to Script SQL Database Object and ReApply on a different Server


This week I had a requirement to move SQL 2012 database to a SQL server 2008 R2 server and the two servers reside in a different domain with no trust. Since SQL 2012 backups are not compatible with lower version servers the only way to move the databases was to script database with and apply the script on the destination, doing this manually took a lot of time so I decided to automate this process in PowerShell.
I took MSDN blog http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/04/use-powershell-to-script-sql-database-objects.aspx as reference for scripting a database object using SMO in PowerShell and created my script the database using EnumScript() function instead of Script() function to accomplish my requirement. 

1.       ScriptDB.ps1 will script all the objects with data and will save it in a folder with ServerName->DBname. I have base path to save the script file is U:\, If you want to save It another location just change the following variable in the first line of the script $script:basepath = "U:\" to wherever you like or you can get the value as an argument.  The Script will create a folder in< ServerName>-><Dbname> and sub-folders Tables,Views,SP and Functions in the <dbname> Folder. 

2.       ReApply.ps1 will reapply the scripts saved in step one on the destination server. You have to pass the base folder path as Argument.

Script Database (ScriptDB.Ps1) -
# Script to Script a Database
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 12/06/2013
# Script Help :-
#---------------
# Parameter 1 :- "-s" to specify the SQL Server Name
# Parameter 2 :- "-d" to specify the database name
# Example1:- ScriptDB.ps1 -s sqlservername -d dbname
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb

Clear-Host
$script:basepath = "U:\"
<#*************************************************START:Main Program***************************************************************#>
<#Command Line Argument Verification#>
if($args.Length -ne 4)
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name and use -d to specify the database name" -ForegroundColor Red
$uParameterHelp = "
Help:-
******

# Parameter 1 :- -s to specify the SQL Server Name
# Parameter 2 :- -d to specify the database name
# Example1:- ScriptDB.ps1 -s sqlservername -d dbname
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#START:Install MAIN Program#>
elseif((($args[0] -eq "-s") -or ($args[0] -eq "-S")) -and (($args[2] -eq "-d") -or ($args[2] -eq "-D")))
{
$SQLInstance = $args[1]
$SQLSplit = $SQLInstance.Split("\")
$SQLcompname = $SQLSplit[0]
if($SQLSplit[1] -ne $null)
{
$instancename = $SQLSplit[1]
$sqlfolder = $SQLcompname+"_"+$instancename 
}else{$sqlfolder = $SQLcompname}

$dbname = $args[3]

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$SQLInstance"
$dbobj = $srv.databases[$dbname]

#$scriptdb = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
#$scriptdb.Options.AppendToFile = $True
#$scriptdb.Options.ToFileOnly = $True
$dbfolder = $basepath+$sqlfolder+"\"+$dbname
write-host $dbfolder
if((Test-Path -Path $dbfolder) -eq $false)
{
 New-Item -Path $dbfolder -ItemType Directory -ErrorAction Stop|out-null
}
#$scriptdb.Options.FileName = "$dbfolder\dbcreate.sql"
#$scriptdb.Script($dbobj)

$Objects = $null
$Objtbl = $dbobj.Tables | where {!($_.IsSystemObject)}
$Objviews = $dbobj.Views | where {!($_.IsSystemObject)}
$Objsp = $dbobj.StoredProcedures | where {!($_.IsSystemObject)}
$Objfn = $dbobj.UserDefinedFunctions | where {!($_.IsSystemObject)}
If($Objtbl -ne $null)
{
$Objects += $Objtbl
$tblfolder=$dbfolder+"\Tables"
 if((Test-Path -Path $tblfolder) -eq $false)
 {
  New-Item -Path $tblfolder -ItemType Directory -ErrorAction Stop|Out-Null
 }
}
if($Objviews -ne $null)
{
$Objects += $Objviews
$vwfolder=$dbfolder+"\Views"
 if ((Test-Path -Path $vwfolder) -eq $false)
 {
  New-Item -Path $vwfolder -ItemType Directory -ErrorAction Stop|Out-Null
 }
}
If($Objsp -ne $null)
{
$Objects += $Objsp
$spfolder=$dbfolder+"\SP"
 if ((Test-Path -Path $spfolder) -eq $false)
 {
  New-Item -Path $spfolder -ItemType Directory -ErrorAction Stop|Out-Null
 }
}
if($Objfn -ne $null)
{
$Objects += $Objfn
$fnfolder=$dbfolder+"\Functions"
 if ((Test-Path -Path $fnfolder) -eq $false)
 {
  New-Item -Path $fnfolder -ItemType Directory -ErrorAction Stop|Out-Null
 }
}

foreach ($ScriptThis in $Objects) 
 {
  $objname = $ScriptThis.name
  write-host $objname
  #Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name 
  $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
  $scriptr.Options.AppendToFile = $True
  $scriptr.Options.AllowSystemObjects = $False
  $scriptr.Options.ClusteredIndexes = $True
  $scriptr.Options.DriAll = $True
  $scriptr.Options.ScriptDrops = $False
  $scriptr.Options.IncludeHeaders = $True
  $scriptr.Options.ToFileOnly = $True
  $scriptr.Options.Indexes = $True
  $scriptr.Options.Permissions = $True
  $scriptr.Options.WithDependencies = $False
  $scriptr.Options.ScriptData = $true
  $TypeFolder=$ScriptThis.GetType().Name
  switch($TypeFolder)
  {
  "Table"{$scriptr.Options.FileName = "$tblfolder\$objname.sql"}
  "View"{$scriptr.Options.FileName = "$vwfolder\$objname.sql"}
  "StoredProcedure"{$scriptr.Options.FileName = "$spfolder\$objname.sql"}
  "UserDefinedFunction"{$scriptr.Options.FileName = "$fnfolder\$objname.sql"}
  }
  #$scriptr.Script($ScriptThis)
  $Scriptr.EnumScript($ScriptThis) 
 }
}
else
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name and use -d to specify the database name" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter 2 :- -d to specify the database name
# Example1:- ScriptDB.ps1 -s sqlservername -d dbname
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb"
Write-Host $uParameterHelp -ForegroundColor Blue
}
ReApply Database (ReApply.Ps1) -
# Script to Reapply scripted database
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 12/06/2013
# Script Help :-
#---------------
# Parameter 1 :- "-s" to specify the SQL Server Name
# Parameter 2 :- "-d" to specify the database name
# Parameter 3 :- "-p" to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s SQLServerName -d dbname -p Folder Path
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb -P U:\SQLServer1
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb -P U:\SQLServer1

Clear-Host

<#*************************************************START:Main Program***************************************************************#>
<#Command Line Argument Verification#>
if($args.Length -ne 6)
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name;use -d to specify the database name and -p to specify folder path of the script files" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter 2 :- -d to specify the database name
# Parameter 3 :- -p to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s SQLServerName -d dbname -p Folder Path
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb -P U:\SQLServer1
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb -P U:\SQLServer1"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#START:Install MAIN Program#>
elseif((($args[0] -eq "-s") -or ($args[0] -eq "-S")) -and (($args[2] -eq "-d") -or ($args[2] -eq "-D")) -and (($args[4] -eq "-p") -or ($args[4] -eq "-P")))
{
$SQLInstance = $args[1]
$dbname = $args[3]
$basepath = $args[5]
Write-Host "ServerName :- "$SQLInstance
Write-Host "DatabaseName :- "$dbname
 if ((Test-Path $basepath) -eq $false)
 {
   Write-host -ForegroundColor Red "Script folder specified in the Argument cannot be found"
   EXIT;
 }Else
 {
  $tblfolder = $basepath+"\Tables"
  $vwfolder = $basepath+"\Views"
  $spfolder = $basepath+"\SP"
  $fnfolder = $basepath+"\Functions"
   if(Test-Path $tblfolder)
   {
    Write-Host "Creating Tables....."
   foreach($tblfile in Get-ChildItem $tblfolder)
   {
    $tblscriptfullpath = $tblfolder+"\"+$tblfile
    Write-Host "Executing SQLScript " $tblfile 
    Try{Invoke-Sqlcmd -InputFile $tblscriptfullpath -ServerInstance $SQLInstance -Database $dbname -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $tblfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
      }
   }
   }
     if(Test-Path $vwfolder)
   {
    Write-Host "Creating Views....."
   foreach($vwfile in Get-ChildItem $vwfolder)
   {
    $vwscriptfullpath = $vwfolder+"\"+$vwfile
    Write-Host "Executing SQLScript " $vwfile 
    Try{Invoke-Sqlcmd -InputFile $vwscriptfullpath -ServerInstance $SQLInstance -Database $dbname -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $vwfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
      }
   }
   }
  if(Test-Path $spfolder)
   {
    Write-Host "Creating Store Procedures....."
   foreach($spfile in Get-ChildItem $spfolder)
   {
    $spscriptfullpath = $spfolder+"\"+$spfile
    Write-Host "Executing SQLScript " $spfile 
    Try{Invoke-Sqlcmd -InputFile $spscriptfullpath -ServerInstance $SQLInstance -Database $dbname -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $spfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
      }
   }
   }
  if(Test-Path $fnfolder)
   {
    Write-Host "Creating Store Procedures....."
   foreach($fnfile in Get-ChildItem $fnfolder)
   {
    $fnscriptfullpath = $fnfolder+"\"+$fnfile
    Write-Host "Executing SQLScript " $fnfile 
    Try{Invoke-Sqlcmd -InputFile $fnscriptfullpath -ServerInstance $SQLInstance -Database $dbname -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $fnfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
      }
   }
   }
  
  
 }

}
else
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name;use -d to specify the database name and -p to specify folder path of the script files" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter 2 :- -d to specify the database name
# Parameter 3 :- -p to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s SQLServerName -d dbname -p Folder Path
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb -P U:\SQLServer1
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb -P U:\SQLServer1"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#Clean-up Variables#>
$SQLInstance = $null
$dbname = $null
$basepath = $null
$tblfolder = $null
$vwfolder = $null
$spfolder = $null
$fnfolder = $null
Remove-Variable -name tblfile
Remove-Variable -name vwfile
Remove-Variable -name spfile
Remove-Variable -name fnfile
$tblscriptfullpath = $null
$vwscriptfullpath = $null
$spscriptfullpath = $null
$fnscriptfullpath = $null
Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

8 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi Manoharan,

    It's working really great.Thanks a lot for providing this. I have tested this my play box.Can you tell me where i need to update, if i need only table structure without insert statements?

    Thanks,
    Krishna Vuppala

    ReplyDelete
    Replies
    1. update the line $scriptr.Options.ScriptData = $true to $scriptr.Options.ScriptData = $false in the scriptdb.ps1;

      Delete
    2. Thanks Manoharan. I have updated script and scheduled job it from windows tasks. It's working great.

      Delete
  3. Manoharan,

    Can you recommend any specific books to work on SQL through PowerShell?! I'm planning to learn powershell stuff. It's looks, it has very good options available to work on SQL.

    -Krishna

    ReplyDelete
  4. Vinoth,

    Is it possible to get rid of COLLATE Option from CREATE OBJECT statement?! Below is sample file. Any inputs would be helpful.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[TEST](
    [ID] [float] NULL,
    [Title] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SECOND_Column] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [THIRD_Column] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
    ON [PRIMARY]

    GO


    Thanks-Krishna

    ReplyDelete
    Replies
    1. Hi,

      Adding $scriptr.Options.NoCollation = $false to the scripter options in scriptdb.ps1 should take care of your requirement.

      Delete
  5. Vinoth,

    Above CREATE TABLE sql is generated by PowerShell script.

    Thanks

    ReplyDelete