Tuesday, February 12, 2013

This blog post is part of T-SQL Tuesday, a monthly SQL blog party with a rotating host and common topic. This month marks #39, hosted by Wayne Sheffield (@DBAWayne), and the theme is "Can You Shell What The PoSh Is Cooking?"

For many years I made learning PowerShell a goal and for many years the best I could do was use the PowerShell console as a replacement for the command prompt. Like most DBAs I figured out how get by with T-SQL and multi-server queries...and then I started a side project using PowerShell that didn't involve SQL Server (yet) and it was time to sink or swim. I'm happy to say a year and a half later I swam, and now PowerShell has become such an essential core skill for me that I'm kicking myself for not having learned it sooner!

On that note, Wayne has started an excellent month-long series on PowerShell that even seasoned script writers should check out. Open a console, start at the series landing page, and get scripting!

For today's post I'm going to share a script I wrote which uses Server Management Objects (SMO) to copy a database from one instance to another via backup and restore. This works against all versions and editions of SQL Server starting with SQL 2000.

If you've got Management Studio installed then you've already got SMO. If you want to run this script on a machine that doesn't have SMO you can download it as part of the SQL 2012 Feature Pack.

The script is below. Copy and paste the contents into your favorite editor and save the file as Copy-SqlServerDatabase.ps1. If copy and paste isn't your thing you can download the script - but make sure to unblock the file in Windows Explorer before extracting or you'll run into problems when you try to run it.

####################### CONSTANTS####################### SQL Versions# See http://social.technet.microsoft.com/wiki/contents/articles/783.sql-server-versions.aspx for version timeline# Also see http://support.microsoft.com/kb/321185# Also see http://sqlserverbuilds.blogspot.com/New-Object-TypeNameSystem.Version-ArgumentList'8.0.0.0'|New-Variable-NameSQLServer2000-ScopeScript-OptionConstantNew-Object-TypeNameSystem.Version-ArgumentList'9.0.0.0'|New-Variable-NameSQLServer2005-ScopeScript-OptionConstantNew-Object-TypeNameSystem.Version-ArgumentList'10.0.0.0'|New-Variable-NameSQLServer2008-ScopeScript-OptionConstantNew-Object-TypeNameSystem.Version-ArgumentList'10.50.0.0'|New-Variable-NameSQLServer2008R2-ScopeScript-OptionConstantNew-Object-TypeNameSystem.Version-ArgumentList'11.0.0.0'|New-Variable-NameSQLServer2012-ScopeScript-OptionConstant

# Open a connection to the source server and kick off a full, copy-only backup to the backup pathif ($PSCmdlet.ParameterSetName-eq'SQLAuthentication') {$SourceConnection=Get-SqlConnection-Instance$SourceInstance-Username$Username-Password$Password } else {$SourceConnection=Get-SqlConnection-Instance$SourceInstance-WindowsAuthentication }$SourceServer=New-Object-TypeNameMicrosoft.SqlServer.Management.Smo.Server-ArgumentList$SourceConnection$SourceServer.ConnectionContext.Connect()

# Setup the backup$Backup=New-Object-TypeNameMicrosoft.SqlServer.Management.Smo.Backup$Backup.Action='Database'$Backup.BackupSetName="$SourceDatabase FULL Backup"$Backup.BackupSetDescription='FULL backup of $SourceDatabase for RemitPlus upgrade'$Backup.Database=$SourceDatabase$Backup.Incremental=$false

# Iterate through files in backup and set up a new physical path for each$Restore.ReadFileList($TargetServer).Rows|ForEach-Object {$RelocateFile=New-Object-TypeNameMicrosoft.SqlServer.Management.Smo.RelocateFile$RelocateFile.LogicalFileName=$_.LogicalName