Advertisements
Home > Powershell, SQL Server > PowerShell for Copy Database

PowerShell for Copy Database

This is PowerShell tutorial script to understand how to copy existing database with PowerShell and SMO. We will be using Transfer Class from SMO. If you wish to just generate the copy script then there is an option to just script out the transfer using the ScriptTransfer method. When we are ready to bring the data and schema over, we can use the TransferData method.

Here is PowerShell Script to Copy SQL Server Database

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
$Server  = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName

#provide your database name which you want to copy
$SourceDBName   = "TestDB"

#create SMO handle to your database
$SourceDB = $Server.Databases[$SourceDBName]

#create a database to hold the copy of your source database
$CopyDBName = "$($SourceDBName)_copy"
$CopyDB = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Database -ArgumentList $Server , $CopyDBName
$CopyDB.Create()

#Use SMO Transfer Class by specifying source database
#you can specify properties you want either brought over or excluded, when the copy happens
$ObjTransfer   = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -ArgumentList $SourceDB
$ObjTransfer.CopyAllTables = $true
$ObjTransfer.Options.WithDependencies = $true
$ObjTransfer.Options.ContinueScriptingOnError = $true
$ObjTransfer.DestinationDatabase = $CopyDBName
$ObjTransfer.DestinationServer = $Server.Name
$ObjTransfer.DestinationLoginSecure = $true
$ObjTransfer.CopySchema = $true

#if you wish to just generate the copy script
#just script out the transfer
$ObjTransfer.ScriptTransfer()

#When you are ready to bring the data and schema over,
#you can use the TransferData method
$ObjTransfer.TransferData()

Once you are done with successful execution of this PowerShell Script, Check whether you database is copied or not. Go to SQL Server Management Studio and check the user databases in Object Explorer. You may need to refresh Databases folder from Object Explorer.

CopyDB

Hope this helps.

Let me know if you have any queries on this PowerShell Tutorial.

Thanks & Regards,

Sandip

Advertisements
  1. April 20, 2013 at 7:06 am

    I just want to mention I am just beginner to blogging and seriously loved this web page. Most likely I’m planning to bookmark your website . You surely have perfect well written articles. Thank you for sharing with us your website page.

  2. April 23, 2013 at 9:05 am

    This website was… how do I say it? Relevant!
    ! Finally I’ve found something that helped me. Kudos!

  3. April 23, 2013 at 11:36 am

    When someone writes an article he/she maintains the thought of a
    user in his/her mind that how a user can know it. So that’s why this post is amazing. Thanks!

  4. April 24, 2013 at 2:48 am

    Hello there! Would you mind if I share your blog with my facebook group?
    There’s a lot of folks that I think would really appreciate your content. Please let me know. Thanks

  5. April 24, 2013 at 3:02 am

    I know this web page presents quality based posts and additional stuff, is there any other web page which provides these data in quality?

  6. April 24, 2013 at 4:36 am

    I was recommended this website by my cousin.
    I’m not sure whether this post is written by him as nobody else know such detailed about my difficulty. You’re
    incredible! Thanks!

  7. April 24, 2013 at 8:45 pm

    Hola! I’ve been following your blog for a long time now and finally got the courage to go ahead and give you a shout out from New Caney Texas! Just wanted to say keep up the excellent work!

  8. May 15, 2013 at 12:40 pm

    Excellent site you have here.. It’s hard to find high quality writing like yours nowadays. I really appreciate people like you! Take care!!

  9. May 21, 2013 at 8:16 pm

    Oh my goodness! Awesome article dude! Thank you so much, However I am experiencing problems with your RSS. I don’t know why I am unable to join it. Is there anybody getting similar RSS problems? Anyone that knows the answer can you kindly respond? Thanx!!

  10. June 5, 2013 at 7:15 am

    Ahaa, its pleasant conversation about this piece of
    writing here at this web site, I have read all that, so at this time me
    also commenting at this place.

  11. sohila zadran
    June 19, 2013 at 2:49 am

    We absolutely love your blog and find many of your post’s to be just what I’m looking for. can you offer guest writers to write content in your case? I wouldn’t mind creating a post or elaborating on a number of the subjects you write related to here. Again, awesome web site!

  12. September 22, 2015 at 4:57 pm

    Exception calling “TransferData” with “0” argument(s): “An error occurred while transferring data. See the inner exception for details.”
    At line:36 char:1
    + $ObjTransfer.TransferData()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : TransferException

  13. February 24, 2016 at 3:35 pm

    This is still valid, and works like a charm for CI / CD where database has to come up and be put to death automatically

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: