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.
at

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

Subscribe to: Post Comments (Atom)