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.

Any advice? Thank you!
Brad BansnerWeb DeveloperAsked:
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.

nemws1Database AdministratorCommented:
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
Brad BansnerWeb 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
nemws1Database AdministratorCommented:
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

Open 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior 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
Brad BansnerWeb 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
Brad BansnerWeb 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
nemws1Database AdministratorCommented:
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
nemws1Database AdministratorCommented:
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
Brad BansnerWeb 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
Brad BansnerWeb DeveloperAuthor Commented:
@nemws1
We don't have special backup software, but we have encryption on the drive, maybe that is the reason.
0
nemws1Database AdministratorCommented:
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

Open in new window

0
Brad BansnerWeb 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
Brad BansnerWeb DeveloperAuthor Commented:
pcefitness_beta did not exist beforehand.
0
nemws1Database AdministratorCommented:
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',
NOUNLOAD,  STATS = 10
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
nemws1Database AdministratorCommented:
Anxiously awaiting an update... hoping this last one worked for you. :)
0
Scott PletcherSenior 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
nemws1Database AdministratorCommented:
>> 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
Scott PletcherSenior 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
Brad BansnerWeb 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',
NOUNLOAD, STATS=10
;

Open 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.
0
Scott PletcherSenior 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
nemws1Database AdministratorCommented:
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
nemws1Database AdministratorCommented:
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

Open in new window

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.