Sql-server – Restore Database in SQL Server 2014 with different name

restoresql serversql server 2014

I am using SQL Server 2014.
I have a database but as the user deleted some records, I need to create another database with a different name
and then transfer the data from the restored database to the current one.

I know I need to rename the restored database.

From my understanding, I also need to go to the Files page and then click on Relocate all files to folder.

In terms of renaming, do I need to rename both the Data file folder and the Log file folder?

Any assistance would help clarify

Best Answer

Perform a RESTORE FILELISTONLY first, so you understand the logical and physical names of the files.

RESTORE FILELISTONLY FROM DISK = 'c:\temp\foo.bak';

From there, you just need to fill in the pertinent pieces here:

RESTORE DATABASE foo_copy FROM DISK = 'c:\temp\foo.bak'
  WITH REPLACE, RECOVERY,
  MOVE N'foo_data' TO 'c:\...\foo_copy.mdf',
  MOVE N'foo_log' TO 'c:\...\foo_copy.ldf';

In case it isn't clear: don't use the UI for this. Pointing and clicking is not something you can easily reproduce, document, or store in source control. It can be a useful way to generate the commands in some cases (using the handy Script button), but in the long run you're much better off knowing the commands the UI would have generated anyway.

Related Question