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) -
ReApply Database (ReApply.Ps1) -
Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

No comments:

Post a Comment