Advertisements
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

Advertisements
  1. Ann Cao
    December 17, 2012 at 7:42 am

    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

    • Manish Kaushik
      December 18, 2012 at 1:08 pm

      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

      • Ann Cao
        December 19, 2012 at 1:00 am

        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…

      • Manish Kaushik
        December 19, 2012 at 10:08 am

        Hi Ann ,
        Glad to know that you resolved your issues, you are welcome and Thanks for visiting http://bi-bigdata.com

        Thanks
        Manish

      • Diana
        January 17, 2017 at 2:24 am

        Ann: So how did you fix the issue? Did you remove the For each loop?

  2. Ann Cao
    December 18, 2012 at 3:37 am

    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,

    • Manish Kaushik
      December 18, 2012 at 4:56 pm

      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

      Copy-Item D:\Backup\Test.bak -Destination \\ManishRemotePC\ManishShare
      

      This above script will copy your .bak file from your server to remote server.

      Let me know if you need anything else.

      Thanks
      Manish

  3. February 4, 2013 at 3:04 am

    Wow, this paragraph is nice, my sister is analyzing these kinds of
    things, so I am going to inform her.

  4. March 10, 2013 at 2:41 pm

    I am truly grateful to the holder of this web page who has shared this fantastic post at at this time.

  5. December 20, 2013 at 8:36 pm

    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

  6. Andrey
    January 30, 2014 at 11:09 pm

    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

  7. Uday Reddy
    March 8, 2016 at 12:24 am

    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….

  8. Manish Kaushik
    March 13, 2016 at 2:05 am

    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

    • Diana
      January 17, 2017 at 2:06 am

      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.

      • Manish Kaushik
        May 10, 2017 at 10:37 am

        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

  9. Mr Umang Desai
    May 2, 2017 at 8:45 pm

    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 :

    • Manish Kaushik
      May 10, 2017 at 10:31 am

      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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: