Category: .NET

#============================================================
# Restore a Database using PowerShell and SQL Server SMO
# Restore to the same database, overwrite existing db
# Donabel Santos
# 2010.10.16 paulus: modified
#============================================================

#clear screen
cls

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

#get backup file
#you can also use PowerShell to query the last backup file based on the timestamp
#I'll save that enhancement for later
$backupFile = "C:MSSQLBackup" + $args[0] + ".bak"

#we will query the db name from the backup file later

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#settings for restore
$smoRestore.NoRecovery = $false;
$smoRestore.Partial = $false
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"

#show every 10% progress
$smoRestore.PercentCompleteNotification = 10;

$smoRestore.Devices.Add($backupDevice)

#read db name from the backup file's backup header
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)

#display database name
"Database Name from Backup Header : " + $smoRestoreDetails.Rows[0]["DatabaseName"]

$smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"]

#restore
$smoRestore.SqlRestore($server)

#2010.10.16 paulus: set owner to sa
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $server.Databases.Item($smoRestoreDetails.Rows[0]["DatabaseName"])
$db.SetOwner("sa", $TRUE)

"Done"

#============================================================
# Backup a Database using PowerShell and SQL Server SMO
# Script below creates a full backup
# Donabel Santos
# 2010.10.16 paulus：modified
#============================================================

#specify database to backup
#ideally this will be an argument you pass in when you run
#this script, but let's simplify for now
$dbToBackup = $args[0]

#clear screen
cls

#load assemblies
#note need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008
#otherwise may get this error
#Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure
#the assembly containing this type is loaded.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

#create a new server object
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDirectory = $server.Settings.BackupDirectory

#2010.10.16 paulus: display default backup directory
"Default Backup Directory: " + $backupDirectory

#display current backup database
"Current database: " + $dbToBackup

$db = $server.Databases[$dbToBackup]
$dbName = $db.Name

$timestamp = Get-Date -format yyyyMMddHHmmss
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")

#BackupActionType specifies the type of backup.
#Options are Database, Files, Log
#This belongs in Microsoft.SqlServer.SmoExtended assembly

$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
#2010.10.16 paulus: overwrite
$smoBackup.Initialize = $TRUE
#2010.10.16 paulus: no need for timestamp
#$smoBackup.Devices.AddDevice($backupDirectory + "" + $dbName + "_" + $timestamp + ".bak", "File")
$smoBackup.Devices.AddDevice($backupDirectory + "" + $dbName + ".bak", "File")
$smoBackup.SqlBackup($server)

#let's confirm, let's list list all backup files
$directory = Get-ChildItem $backupDirectory

#list only files that end in .bak, assuming this is your convention for all backup files
$backupFilesList = $directory | where {$_.extension -eq ".bak"}
$backupFilesList | Format-Table Name, LastWriteTime