Big Data, Data Analytics and Data Science | BI-BIGDATA.com

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.

Hope this helps.

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

Thanks & Regards,

Sandip