CODEWITHSUNDEEP
sql-server
Paul S
Paul S

Reputation: 113

SQL Server - How to change default data files destination when restoring?

I have a database called 'AdvWorks'. I have a backup call 'BackupDB.bak'.

When I restore the BackupDB.bak over AdvWorks, SQL tries to overwrite the data files of the BackupDB rather than the AdvWorks data files. I have to change this everything and to me this makes no sense.

Can someone please tell me why it behaves like this? And secondly, can this default location be changed to the restore db files rather than the backup db files?

Thanks.

Upvotes: 0

Views: 183

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

, SQL tries to overwrite the data files of the BackupDB rather than the AdvWorks data files

This is because,the location is also copied in backup file

To overcome this,use RESTORE WITH MOVE..

RESTORE DATABASE MyNwind  
   FROM MyNwind_1  
   WITH NORECOVERY,  
   MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',   
   MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf';

Upvotes: 3

Related Questions