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= "localhostSQLExpress"

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

# Set the backup file path
$backupPath= "D:SQLBackupMorganDB.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= "localhostSQLExpress"

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

# Set the existing backup file path
$backupPath= "D:SQLBackupMorganDB.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


Advertisement

10 thoughts on “Powershell script to Backup and Restore SQL Database”

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

    Reply
  2. Sille, try using the following to get more detailed information on why it went wrong:

    $Error[0].exception.GetBaseException().Message

    Reply

Leave a Comment