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

25Jan/092

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.

Posted by: Chrissy Filed under: PowerShell, SQL Server Leave a comment

Comments (2) Trackbacks (0) ( subscribe to comments on this post )

robert
February 3rd, 2009 - 12:17

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

thanx

( REPLY )
kasi
October 31st, 2009 - 02:29

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)

( REPLY )