#PSTip Generate T-SQL script for cloning a SQL database

Note: This tip requires PowerShell 2.0 or above.

As database administrator, you may want to create a database on development or test servers with similar settings as on the production server. This is usually done using T-SQL. So, how is this related to PowerShell?

We can use SQL SMO objects in PowerShell to generate this T-SQL script!

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName

Once we have the SQL Server object, we can use the Databases property to list all databases on the server.


The database object has the Script() method which can be used to generate the T-SQL script!


About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts