Sunday, 9 November 2014

Powershell script to Backup and Restore SQL Database

Getting Backup of SQL Database is important task for every DBA before making any changes in production environment, so that we can easily Restore the Backup of the database if anything goes wrong during database migration. If you have SQL Server Management Studio(SSMS), you can easily Backup and Restore database using simple user interface (Refer this artcle: Backup and Restore SQL Database using SSMS). In Powershell, you can get backup and restore SQL database by using Server Management Objects (SMO).

Backup SQL Server Database using Powershell script

We are going to get the backup of SQL Database by using Server Management Objects (SMO) namespace class Microsoft.SqlServer.Management.Smo.Backup.  Follow the below steps to complete the backup process.

   1. Copy the below Powershell script and paste in Notepad file.
   2. Change the values for the variables $sqlName, $dbname and $backupPath with your own SQL Server instance name, database name and destination path for backup.
   3. SaveAs the Notepad file with the extension .ps1 like Backup-SQL-Database.ps1

Powershell Script: Download Backup-SQL-Databse.ps1
# Set SQL Server instance name
$sqlName= "localhost\SQLExpress"

# Set the databse name which you want to backup
$dbname= "MorganDB"

# Set the backup file path
$backupPath= "D:\SQLBackup\MorganDB.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 

# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

#Create SMO Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")

$dbBackup.Database = $dbname

#Add the backup file to the Devices
$dbBackup.Devices.AddDevice($backupPath, "File")

#Set the Action as Database to generate a FULL backup 
$dbBackup.Action="Database"

#Call the SqlBackup method to complete backup 
$dbBackup.SqlBackup($sqlServer)

Write-Host "...Backup of the database"$dbname" completed..."
   4. Now run the file Backup-SQL-Database.ps1 from Powershell to get backup of sql database. 

Powershell script to Backup SQL Database

Restore SQL Server Database using Powershell script

We are going to restore the backup of sql database by using  Server Management Objects (SMO) namespace class Microsoft.SqlServer.Management.Smo.Restore. Follow the below steps to complete the restore database process.

   1. Copy the below Powershell script and paste in Notepad file.
   2. Change the values for the variables $sqlName, $dbname and $backupPath with your own SQL Server instance name, database name and destination path of the backup file.
   3. SaveAs the Notepad file with the extension .ps1 like Restore-SQL-Database.ps1

Powershell Script: Download Restore-SQL-Database.ps1
# Set SQL Server instance name
$sqlName= "localhost\SQLExpress"

# Set new or existing databse name to restote backup
$dbname= "MorganDB"

# Set the existing backup file path
$backupPath= "D:\SQLBackup\MorganDB.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 

# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

# Create SMo Restore object instance
$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")

# Set database and backup file path
$dbRestore.Database = $dbname
$dbRestore.Devices.AddDevice($backupPath, "File")

# Set the databse file location
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreFile.PhysicalFileName = $sqlServer.Information.MasterDBPath + "\" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName = $dbname + "_Log"
$dbRestoreLog.PhysicalFileName = $sqlServer.Information.MasterDBLogPath + "\" + $dbRestore.Database + "_Log.ldf"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

# Call the SqlRestore mathod to complete restore database 
$dbRestore.SqlRestore($sqlServer)

Write-Host "...SQL Database"$dbname" Restored Successfullyy..."
   4. Now run the file Restore-SQL-Database.ps1 from Powershell to restore backup of SQL Database.

Powershell script to Restore SQL Database
Note: I have placed Powershell script file in the location C:\Scripts, if you placed in any other location, you can navigate to the corresponding path using CD path command (like cd "C:\Downloads").

Thanks,
Morgan

Advertisements
Advertisements

6 comments:

  1. I can't restore my local database on local SQL server using Restore-SQL-Database.ps1 script, I'm getting exception

    Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'localhost\SQLExpress'. "
    At line:35 char:1
    + $dbRestore.SqlRestore($sqlServer)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : FailedOperationException

    Changed $dbname and $backupPath... Can you help me?

    ReplyDelete
  2. # Set SQL Server instance name
    $sqlName= "(local)"

    ReplyDelete
  3. what to do if we have more than 1 mdf file. how to restore. please help

    ReplyDelete
  4. How can you restore with more then 2 of the Same File Name with a Different date attached to that

    ReplyDelete
  5. Thanks for the extra explanations ... I´m new to this and the other tutorials just trolled me -.-

    ReplyDelete
  6. Getting this exception even after setting $sqlName= "(local)",
    Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'CCWTSTESQL'. "
    At C:\Users\sduser\Desktop\Restore.ps1:35 char:22
    + $dbRestore.SqlRestore <<<< ($sqlServer)
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    ReplyDelete