0

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:\db\202010152000.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:\db\202010152000.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
RESTORE DATABASE [MyDb] FROM  DISK = N'C:\db\diff-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)


EDIT

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:\db\Full.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:\db\diff2final.bak' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5
GO

alter database [MYDB] set MULTI_USER
GO
2

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:\db\diff-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.

2
  • amazing, i've edited my original, does that sound right?
    – Quade
    Oct 31 '20 at 13:58
  • Yes, it is right. Oct 31 '20 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.