Switching gears a little bit, much of my attention this week has revolved around backups. We have several large databases that have gotten to the point where full backups every night have become unwieldy. So I’ve been redesigning the maintenance plans for them around a full backup once a week and differentials nightly. So far it’s working out very well, saving us a lot of time, disk, and tape. Woot!
While I was thinking about backups in general, I also started thinking about a question that’s come up before: how to script out all the database objects in an automated fashion. Whether it’s so you can create a second ’empty shell” copy of your database, or so you can restore a single stored procedure that you accidentally dropped, or just because it’s a development environment and you don’t care about the data so much as the object definitions. (Or because it’s just cool.) Whatever the reason, I personally think this is something that could come in handy, and I hadn’t come across any way in SQL Server to automate the scripting a database.
So i did some searching and found a few third party tools (nah), some options that revolved around VB or C# or something (yeah, have I mentioned I’m not a programmer?). Then I came across a few partial solutions using powershell. Now, again me != programmer, but I can handle a little bit of powershell. They were pretty straightforward, but they weren’t quite… enough. So I took a little of each, threw in a few touches of my own, and voila, a solution that I’m pretty darn pleased with. Can it be improved upon? I fairly confident it can be. And probably will be. But in the meantime it gets the job done. And it can be scheduled either through a database job or via the Windows scheduler, which was the whole point. And so, without further ado, I present… the script… (ooooooo!)
# SQLExportDBSchema.ps1 # Usage - # From PS: .SQLExportDBSchema.ps1 "[output location]" "[Server]" "[Database]" # From CMD: powershell -command "& 'D:powershellSQLExportDBSchema.ps1' "[output location]" "[Server]" "[Database]" " param( [string]$filepath=$null, [string]$srv=$null, [string]$database=$null ) [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $filepath = $filepath+"" $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srv $db=$s.Databases[$database] $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s) $scrp.Options.AppendToFile = $False $scrp.Options.ClusteredIndexes = $True $scrp.Options.DriAll = $True $scrp.Options.ScriptDrops = $False $scrp.Options.IncludeHeaders = $True $scrp.Options.IncludeIfNotExists = $True $scrp.Options.ToFileOnly = $True $scrp.Options.Indexes = $True $scrp.Options.Triggers = $True $scrp.Options.Permissions = $True #CREATE DATABASE $outfile =$filepath+$database+"_create.sql" $scrp.Options.FileName = $outfile $scrp.Script($db) #SECURITY $outfile = $filepath+$database+"_security.sql" $scrp.Options.FileName = $outfile $scrp.Options.IncludeDatabaseRoleMemberships = $true $scrp.Script($db.Roles) $scrp.Options.AppendToFile = $True $scrp.Script($db.ApplicationRoles) $scrp.Script($db.Users) $scrp.Script($db.Schemas) $scrp.Options.AppendToFile = $False $scrp.Options.WithDependencies = $True #PARTITIONING $outfile = $filepath+$database+"_partitioning.sql" $scrp.Options.FileName = $outfile $scrp.Script($db.PartitionFunctions) $scrp.Options.AppendToFile = $True $scrp.Script($db.PartitionSchemes) $scrp.Options.AppendToFile = $False #TABLES $outfile = $filepath+$database+"_tables.sql" $scrp.Options.FileName = $outfile $scrp.Script($db.Tables) #DDLTRIGGERS $outfile = $filepath+$database+"_triggers.sql" $scrp.Options.FileName = $outfile $scrp.Script($db.Triggers) #VIEWS $outfile = $filepath+$database+"_views.sql" $scrp.Options.FileName = $outfile $views = $db.Views | where {$_.IsSystemObject -eq $false} $scrp.Script($views) #FUNCTIONS $outfile = $filepath+$database+"_functions.sql" $scrp.Options.FileName = $outfile $functions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false} $scrp.Script($functions) #STORED PROCS $outfile = $filepath+$database+"_storedProcs.sql" $scrp.Options.FileName = $outfile $storedprocs = $db.StoredProcedures | where {$_.IsSystemObject -eq $false} $scrp.Script($storedprocs) #SYNONYMS $outfile = $filepath+$database+"_synonyms.sql" $scrp.Options.FileName = $outfile $scrp.Script($db.Synonyms)
I ran this code, and it generated some errors, but it successfully created some files with TSQL for a bunch of objects. On my test system, it didn’t create *_security.sql, *_partitioning.sql, *_tables.sql, *_triggers.sql, or *_synonyms.sql.
There are differences between the GUI wizard between SQL 2008 and SQL 2008 R2 when scripting objects. Do you know if different versions of SQL Server would require (slightly?) different code to script out those objects?
William –
You are absolutely correct, and thanks for pointing it out to me. When I wrote the script I had a specific database in mind that I wanted to script out, and that database happened to be on a SQL 2005/Powershell 1.0 server. Therefore I never tested this on a SQL 2008/Powershell 2.0 server. I apologize for the oversight, so embarrassing!
I’m in the process of fixing it to run in either environment and will post the new version asap (probably in the morning).
Pingback: Scripting out a database – revisited « Cleveland DBA