How to make a copy of a database in Microsoft SQL Server 2012 to a new database on the same server

The usual/best process is to do a BACKUP of the database on instance #1 and then a RESTORE on instance #2.  What error did you receive when you tried this?

(NOTE: You cannot go "backwards" with releases doing this.  IE, you can't back up a database on SQL 2012 and restore it to SQL 2005 - it won't work.  You can go from 2005 to 2012, though.)

I want to restore to the same exact SQL Server installation... so they are both MS SQL 2012. The problem I seem to be having is with identifying the target MDF/LDF files. It keeps wanting to change them back to the source database filenames. I swear I've even seen SQL change them back after I changed them the first time (within the restore dialogue window).

My server tech ran in a problem a few weeks ago doing this exact same thing, and ended up losing a few hours of data because it overwrote the MDF/LDF files. Which is why I'm trying to be exceedingly cautious here.

Ah.  In that case, you can restore a copy of the live database.

Go to the "Restore Database" dialog.
- "From database" should already be selected.
- Select the database you want to copy from the dropdown to the right of "From Database"
   (this will automatically show any known backups for the databases on your system - I assume you are backing them up)
- Up above, beside "To database" click and fill in a new name for the database.
- Click on "Script" way up at the top (this will open a new query window with some SQL in it)
- Click on "Cancel" (yes, "Cancel" - NOT okay)

In the script that is generated, reformat it so you can read it like this:

RESTORE DATABASE [AdventureCopy] FROM 
DISK = N'F:\Backups\hostname\SQLinstancename_AdventureWorks2008R2_FULL_20140806_022000.bak' WITH  FILE = 1,
MOVE N'AdventureWorks2008R2_Data' TO N'F:\MSSQL14\Data\AdventureCopy.mdf',
MOVE N'AdventureWorks2008R2_Log' TO N'F:\MSSQL14\Log\AdventureCopy_1.ldf',
NOUNLOAD,  STATS = 10
GO

Select allOpen in new window

Now you can MAKE SURE that the data/log paths and filenames are correct.  Here, they all appear as "AdventureCopy".  Just run the RESTORE command in the new window by clicking Execute in SSMS.

>> I swear I've even seen SQL change them back after I changed them the first time (within the restore dialogue window). <<

Ooh, yeah, that's a problem.  NEVER use the gui to restore a database, because, among other reasons:
1) it's still flaky on occasion
2) you'll have no record of what you actually did
3) you have to re-do it from scratch every time instead of having a working script you can just modify

USE existing_db

BACKUP DATABASE existing_db TO DISK = 'x:\full\path\to\backup\file\existing_db__FULL.BAK' WITH COMPRESSION, INIT

--this command will list all the logical file names.
--you'll have to put every logical file name into a MOVE command on the RESTORE.
EXEC dbo.sp_helpfile

RESTORE DATABASE new_db
FROM DISK =  'x:\full\path\to\backup\file\existing_db__FULL.BAK'
WITH
MOVE '<logical_file_name1>' TO '<new_physical_file_name1>',
MOVE '<logical_file_name2>' TO '<new_physical_file_name2>'
--,...

@ScottPletcher: I tried step 1 but I get an error message:

Msg 1844, Level 16, State 1, Line 1
BACKUP DATABASE WITH COMPRESSION is not supported on Web Edition (64-bit).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I guess the version we have doesn't work with that particular backup method?

@nemws1: I went to restore / database, selected the FROM database from the drop-down menu (the existing database), selected the TO database from the drop-down menu (a new database I just created), and generated the script, which looks like this:

USE [master]
RESTORE DATABASE [pcefitness_beta] FROM  DISK = N'{CD5F4065-13FE-4B5E-85B1-89F5BA1C5594}3' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

I don't see anything about moving the MDF/LDF files. I'm really nervous see this, because I need to make sure I am not disturbing anything with regards to the existing database.

Are you using some sort of backup solution (CommVault/EMF Networker/etc?) on your SQL servers?

This:  

DISK = N'{CD5F4065-13FE-4B5E-85B1-89F5BA1C5594}3'

looks really weird to me.

Do a COPY ONLY backup (make sure you select this when you do your backup!!! ) to a file on your local filesystem ("Back up to" section, remove whatever is there and add in your own path somewhere) and restore explicitly from from that location (use "From device" instead of "From database" in the Restore dialog).  Again, script it out and make sure you see the "MOVE" statements.

