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.
Hi Manoharan,
ReplyDeleteIt'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
update the line $scriptr.Options.ScriptData = $true to $scriptr.Options.ScriptData = $false in the scriptdb.ps1;
DeleteThanks Manoharan. I have updated script and scheduled job it from windows tasks. It's working great.
DeleteManoharan,
ReplyDeleteCan 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
Vinoth,
ReplyDeleteIs 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
Hi,
DeleteAdding $scriptr.Options.NoCollation = $false to the scripter options in scriptdb.ps1 should take care of your requirement.
Vinoth,
ReplyDeleteAbove CREATE TABLE sql is generated by PowerShell script.
Thanks
Cheap Moncler jackets , combining elegant style and cutting-edge technology, a variety of styles of Cheap moncler jackets men jackets , the pointer walks between your exclusive taste style.
ReplyDeleteI think this is very good information and a post which is able to throw light on some more aspects of SQL.
ReplyDeleteSQL Server Load Soap API