PowerShell 1.0: Backup and Restore a Database Between Two SQL 2005 Servers

My technical partner-in-crime, Brandon, created this PowerShell script last year in an effort to help him understand PowerShell a bit more. He noted that while it may not all be best practices SQL-wise, he tried his best to use as much PowerShell as possible.

#Powershell to backup and restore a SQL database from server1 to server2. #2008, Brandon Abshire / netnerds.net

#load assemblies [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

# create source and destination sql servers $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server1' $d = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server2' # set backup directory, dbname and grab date $bkdir = 'e:\' $remoteDir = 'e$\' $dbname = 'databasename' $dt = get-date -format yyyyMMddHHmmss

#create backup object and set properties $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup') $dbbk.Action = 0 $dbbk.BackupSetDescription = "Backup of " + $dbname $dbbk.BackupSetName = $dbname + " Backup" $dbbk.Database = $dbname $dbbk.Devices.AddDevice($bkdir + "\" + $dbname + $dt + ".bak", 2) $dbbk.MediaDescription = "Disk" $dbbk.Incremental = $false

#Backup the database $dbbk.SqlBackup($s)

#create restore object and set properties. $dbres = New-Object ('Microsoft.SqlServer.Management.Smo.Restore') $dbres.Database = $dbname $dbres.Action = 0 $dbres.ReplaceDatabase = $true

#Add the backup that is on the remote server by whatever method you wish. #You can copy it locally first. I just used named pipes to grab it off the network, since it is a fast enough connection.

$dbres.Devices.AddDevice("\\" + $s + "\" + $remoteDir + $dbname + $dt + ".bak", 2) $dbres.NoRecovery = $false

#Without the KillAll, my restore got stuck in "Restoring..." forever. $d.KillAllProcesses('databasename')

#restore the database. $dbres.SqlRestore($d)

#The database is now restored from one server to another. #We must re-map the mappings on the database for my user, or login will fail.

#Set db to the specific database we want on the destination server. $db = $d.databases["databasename"]

#Set newUser to the user we want to give permissions to and drop it. #This user was restored from the other database and doesn't match up #to the one on the destination SQL server. #sp_change_users_login 'autofix','userlogin' could also be used but I #wanted to practice PS moreso than SQL

$newuser = $db.users["username"] $newuser.drop()

#Create a new user. Set the parent to the database we restored. $newuser = New-Object ('Microsoft.SqlServer.Management.Smo.User') $newuser.parent = $db

#Set the username and login name. $newuser.name = "My User" $newuser.login = "username"

#Set the default Schema, create the user and add the appropriate roles. $newuser.defaultSchema = "dbo" $newuser.Create() $newuser.AddToRole("db_datareader") $newuser.AddToRole("db_datawriter")

Credit goes out to the Allen White, MVP of the MSDN Forums for a PowerShell/SQL quickstart.