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

#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.

#restore the database.

#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”]

#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”

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

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server