Here’s a poor man’s versioning and backup scheme for Microsoft SQL Server, using a short PowerShell script to do the heavy lifting.

The PowerShell script extracts the definitions for the data structures into the Windows file system, where you can use Git or your favorite versioning software to save the structures in all their evolutionary glory. Data isn’t saved, just definitions of the structures, although you could modify the script to save some data also.

Here’s the script.

[powershell]
$scrp = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions($srv);
$scrp.Indexes = "True";
$scrp.Triggers = "True";

foreach ($db in @("Database1","Database2","Database3")) {
foreach ($objtype in @("Tables","Views","StoredProcedures","UserDefinedFunctions","UserDefinedDataTypes")) {
del C:\databasebackup\$($db + "Database")\$objtype\*.sql;
foreach ($Item in Get-ChildItem SQLSERVER:\SQL\server.yourco.com\DEFAULT\Databases\$db\$objtype) {
$sc = $scrp;
$Item.Script($sc) | Out-File -Filepath C:\databasebackup\$($db + "Database")\$objtype\$($Item.Schema.Replace("[","").Replace("[","") + "." + $Item.Name.Replace("[","").Replace("]","")).sql;
}
}
}
foreach ($Item in Get-ChildItem SQLSERVER:\SQL\sscdb.yourco.com\DEFAULT\JobServer\Jobs) {
$Item.Script($scrp) | Out-File -Filepath C:\databasebackup\Jobs\$($Item.Name.Replace("[","").Replace("]","")).sql;
}
[/powershell]

You’ll need to replace Database1, Database2, Database3 with the database you want to back up; replace server.yourco.com with the address of the server on which runs the database to be backed up; and C:\databasebackup with the path to the folder which will contain the SQL scripts.

This will generate creation scripts for the objects indicated above. You’ll see, for example, C:\databasebackup\Database1\Tables\MyTable1, C:\databasebackup\Database1\Views\MyView1, etc.

The script as it sits will extract scripts for tables, views, stored procedures, functions, user defined data types, and Agent jobs. Triggers and indexes are included in the scripts.

You can use the scripts to create a versioning history for your database structure, to re-create part or all of the database elsewhere, etc.

There are better tools on the market that you can pay for, but this is free and simple, and pretty customizable.

Hope this has helped!

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available