I recently finished taking down a bunch of servers that I was using to scale out my environment by forcing read only connections off of my main read-write servers. To make a long story short, hardware advances and the additional diagnostic information in SQL 2005 allowed me to consolidate to a few very powerful, reasonably well-tuned read-write servers. The consolidation of servers allowed me to save a ton of power and cooling along with some rack space and a good size chunk of SAN disk.
Taking down the servers means that I now have to update all of my environment diagrams, server configuration scripts and even a spreadsheet or two. Anyone who has ever done this before is cringing right now. One of the worst tasks is updating the replication scripts. I script my replication settings to a network share just in case I do something silly and need to revert to my last know good setup. The scripts can really save my bacon but they are incredibly tedious to create. I have to go into Management Studio, right-click on each publication, select generate script, select script to file then finally find the existing file for that database to add to or decide there is not one and start a new file. With the amount of scripts I had to create it would have easily taken 4, make that 8 hours with interruptions to get everything scripted.
Given, that the whole process would have taken hours and probably would have gotten screwed up along the way I decided to turn to PowerShell. Unfortunately, I did not have a script ready to go….WHHAAAT?…yeah I know..I don’t have a script for everything..so I threw the question out to Twitter. Aaron Nelson (Blog|Twitter) came back right away, pointing me toward SQL PowerShell Extensions (SQLPSX) and very quickly I had a working script. If you are not familiar with SQLPSX please take some time to check it out. It really makes coding PowerShell for SQL Server fast. More importantly, if you are not part of the SQL community on Twitter then get there first.
The actual script is not terribly complex. It takes a distribution server name and an output directory as parameters then works through all publications on each of the servers that connects to the distribution server, scripting them out.
I have only run this script against a dedicated distribution server but it should also work where the publisher is the distributor too.
I spent about 4 hours throwing the script together and generated all of the scripts I needed in a little over 1 minute.
With that, here is the script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | param ([string]$sqlServer, [string]$outputDirectory, [bool]$scriptPerPublication) if ($sqlServer -eq "") $sqlserver = Read-Host -Prompt "Please provide a value for -sqlServer" if ($outputDirectory -eq "") $outputDirectory = Read-Host -Prompt "Please provide a value for -outputDirectory" function ScriptPublications param ([string]$sqlServer, [string] $outputDirectory, [bool] $scriptPerPublication)
Import-Module Repl
[string] $path = "$outputDirectory\$((get-date).toString('yyyy-MMM-dd_HHmmss'))\"
New-Item $path -ItemType Directory | Out-Null
foreach($publication in Get-ReplPublication $sqlServer) { [string] $fileName = "{0}{1}.sql" -f $path,$publication.DatabaseName.Replace(" ", "") if($scriptPerPublication) { $fileName = "{0}{1}_{2}.sql" -f $path,$publication.DatabaseName.Replace(" ", ""),$publication.Name.Replace(" ", "") } [string] $progressText = "Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName Write-Output $progressText $publication.Script([Microsoft.SqlServer.Replication.scriptoptions]::Creation ` -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles ` -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions ` -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateSnapshotAgent ` -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeGo ` -bor [Microsoft.SqlServer.Replication.scriptoptions]::EnableReplicationDB ` -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublicationAccesses ` -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateLogreaderAgent ` -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateQueuereaderAgent ` -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions) | Out-File $fileName -Append } [Microsoft.SqlServer.Management.Common.ServerConnection] $serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlServer) foreach($distributionPublisher in $distributor.DistributionPublishers) if($distributionPublisher.PublisherType -eq "MSSQLSERVER") { [string] $path = $outputDirectory + "\from_" + $distributionPublisher.Name.Replace("\", "_") ScriptPublications -sqlServer $distributionPublisher.Name -outputDirectory $path -scriptPerPublication $false } |
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
