SQL Server 2016 Restore – Microsoft SQL Server 2016 Restore Command for Multiple Files

sql serversql-server-2016

I have a question regarding proper syntax for restoring a 2016 SQL Server database.

One database 1 mdf no issue. But I need to restore a database that has 3 mdf files and 73 ndf files and then apply 2 differential back ups.

RESTORE DATABASE [Database]
FROM DISK = N'E:\Data\Server_Database_20210213221000_full_1_of_2.bak',
     DISK = N'E:\Data\Server_Database_20210213221000_full_2_of_3.bak'
     DISK = N'E:\Data\Server_Database_20210213221000_full_3_of_3.bak'
WITH FILE = 1, REPLACE,
MOVE 'Database' TO 'E:\Data\Database.mdf',
MOVE 'FactPart00' TO 'E:\Data1\FactPart\FactDatePart00.ndf',  
MOVE 'FactPart01' TO 'E:\Data1\FactPart\FactDatePart01.ndf',  
MOVE 'FactPart02' TO 'E:\Data1\FactPart\FactDatePart02.ndf',
... all the way to 73

The above part works my question is now once how do I then restore diff file
File Name

Server_Database_DIFF_20210214_220209.bak

To restore the diff will I also need to reference all the ndf files as well or just restore. What should my syntax look like? I don't have an error since I have not ran it as I am nervous about corrupting database.

Best Answer

to restore a differential backup please tale a look to the official doc here:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-differential-database-backup-sql-server?view=sql-server-ver15#using-transact-sql

You have to restore your full backup with the NORECOVERY clause. Then take your last differential backup and restore it.

Something like that:

RESTORE DATABASE [Database]  
   FROM Disk = N'Database_backup_full.bak'
WITH FILE = 1, REPLACE, NORECOVERY,
MOVE 'Database' TO 'somepath\Database.mdf',
.....
   ;  
GO  
RESTORE DATABASE [Database]
   FROM Disk = N'Database_backup_differential.bak'
   WITH RECOVERY;  
GO

You do not need to specify MOVE clause again in restoring differential backup files if that's already done when restoring full backup file

Related Question