Restore with Move placed files in original location

aneuaneu Posts: 1 New member
We are trying to restore a large database with multiple partition files. We need all of the data files to be restored to a new drive. We executed the below restore script. All of the files restored back to the their original drive location. It appears that the move commands were ignored. I do not see any thing in the logs to show that the move commands were executed.

Sql Command:

DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [CloudExpense]
FROM Disk = ''\\XXXXXX\shared\GroupData\SqlBackup1Yr\MSSQLBackup\XXXX-SQL-GSRLZ\FULL_GSRLZ_CloudExpense_20210624_005037.sqb''
SOURCE = ''CloudExpense'' LATEST_FULL with recovery , DISCONNECT_EXISTING, REPLACE"
MOVE ''CloudExpense'' TO ''H:\Data\CloudExpense.mdf'',
MOVE ''File_01'' TO ''H:\Data\CloudExpense_1.ndf'',
MOVE ''File_02'' TO ''H:\Data\CloudExpense_2.ndf'',
MOVE ''File_03'' TO ''H:\Data\CloudExpense_6.ndf'',
MOVE ''File_04'' TO ''H:\Data\CloudExpense_7.ndf'',
MOVE ''File_05'' TO ''H:\Data\CloudExpense_8.ndf'',
MOVE ''File_06'' TO ''H:\Data\CloudExpense_9.ndf'',
MOVE ''File_07'' TO ''H:\Data\CloudExpense_10.ndf'',
MOVE ''File_08'' TO ''H:\Data\CloudExpense_11.ndf'',
MOVE ''File_09'' TO ''H:\Data\CloudExpense_12.ndf'',
MOVE ''File_010'' TO ''H:\Data\CloudExpense_3.ndf'',
MOVE ''File_011'' TO ''H:\Data\CloudExpense_4.ndf'',
MOVE ''File_012'' TO ''H:\Data\CloudExpense_5.ndf'',
MOVE ''File_2018'' TO ''H:\Data\CloudExpense_13.ndf'',
MOVE ''File_2019'' TO ''H:\Data\CloudExpense_14.ndf'',
MOVE ''File_2020'' TO ''H:\Data\CloudExpense_15.ndf'',
MOVE ''File_2021'' TO ''H:\Data\CloudExpense_16.ndf'',
MOVE ''File_2021_03'' TO ''H:\Data\File_2021_03.ndf'',
MOVE ''File_2021_04'' TO ''H:\Data\File_2021_04.ndf'',
MOVE ''File_2021_05'' TO ''H:\Data\File_2021_05.ndf'',
MOVE ''File_2021_06'' TO ''H:\Data\File_2021_06.ndf'',
MOVE ''File_2022'' TO ''H:\Data\CloudExpense_17.ndf'',
MOVE ''Part2018'' TO ''H:\Data\CloudExpensePartitionDB.ndf'',
MOVE ''Part2019'' TO ''H:\Data\CloudExpensePartitionDB2.ndf'',
MOVE ''Part2020'' TO ''H:\Data\CloudExpensePartitionDB3.ndf'',
MOVE ''CloudExpense_log'' TO ''G:\Log\CloudExpense_log.ldf'', REPLACE"'IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
ALTER DATABASE [CloudExpense] SET RECOVERY SIMPLE ;

SQL Backup Logs

SQL Backup log file 10.1.7.1571
 
-SQL "RESTORE DATABASE [CloudExpense]
FROM Disk  = '\\XXXX.com\shared\GroupData\SqlBackup1Yr\MSSQLBackup\XXXX-SQL-GSRLZ\FULL_GSRLZ_CloudExpense_20210624_005037.sqb'
SOURCE = 'CloudExpense' LATEST_FULL with recovery , DISCONNECT_EXISTING, REPLACE "
MOVE 'CloudExpense' TO 'H:\Data\CloudExpense.mdf',
MOVE 'File_01' TO 'H:\Data\CloudExpense_1.ndf',
MOVE 'File_02' TO 'H:\Data\CloudExpense_2.ndf',
MOVE 'File_03' TO 'H:\Data\CloudExpense_6.ndf',
MOVE 'File_04' TO 'H:\Data\CloudExpense_7.ndf',
MOVE 'File_05' TO 'H:\Data\CloudExpense_8.ndf',
MOVE 'File_06' TO 'H:\Data\CloudExpense_9.ndf',
MOVE 'File_07' TO 'H:\Data\CloudExpense_10.ndf',
MOVE 'File_08' TO 'H:\Data\CloudExpense_11.ndf',
MOVE 'File_09' TO 'H:\Data\CloudExpense_12.ndf',
MOVE 'File_010' TO 'H:\Data\CloudExpense_3.ndf',
MOVE 'File_011' TO 'H:\Data\CloudExpense_4.ndf',
MOVE 'File_012' TO 'H:\Data\CloudExpense_5.ndf',
MOVE 'File_2018' TO 'H:\Data\CloudExpense_13.ndf',
MOVE 'File_2019' TO 'H:\Data\CloudExpense_14.ndf',
MOVE 'File_2020' TO 'H:\Data\CloudExpense_15.ndf',
MOVE 'File_2021' TO 'H:\Data\CloudExpense_16.ndf',
MOVE 'File_2021_03' TO 'H:\Data\File_2021_03.ndf',
MOVE 'File_2021_04' TO 'H:\Data\File_2021_04.ndf',
MOVE 'File_2021_05' TO 'H:\Data\File_2021_05.ndf',
MOVE 'File_2021_06' TO 'H:\Data\File_2021_06.ndf',
MOVE 'File_2022' TO 'H:\Data\CloudExpense_17.ndf',
MOVE 'Part2018' TO 'H:\Data\CloudExpensePartitionDB.ndf',
MOVE 'Part2019' TO 'H:\Data\CloudExpensePartitionDB2.ndf',
MOVE 'Part2020' TO 'H:\Data\CloudExpensePartitionDB3.ndf',
MOVE 'CloudExpense_log' TO 'G:\Log\CloudExpense_log.ldf', REPLACE"
 
-----------------------  PROCESSES COMPLETED SUCCESSFULLY   --------------------
 
6/24/2021 3:51:07 AM: Restoring CloudExpense (database) on GSRLZ instance from:
6/24/2021 3:51:07 AM:   \\XXXX.com\shared\GroupData\SqlBackup1Yr\MSSQLBackup\XXXX-SQL-GSRLZ\FULL_GSRLZ_CloudExpense_20210624_005037.sqb
 
6/24/2021 3:51:08 AM: RESTORE DATABASE [CloudExpense]  FROM VIRTUAL_DEVICE = 'SQLBACKUP_5C6AC3D5-0A65-472B-BAC8-4FFA758B266A' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , RECOVERY, REPLACE
 
6/24/2021 6:48:22 AM: Processed 37028736 pages for database 'CloudExpense', file 'CloudExpense' on file 1.
6/24/2021 6:48:22 AM: Processed 16 pages for database 'CloudExpense', file 'Part2018' on file 1.
6/24/2021 6:48:22 AM: Processed 16 pages for database 'CloudExpense', file 'Part2019' on file 1.
6/24/2021 6:48:22 AM: Processed 16 pages for database 'CloudExpense', file 'Part2020' on file 1.
6/24/2021 6:48:22 AM: Processed 977328 pages for database 'CloudExpense', file 'File_01' on file 1.
6/24/2021 6:48:22 AM: Processed 14328536 pages for database 'CloudExpense', file 'File_02' on file 1.
6/24/2021 6:48:22 AM: Processed 874720 pages for database 'CloudExpense', file 'File_10' on file 1.
6/24/2021 6:48:22 AM: Processed 883080 pages for database 'CloudExpense', file 'File_11' on file 1.
6/24/2021 6:48:22 AM: Processed 930472 pages for database 'CloudExpense', file 'File_12' on file 1.
6/24/2021 6:48:22 AM: Processed 920 pages for database 'CloudExpense', file 'File_03' on file 1.
6/24/2021 6:48:22 AM: Processed 880 pages for database 'CloudExpense', file 'File_04' on file 1.
6/24/2021 6:48:22 AM: Processed 2656 pages for database 'CloudExpense', file 'File_05' on file 1.
6/24/2021 6:48:22 AM: Processed 4288 pages for database 'CloudExpense', file 'File_06' on file 1.
6/24/2021 6:48:22 AM: Processed 1204440 pages for database 'CloudExpense', file 'File_07' on file 1.
6/24/2021 6:48:22 AM: Processed 828680 pages for database 'CloudExpense', file 'File_08' on file 1.
6/24/2021 6:48:22 AM: Processed 958904 pages for database 'CloudExpense', file 'File_09' on file 1.
6/24/2021 6:48:22 AM: Processed 4743144 pages for database 'CloudExpense', file 'File_2018' on file 1.
6/24/2021 6:48:22 AM: Processed 11985296 pages for database 'CloudExpense', file 'File_2019' on file 1.
6/24/2021 6:48:22 AM: Processed 1050960 pages for database 'CloudExpense', file 'File_2020' on file 1.
6/24/2021 6:48:22 AM: Processed 3528616 pages for database 'CloudExpense', file 'File_2021' on file 1.
6/24/2021 6:48:22 AM: Processed 16 pages for database 'CloudExpense', file 'File_2022' on file 1.
6/24/2021 6:48:22 AM: Processed 1550352 pages for database 'CloudExpense', file 'File_2021_03' on file 1.
6/24/2021 6:48:22 AM: Processed 1449488 pages for database 'CloudExpense', file 'File_2021_04' on file 1.
6/24/2021 6:48:22 AM: Processed 1019168 pages for database 'CloudExpense', file 'File_2021_05' on file 1.
6/24/2021 6:48:22 AM: Processed 40 pages for database 'CloudExpense', file 'File_2021_06' on file 1.
6/24/2021 6:48:22 AM: Processed 1 pages for database 'CloudExpense', file 'CloudExpense_log' on file 1.
6/24/2021 6:48:22 AM: RESTORE DATABASE successfully processed 83350769 pages in 10625.985 seconds (61.281 MB/sec).
6/24/2021 6:48:22 AM: SQL Backup process ended.
 


Answers

  • Hi, there is a syntax error in your RESTORE command that the SQL Backup parser did not detect.  Look for the first REPLACE keyword.  There is a double quote after REPLACE that should have been a comma (,) instead of a double quote (").  Replace that double quote with a comma i.e. from

    EXECUTE master..sqlbackup '-SQL "RESTORE ... with recovery , DISCONNECT_EXISTING, REPLACE" MOVE ..

    to

    EXECUTE master..sqlbackup '-SQL "RESTORE ... with recovery , DISCONNECT_EXISTING, REPLACE, MOVE 

    You also have a second REPLACE keyword at after all the MOVE keywords, which you can remove.
    SQL Backup - beyond compression
Sign In or Register to comment.