October 6

SharePoint Script - Restoring a Content Database

Last modified by sbrickey on 3/19/2014 3:23 PM

So if your SharePoint development begins in a DEV farm, before going to a TEST farm, then finally the PROD farm, you probably end up restoring databases quite often.

Development is one of two types of effort:
- Back end (deploy to farm)
- Front end (deploy to site or site collection)

When you're dealing with back-end changes, maintaining a recent copy from PROD is probably less likely, since the functionality should be highly reusable. Additionally, these are often prerequisites to front-end changes.

When dealing with front-end changes, you may find out that you need to undo some of the work you've done... sometimes very simple, other times very difficult.

Then, after you've got pieces working, you need to reproduce the effort against TEST and PROD... and you may need to validate that what you've done in DEV will smoothly run against TEST and PROD.

For these reasons and more, I've lately decided that PowerShell scripts are my preferred approach for deployments. Whether deploying a schema change, a workflow, whatever. I script that mofo. Then I can run and rerun the script in DEV, TEST, and PROD... and it should always run the same.

So... back to the original point, I often end up needing to restore the content database to a copy of PROD, to ensure that my scripts will run against TEST (which should also have a recent copy from PROD) and PROD.

After running through this enough times, I decided to script the restore. This way, I can restore my DEV environment quickly, whenever I need.

The script uses PowerShell Remoting, so I can run this on my workstation instead of the server. It locates the content database from the site collection, removes the content database from the webapp, takes the database offline (drops any remaining connections), brings the database back online, restores the database from backup file, then attaches it back to the web application.

# Connect via Windows Remoting (only required if this is not being executed on the SharePoint server)
Enter-PSSession -ComputerName "SPServer" -Authentication CredSSP -Credential "DOMAIN\USERNAME"

# Variables
$tSite_Url = "http://spserver/sitecollection"
$tSqlRestore_File = "C:\[BACKUPS]\WSS_Content - 2011-09-15.bak"

# Load the SharePoint and SQL assemblies
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

############################
###   INPUT VALIDATION   ###
############################
if (![System.Io.File]::Exists($tSqlRestore_File))
{
    Write-Host "SQL Backup file [$tSqlRestore_File] NOT FOUND"
    Return
}

if ([System.Runtime.InterOpServices.Marshal]::SizeOf([System.IntPtr]) -eq 4)
{
    Write-Host "Cannot operate in PowerShell 32-bit (x86) mode"
    Return
}

if ( [Microsoft.SharePoint.Administration.SPFarm]::Local -eq $null )
{
    Write-Host "Unable to connect to SharePoint farm!"
    Return
}

try { (New-Object Microsoft.SharePoint.SPSite($spSite_Url)).Dispose() }
catch [System.Exception] {
    Write-Host "SharePoint Site [$spSite_Url] NOT FOUND"
    if ($tSite -ne $null) { $tSite.Dispose() }
    Return
}


# Find the Content Database
$tSite = New-Object Microsoft.SharePoint.SPSite($tSite_Url)
$tWebApp = $tSite.WebApplication
$tContentDB = $tSite.ContentDatabase
$tSite.Dispose()

# Remove the Content Database
$tContentDB_Name = $tContentDB.Name
$tContentDB_Server = $tContentDB.Server
$tWebApp.ContentDatabases.Delete($tContentDB.Id)

# Prepare SQL connection
$tSqlConnStrBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$tSqlConnStrBuilder["Server"] = $tContentDB_Server
$tSqlConnStrBuilder["Initial Catalog"] = "Master"
$tSqlConnStrBuilder["Integrated Security"] = $true
$tSqlConnStrBuilder["Timeout"] = 0
$tSqlConn = New-Object System.Data.SqlClient.SqlConnection($tSqlConnStrBuilder.ConnectionString)

# Take the database offline (drops any remaining connections)
$tSqlCmd = New-Object System.Data.SqlClient.SqlCommand
$tSqlCmd.Connection = $tSqlConn
$tSqlCmd.CommandType = [System.Data.CommandType]::Text
$tSqlCmd.CommandText = [System.String]::Format("ALTER DATABASE [{0}] SET OFFLINE WITH ROLLBACK IMMEDIATE", $tContentDB_Name)
$tSqlCmd.Connection.Open()
$tSqlCmd.ExecuteNonQuery() | Out-Null
$tSqlCmd.Connection.Close()

# Bring the database back online
$tSqlCmd = New-Object System.Data.SqlClient.SqlCommand
$tSqlCmd.Connection = $tSqlConn
$tSqlCmd.CommandType = [System.Data.CommandType]::Text
$tSqlCmd.CommandText = [System.String]::Format("ALTER DATABASE [{0}] SET ONLINE", $tContentDB_Name)
$tSqlCmd.Connection.Open()
$tSqlCmd.ExecuteNonQuery() | Out-Null
$tSqlCmd.Connection.Close()

# Restore the Database from backup

# DO NOT USE T-SQL... the connection times out, and the database is left in a 'Restoring...' state.
#$tSqlCmd = New-Object System.Data.SqlClient.SqlCommand
#$tSqlCmd.Connection = $tSqlConn
#$tSqlCmd.CommandType = [System.Data.CommandType]::Text
#$tSqlCmd.CommandText = [System.String]::Format("RESTORE DATABASE [{0}] FROM DISK = N'{1}' WITH FILE = 1, MOVE N'{0}' TO N'{2}', MOVE N'{0}_log' TO N'{3}', NOUNLOAD, STATS = 10", 
#                                               $tContentDB_Name,
#                                               "C:\[BACKUPS]\WSS_Content - 2011-09-15.bak",
#                                               "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WSS_Content.mdf",
#                                               "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WSS_Content.LDF")
#$tSqlCmd.Connection.Open()
#$tSqlCmd.ExecuteNonQuery() | Out-Null
#$tSqlCmd.Connection.Close()

$tSqlRestore_Device = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem($tSqlRestore_File, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

$tSqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($tContentDB_Server)
$tSqlServer.ConnectionContext.StatementTimeout = 0
$tSqlRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$tSqlRestore.NoRecovery = $false
$tSqlRestore.ReplaceDatabase = $true
$tSqlRestore.Database =  $tContentDB_Name                                               # Restore to the SP Content Database
$tSqlRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database  # Restore the SQL Database
$tSqlRestore.PercentCompleteNotification = 1                                            # Fire the PercentComplete event every 1%
$tSqlRestore.Devices.Add($tSqlRestore_Device)                                           # Attach the backup file
$tSqlRestoreDetails = $tSqlRestore.ReadBackupHeader($tSqlServer)                        # Read the backup's contents

$tSqlRestore_percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler]    { Write-Progress -id 1 -activity "Restoring Database" -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent)) }
$tSqlRestore.add_PercentComplete($tSqlRestore_percentEventHandler)
$tSqlRestore.add_Complete($tSqlRestore_completedEventHandler)
 
Write-Output([System.String]::Format("Database restore from [{0}] is STARTING", $tSqlRestore_File))
Write-Progress -id 1 -activity "Restoring Database" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))
$tSqlRestore.SqlRestore($tSqlServer)
Write-Progress -id 1 -activity "Restoring Database" -status "Complete" -Completed
Write-Output([System.String]::Format("Database restore from [{0}] is COMPLETE", $tSqlRestore_File))

# Switch recovery model to SIMPLE
$tSqlDatabase = $tSqlServer.Databases[$tContentDB_Name]
$tSqlDatabase.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple
$tSqlDatabase.Alter()

# Shrink log file
Write-Output([System.String]::Format("Shrinking log file"))
$tSqlLogfile = $tSqlDatabase.LogFiles[0]
Write-Output([System.String]::Format("    from: {0} KB", $tSqlLogfile.Size))
$t_SizeBefore = $tSqlLogfile.Size
$tSqlLogfile.Shrink(0, ( [Microsoft.SqlServer.Management.Smo.ShrinkMethod]::NoTruncate ) )
while ($tSqlLogfile.Size -eq $t_SizeBefore)
{ $tSqlLogfile.Refresh() }
Write-Output([System.String]::Format("      to: {0} KB", $tSqlLogfile.Size))

# Attach the content database
$tWebApp.ContentDatabases.Add($tContentDB_Server, $tContentDB_Name, $null, $null, 9000, 15000, 0
)
 

Note: I also usually include a few steps afterwards, such as adding " (DEV)" to the site names, which is used when alerts or workflow task emails are sent. Depending on the work being done, I may also perform data updates, such as changing group members. 

References:
- http://www.sqlmusings.com/2009/06/01/how-to-restore-sql-server-databases-using-smo-and-powershell/ for the database backup scripts (since the T-SQL approach caused timeouts and left the database in an incomplete restore state)

EDITS:
  - 2012/05/03 - update SQL Restore to include progress bars and extra logging
  - 2012/05/07 - update SP code to locate the correct content database (if multiple exist)
  - 2012/08/28 - update $tSqlRestore.DataBase to use the SP Content Database name... in the event that the backup is being restored to a different database name
  - 2014/03/19 - update SMO code to set no timeout
                      - add some validation in the beginning, since we don't want to be stuck in a half-state
                      - add code to shrink log files and switch to simple recovery mode. Why? because it's DEV!




comments powered by Disqus