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.

Backup and Restore

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

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, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server
2 comments on “PowerShell 1.0: Backup and Restore a Database Between Two SQL 2005 Servers
  1. robert says:

    I can't seem to find a powershell script to create a new user on a server. Can you provide some help?

    thanx

  2. kasi says:

    getting following error while restoring db

    Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'SRSDEV_KASI'. "
    At :line:59 char:17
    + $dbres.SqlRestore <<<< ($d)

Leave a Reply

Your email address will not be published. Required fields are marked *

*