Also, you do NOT need to have your database created beforehand.  Usually I do *NOT* do this.  If you do have the DB already created, you need to select the "Overwrite the existing database" which will add the "REPLACE" option.  I can see you did *not* do this above, so even if you ran it and it did what you wanted it to do, it wouldn't have worked since your destination database already exists.

Drop your blank/new database.  It'll make restoring *easier* (and you know it won't blow away your existing/good database, too!)

@Scott Pletcher
I made a non-compressed backup as step 1 using the wizard. Then I ended up with this query:

RESTORE DATABASE pcefitness_beta
FROM DISK =  'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\pcefitness\pcefitness__FULL.BAK'
WITH
MOVE 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness_beta',
MOVE 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness_beta_log'

However, I get this error:

Msg 3234, Level 16, State 2, Line 1
Logical file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness' is not part of database 'pcefitness_beta'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

@nemws1
We don't have special backup software, but we have encryption on the drive, maybe that is the reason.

Try this.  The database 'pcefitness_beta' should not exist before you run this.

RESTORE DATABASE pcefitness_beta
FROM DISK =  'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\pcefitness\pcefitness__FULL.BAK'
WITH
MOVE 'pcefitness' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness_beta',
MOVE 'pcefitness_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness_beta_log',
NOUNLOAD,  STATS = 10

Select allOpen in new window

@nemws1
Tried that exact query, got:

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

pcefitness_beta did not exist beforehand.

Anxiously awaiting an update... hoping this last one worked for you. :)

>> The media set has 2 media families but only 1 are provided. All members must be provided. <<

You must have specified two backup files when you did the backup.  You must then specify both of those files in the RESTORE command as well.

RESTORE DATABASE pcefitness_beta
FROM DISK =  'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\pcefitness\pcefitness__FULL.BAK',
    DISK = '??path??\??filename??.BAK'
WITH
MOVE 'pcefitness' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness_beta',
MOVE 'pcefitness_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness_beta_log',
STATS = 10

>> The media set has 2 media families but only 1 are provided. All members must be provided. <<

Pretty sure this error is just from a missing comma.

>>
>> The media set has 2 media families but only 1 are provided. All members must be provided. <<

 Pretty sure this error is just from a missing comma.
<<

What are you basing that conclusion on?

@nemws1

Looks like it worked!

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 1766584 pages for database 'pcefitness_beta', file 'pcefitness' on file 1.
Processed 3 pages for database 'pcefitness_beta', file 'pcefitness_log' on file 1.
RESTORE DATABASE successfully processed 1766587 pages in 37.085 seconds (372.157 MB/sec).

Procedure was:

full backup pcefitness with "copy-only backup" to pcefitness_full.bak
pcefitness_beta does not exist
query:

RESTORE DATABASE pcefitness_beta
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\pcefitness\pcefitness_full.bak'
WITH FILE=1,
MOVE 'pcefitness' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness_beta',
MOVE 'pcefitness_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\pcefitness_beta_log',
NOUNLOAD, STATS=10
;

Select allOpen in new window

When I do "properties" on pcefitness_beta it says Logical Name: pcefitness and pcefitness_log, not sure why those are like that (must have kept those from the backup, not sure if Logical Name is important to me or not). However, under File Name it says pcefitness_beta and pcefitness_beta_log, so I guess I am OK. As long as the two databases are completely separated now, I should be fone.

The logical name is irrelevant, and has no connection to any other db.

You could give every one of your dbs logical names of xxx and yyy if you really wanted to.

Awesome!!!

We didn't change the logical names, just their location, so they'll still be 'pcefitness' and 'pcefintness_log'.  You can rename that in the database properties dialog (right click database -> Properties, "Files" on the left-hand side, then just name them whatever you want under "Logical Name").

The TQSL to fix change the logical names:

USE [pcefitness_beta]
GO
ALTER DATABASE [pcefitness_beta]
MODIFY FILE (NAME=N'pcefitness', NEWNAME=N'pcefitnessbeta')
GO
ALTER DATABASE [pcefitness_beta]
MODIFY FILE (NAME=N'pcefitness_log', NEWNAME=N'pcefitnessbeta_log')
GO

Select allOpen in new window