1

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.

1
  • You should be able to do it without the MOVE, FILE or REPLACE clauses. I hope that 1. You are doing this under a test database 2. You used WITH NORECOVERY when doing the full restore, otherwise you cannot use the diff Feb 16, 2021 at 2:16

3 Answers 3

2

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

0

you don't need to apply all differentials only the last one

0
0

Small addition to the other comments and answers here:

If a database file was added to your database in the timespan between the full backup was produced and when the diff backup was produced, then you would specify the MOVE option when restoring that differential backup (if you need to change the location for the file compared to what is in the backup's meta-data).

The full backup has no knowledge of that file since it didn't exist at the time that full backup was produced, so referring to something that "doesn't exist" will give you an error message.

Regarding having many database files, note that SSMS has a GUI option to move all data files to some location and all log files to som (other) location. I.e., less copy&paste/replace. Select from device in the restore GUI, modify two paths, let it generate the restore command and script it in the GUI to get the SQL command.

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.