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.
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.
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
# Set SQL Server instance name
$sqlName= "(local)"
what to do if we have more than 1 mdf file. how to restore. please help
How can you restore with more then 2 of the Same File Name with a Different date attached to that
Thanks for the extra explanations … I´m new to this and the other tutorials just trolled me -.-
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
Any success on this? I got the same error message.
Sille, try using the following to get more detailed information on why it went wrong:
$Error[0].exception.GetBaseException().Message
I was getting the same error and discovered that the base exception message was, "Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally."
This link (https://www.mssqltips.com/sqlservertip/1407/getting-exclusive-access-to-restore-sql-server-databases/) describes ways to set the database to single user mode which gives you exclusive access so you can do the restore.