How to create copy of database using backup and restore

Using SQL Server Management Studio 2012, I’m trying to create a copy of a local database. I found a few variants of solution. One of them – backup and restore database as new one – HERE.

Currently create database backup with name Rewards2_bak. This copy of file place in to system catalog C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackup

enter image description here

Next step – create query for restoring database as copy of existing one

GO
use master
RESTORE FILELISTONLY
    FROM Rewards2_bak

RESTORE DATABASE Rewards2_Copy
    FROM Rewards2_bak
    WITH RECOVERY,
    MOVE 'Rewards2_data' TO 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATARewards2_copy.mdf',
    MOVE 'Rewards2_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATARewards2_log_copy.ldf'
GO

Got error, that I don’t have a backup device Rewads2_backup. I’m right understand that in this case like device i can use file, and also file location? Think something missing…

For creating backup use next query (all OK)

USE Rewards2;
GO
BACKUP DATABASE Rewards2
TO DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupRewards2_bak.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of Rewards2';
GO

Also try to use tools in SQL Server 2012 Task --> Backup and Task --> Restore, but got error – can’t create backup. (Launched program with Administrator rights)

This is screen how I config restore to copy

enter image description here

But I have error, that I can’t overwrite database file Rewards2. And this is question – why it wants to overwrite Rewards2 if I put new name of database Rewards2_copy. Or I understand something wrong?

enter image description here

Shure, that ther is a few more possible variants for making copy of database, but really whant to find problem for this solution. Where i make mistake/ what I forget or don’t understand.

Answer

When you restore a database from a backup it will use the same file names as the original database. You need to change these file names during the restore.

On the restore window go to the Files tab. On this window you have a column called Restore As. Change the file names at the end of the path in the column Restore As for each of the files you see.

Advertisement

Answer