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

The title describes what I'm trying to do. I have tried creating a new database and doing a restore from a backup, attaching copies of the MDF/LDF files to the server itself, as well as using the built-in Copy Database wizard. Everything I have tried gives an error message of one kind or another. I have limited knowledge about SQL in general... it isn't really my area, but a client needs me to get this done.

I have a restore procedure for MS SQL 2005 that I know works, but it won't work with MS SQL 2012 apparently. This seems like it should be the most simple thing in the world, but somehow I cannot get it to work. This is just a one-time copy, as well.

I want to do this without taking the DB offline, since it is a live server.

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.)
0
Web DeveloperAuthor Commented:
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.
0
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,
GO


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.
0
Senior DBACommented:
>> 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>'
--,...
0
Web DeveloperAuthor Commented:
@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?
0
Web DeveloperAuthor Commented:
@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.
0
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.
0
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!)
0
Web DeveloperAuthor Commented:
@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.
0
Web DeveloperAuthor Commented:
@nemws1
We don't have special backup software, but we have encryption on the drive, maybe that is the reason.
0
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',

0
Web DeveloperAuthor Commented:
@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.
0
Web DeveloperAuthor Commented:
pcefitness_beta did not exist beforehand.
0
Was missing the FILE=1 part ... sorry.

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',
;

0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Anxiously awaiting an update... hoping this last one worked for you. :)
0
Senior DBACommented:
>> 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
0
>> 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.
0
Senior DBACommented:
>>
>> 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?
0
Web DeveloperAuthor Commented:
@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',
;


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.
0
Senior DBACommented:
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.
0
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").
0
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

0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.