#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!

Share on: