asked
Why does my restore database job take so long to run but it used to take 20 mins before
I am backing up a database on server a and copying the backup file to server b and using a query to restore the database on server b. This job used to take 20 minutes before . now it is taking 6 hours to run. When i restre the same backup file to server a it also takes 20 minutes. What could be the problem this is my query SET ANSI_WARNINGS OFF; GO USE master -- (Can't sit in the database whilst its being restored!) GO ALTER DATABASE RLL SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO -- Or this to prevent even other SA users connecting to the DB ALTER DATABASE RLL SET OFFLINE WITH ROLLBACK IMMEDIATE GO --This command can be used to check weather your database backup is not corrupted. --RESTORE VERIFYONLY FROM DISK = '\\10.10.9.64\E$\Restores\RLL.bak' --GO --RESTORE FILELISTONLY FROM DISK = '\\10.10.9.64\E$\Restores\RLL.bak' -- WITH FILE=1; --GO --RESTORE FILELISTONLY FROM DISK = '\\10.10.9.63\E$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\RLL.bak' -- Restore Full Backup RESTORE DATABASE RLL FROM DISK = '\\10.10.7.152\F$\Backup\TKLive\TKLIVE.bak' WITH FILE = 1, REPLACE, --NORECOVERY, -- Use if more T/Logs to recover RECOVERY, -- Use if NO more T/Logs to recover STATS = 10, -- Show progress (every 10%) MOVE 'RL_DATA' TO 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RLL_DATA_01.MDF', MOVE 'RL_AUDIT' TO 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RLL_AUDIT_01.NDF', MOVE 'RL_EMPLOYEE' TO 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RLL_EMPLOYEE_01.NDF', MOVE 'RL_HEALTH' TO 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RLL_HEALTH_01.NDF', MOVE 'RL_HISTORY' TO 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RLL_HISTORY_01.NDF', MOVE 'RL_WORK ' TO 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RLL_WORK_01.NDF', MOVE 'RL_INDEX ' TO 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RLL_INDEX_01.NDF', MOVE 'RL_LOG' TO 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RLL_LOG_01.LDF' GO ALTER DATABASE RLL SET RECOVERY SIMPLE GO ALTER DATABASE RLL SET MULTI_USER GO ALTER DATABASE RLL SET ONLINE WITH ROLLBACK IMMEDIATE GO USE DW_DRL GO EXEC DW_DRL.dbo.USP_BACKUP_LOG GO
Have you changed the account that used to perform this restore? It could be that [Instant File Initialization][1] was enabled previously, and now it's not? This could have been turned off at the server level or AD groups or group policy. When it runs for 6 hours - what part is it stuck on? The script above has multiple batches. Is it the actual restore? [1]:
https://www.brentozar.com/blitz/instant-file-initialization/
It can't take long on each batch. There must be one particular one that is taking the time. It's probably the restore, but I just wanted you to check that it wasn't stuck trying to change the database options.
![alt text][1] [1]: /storage/temp/4489-capture.png
If the transaction log on the database is large at the time of the full backup on the source, it may take a long time to restore the database on the destination. You could try truncating the log prior to taking a full backup on the source and copying it over for the restore and see if that helps the restore time on the destination.
If the transaction log was large then it would take a long time no matter which server was being used, but the OP said that if he restores on server A, then it takes 20 minutes. Truncating the transaction log is not a good idea - this will break your log chain and the ability to recover from backup.
17 People are following this question.
