TransWikia.com

Can I just restore the .diff file over full backup to speed up restore?

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)


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: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

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP