Solved

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

Posted on 2014-08-06
198 Views
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.

0
Question by:bbdesign

LVL 23

Expert Comment

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

Author Comment

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

LVL 23

Expert Comment

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

LVL 68

Expert Comment

>> 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

Author Comment

@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

Author Comment

@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

LVL 23

Expert Comment

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

LVL 23

Expert Comment

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

Author Comment

@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

Author Comment

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

LVL 23

Expert Comment

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

Author Comment

@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

Author Comment

pcefitness_beta did not exist beforehand.
0

LVL 23

Accepted Solution

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

LVL 23

Expert Comment

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

LVL 68

Expert Comment

>> 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

LVL 23

Expert Comment

>> 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

LVL 68

Expert Comment

>>
>> 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

Author Comment

@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

LVL 68

Expert Comment

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

LVL 23

Expert Comment

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

LVL 23

Expert Comment

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

## Featured Post

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.