How to create copy of database using backup and restore

Question

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 Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\

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 Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Rewards2_copy.mdf',
    MOVE 'Rewards2_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Rewards2_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 Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Rewards2_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

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?

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 1

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.

Answer 2

I think you are trying to overwrite the logical filenames from the database you are trying to copy...guessing a bit but try this - try it in SSMS if you don't need to script it right now:

1. Backup the database you want to copy (Rewards2_bak.bak is fine)
2. Create a new database (Rewards2_copy)
3. Restore into Rewards2_copy using Rewards2_bak.bak

Remember in point three to change the logical filenames within the options tab - otherwise you will try and overwrite the Rewards2 files.

There are lots of guides around:

• Copy Database with Backup and Restore
• Copy Database to other servers
• How do you backup and restore a database as a copy on the same server?

Via SQL to determine the logical filenames you can use: filelistonly

restore filelistonly from disk='enter backup file path here'

Then use the filenames reported to build you restore query, as you have already tried

Answer 3

Here is VBS script that will backup and restore DB to another server. It assumes that each server has "Backup" file share. The script can be run from any location.

sFrom  = "\\server1\Backup"
sTo = "\\server2\Backup"
Set fso = CreateObject("Scripting.FileSystemObject")

CopyDb "Db1"
CopyDb "Db2"

MsgBox "Done!"

Sub CopyDb(sDB)

  If fso.FileExists(sFrom & "\" & sDB & ".bak") Then
     fso.DeleteFile sFrom & "\" & sDB & ".bak"
  End If

  ExecuteSql "server1", "BACKUP DATABASE " & sDB & " TO DISK = '" & sFrom & "\" & sDB & ".bak'"

  fso.CopyFile sFrom & "\" & sDB & ".bak",  sTo & "\" & sDB & ".bak"

  ExecuteSql "server2", "ALTER DATABASE " & sDB & " SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
  ExecuteSql "server2", "RESTORE DATABASE " & sDB & " FROM  DISK = N'" & sTo & "\" & sDB & ".bak'"
  ExecuteSql "server2", "EXEC " & sDB & ".dbo.sp_change_users_login 'Update_One', 'user1', 'user1'"
End Sub

Sub ExecuteSql(sServer, sSql)
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=" & sServer
  cn.Execute sSql
  cn.Close
End Sub

Answer 4

In addition to answer from @JodyT:

In my case you not only needed to change the logical file names, but it is also important to remove the option "Take tail-log backup before restore". If this option is checked, then you cannot restore to Rewards2_copy if Rewards2 is in use!