Database Administrators Asked on February 4, 2021
I’m doing a server migration, and i’m looking at ways to speed up restore.
I have a 1TB full backup file which takes about 2 hours to restore
RESTORE DATABASE [MyDb] FROM DISK = N'C:db202010152000.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
This file takes a while to create, and copy to new server, so I also need to include a DIFF which gets the latest data and copy over quickly,
I would include the diff in the restore like so:
RESTORE DATABASE [MyDb] FROM DISK = N'C:db202010152000.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
RESTORE DATABASE [MyDb] FROM DISK = N'C:dbdiff-final.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
However, this takes about the same time to restore (a little longer)
I was wondering if there was a way to restore the Full backup first, then just apply the diff and it restore just the diff bits, rather than full and diff in one go.
is this even possible? if so I could dramatically reduce downtime.
Database is in simple recovery model so i’m stuck with full and diff backups.
(Sql server 2017 Ent)
So based on the first answer I can just do this
alter database [MYDB] set single_user with rollback immediate
go
USE [master]
RESTORE DATABASE [MYDB] FROM DISK = N'C:dbFull.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO
this takes around 2 hours, then I can do my final diff, and just run this:
USE [master]
RESTORE DATABASE [MYDB] FROM DISK = N'C:dbdiff2final.bak' WITH FILE = 1, RECOVERY, NOUNLOAD, STATS = 5
GO
alter database [MYDB] set MULTI_USER
GO
Yes, it is possible.
You should restore full backup first WITH NORECOVERY
option, and after that, you can restore only DIFF backup.
Just try without FULL backup;
RESTORE DATABASE [MyDb] FROM DISK = N'C:dbdiff-final.bak' WITH FILE = 1, RECOVERY, NOUNLOAD, STATS = 5
RESTORE WITH NORECOVERY Leaves the database in the restoring state. This allows you to restore additional backups in the current recovery path. To recover the database, you will have to perform a restore operation by using the RESTORE WITH RECOVERY option (see the preceding option).
Check out the document.
Answered by Yunus UYANIK on February 4, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP