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 Database (ScriptDB.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