Home
> Powershell > PowerShell script to backup a database and restore on other server.
PowerShell script to backup a database and restore on other server.
Many a times as a DBA when we need to refresh our QA or Dev environment with Production environment, we need to have a full backup of Production database and then manually we need to restore them in other environments. The following is a simple power shell script to just take a full backup with copy only option and then restore it on other server.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[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")
$sqlobjectSource = new-object ("Microsoft.SqlServer.Management.Smo.Server") "MSSQLSERVER"
write-host "Simple Powershell script to backup all user database in MS SQL Server"
$Databases = $sqlobjectSource.Databases
$backuppath = "D:\Backup"
foreach ($Database in $Databases)
{
if($Database.Name -eq "Test")
{
write-host "........... Backup in progress for " $Database.Name " database in " $sqlobjectSource.Name
$dbname = $Database.Name
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Database" # For full database backup, can also use "Log" or "File"
$dbBackup.Database = $dbname
$dbBackup.CopyOnly = "true"
$dbBackup.Devices.AddDevice($backuppath + "\" + $dbname + ".bak", "File")
$dbBackup.SqlBackup($sqlobjectSource)
}
}
write-host "........... Backup Finished for " $Database.Name " database in " $sqlobjectSource.Name
write-host "............................................................"
write-host "............................................................"
$sqlobjectDestination = new-object ("Microsoft.SqlServer.Management.Smo.Server") "MSSQLSERVER\SQL2012"
write-host "...........Restoring " $Database.Name " database in "$sqlobjectDestination.Name " Server...."
$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")
$dbRestore.Database = "Test"
$dbRestore.Devices.AddDevice($backuppath + "\" + $Database.Name + ".bak", "File")
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreFile.LogicalFileName = $Database.Name
$dbRestoreFile.PhysicalFileName = $sqlobjectDestination.Information.MasterDBPath + "\" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName = $Database.Name + "_Log"
$dbRestoreLog.PhysicalFileName = $sqlobjectDestination.Information.MasterDBLogPath + "\" + $dbRestore.Database + "_Log.ldf"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)
$dbRestore.SqlRestore($sqlobjectDestination)
write-host "...........Restored " $Database.Name " database in "$sqlobjectDestination.Name " Server...."
Thanks
Manish
Categories: Powershell
#Powershell, powershell for
Leave a Reply
BI Big Data Authors
Sandip Shinde
Manish Kaushik
Suhas Kudekar(MCC)
Umesh M Patil
YashwantOur Visitors
Big Data, Data Analytics and Data Science | BI-BIGDATA.com 
Hi, Thanks Manish for the script.
I am trying it , and I got an error:
0
1
Exception calling “SqlRestore” with “1” argument(s): “Restore failed for Server ‘myremoteServername
‘. ”
At line:42 char:22
+ $dbRestore.SqlRestore <<<< ($sqlobjectDestination)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
………..Restored workSteve database in snocdv01 Server….
For the destination I am using this:
$sqlobjectDestination = new-object ("Microsoft.SqlServer.Management.Smo.Server") "myremoteServername"
what could be wrong?
Thanks much
Hi Ann,
1. Are you able to connect to your “myremoteServername” from your server, just try to connect via SSMS
2. Once you connect successfully, check if the “workSteve” is already existing or what ?
3. If exists then check if any one connected to “workSteve” or what.
4. Try to manually restore “workSteve” on your remote server to ensure the backupsets are valid and logged in user is having sufficient privilege to restore DB.
Let me know your findings.
Thanks
Manish
Thanks Manish.You answered my question in #4 above.
In #2, it is all YES for your 4 questions. What I figured out later is :
The first step when doing backup, it backed up test.bak
But when doing the restore, because the for each loop above, it generate $database = what ever is the last database on the server.
so when adding device to restore, it adds worksteve.bak instead of test.bak. also the logical filename is worksteve but not test.
So that is why it compalins cannot open device…
Hi Ann ,
Glad to know that you resolved your issues, you are welcome and Thanks for visiting http://bi-bigdata.com
Thanks
Manish
Ann: So how did you fix the issue? Did you remove the For each loop?
Also in :
$dbBackup.Devices.AddDevice($backuppath + “\” + $dbname + “-” + $dt + “.bak”, “File”)
Is the backupath on original server or remote server?
Is there a step that moves the backup file from local server to remote server?
Thanks,
Hi Ann,
1. Is the backupath on original server or remote server?
Yes in above script, look for line no 10, $backuppath = “D:\Backup”, this is on original server.
2. Is there a step that moves the backup file from local server to remote server?
Yes, you can add this following script to move backup from your server to remote server
This above script will copy your .bak file from your server to remote server.
Let me know if you need anything else.
Thanks
Manish
Wow, this paragraph is nice, my sister is analyzing these kinds of
things, so I am going to inform her.
Lorene, thanks for visiting http://bi-bigdata.com
Thanks
Manish
I am truly grateful to the holder of this web page who has shared this fantastic post at at this time.
Get this,
Exception calling “SqlRestore” with “1” argument(s): “Restore failed for Server ‘jon-dev-sql02\mssqlserver’. ”
At C:\temp\remotedbcopy.ps1:41 char:1
+ $dbRestore.SqlRestore($sqlobjectDestination)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException
Hi, thanks for the script .
I am trying it , and I got an error:
System.Data.SqlClient.SqlException: Logical file ‘webcmsv6staging’ is not part of database ‘Synctest6’. Use RESTORE FILELISTONLY to list the logical file names.
webcmsv6staging – is a name of backup db
Synctest6 – is a name of restore db
HI Manish,
I m new to sql server….but we have a requirement same as your code…
could you please hell me on below error which i got executing your code
Exception calling “SqlRestore” with “1” argument(s): “Restor
‘GDAS-CLNT-35\SQLEXPRESS’. ”
At line:31 char:22
+ $dbRestore.SqlRestore <<<< ($sqlobjectDestination)
+ CategoryInfo : NotSpecified: (:) [], MethodIn
+ FullyQualifiedErrorId : DotNetMethodException
Exception calling "SqlRestore" with "1" argument(s): "Restor
'GDAS-CLNT-35\SQLEXPRESS'. "
At line:31 char:22
+ $dbRestore.SqlRestore <<<< ($sqlobjectDestination)
+ CategoryInfo : NotSpecified: (:) [], MethodIn
+ FullyQualifiedErrorId : DotNetMethodException
……….. Backup in progress for testdb database in GDAS-CLNT-36\SQLEXPRESS
……….. Backup Finished for testdb database in GDAS-CLNT-36\SQLEXPRESS
……………………………………………………
……………………………………………………
………..Restoring testdb database in GDAS-CLNT-35\SQLEXPRESS Server….
0
1
Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server
'GDAS-CLNT-35\SQLEXPRESS'. "
At line:31 char:22
+ $dbRestore.SqlRestore <<<< ($sqlobjectDestination)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
………..Restored testdb database in GDAS-CLNT-35\SQLEXPRESS Server….
Hi Uday,
Thanks for visiting http://bi-bigdata.com , could you please try to manually restore “testdb” on your remote server just to ensure the backupsets are valid . Let me know about your findings.
Thanks
Manish
Manish: I’ m getting same error:
Exception calling “SqlRestore” with “1” argument(s): “Restore failed for Server ‘PDBDBAJHS01’. ”
At line:42 char:1
+ $dbRestore.SqlRestore($sqlobjectDestination)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException
I’ve restored my “TestDB” manually and it restored successfully.
Hi Diana,
Thanks for visiting http://bi-bigdata.com , apologies for delay, are you still facing the same error, or it is resolved.
Thanks
Manish
Anyone manage to solve this issue?
FullyQualifiedErrorId : FailedOperationException
In my case its to do with the logicalFile move.
Exception : System.Management.Automation.MethodInvocationException: Exception calling “SqlRestore” with “1” argument(s): “Restore failed for Server ‘localhost\SQL2016’. ” —>
Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server ‘localhost\SQL2016’. —>
Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. —>
System.Data.SqlClient.SqlException: Logical file ‘tester’ is not part of database ‘tester’. Use RESTORE FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally.
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
— End of inner exception stack trace —
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
— End of inner exception stack trace —
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at CallSite.Target(Closure , CallSite , Object , Object )
— End of inner exception stack trace —
at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs,
MemberInfo memberInfo)
at CallSite.Target(Closure , CallSite , Object , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
TargetObject :
CategoryInfo : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : FailedOperationException
ErrorDetails :
Hi Umang,
Thanks for visiting http://bi-bigdata.com , looks like you have done with backup, could you please try to manually restore “testdb” on your remote server just to ensure the backupsets are valid . Let me know about your findings.
Thanks
Manish