[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
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
22
Medium Priority
?
228 Views
Last Modified: 2014-08-07
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!
0
Comment
Question by:bbdesign
  • 10
  • 8
  • 4
22 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 40244317
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

by:bbdesign
ID: 40244329
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

by:nemws1
ID: 40244349
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40244551
>> 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

by:bbdesign
ID: 40244801
@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

by:bbdesign
ID: 40244810
@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

by:nemws1
ID: 40244826
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

by:nemws1
ID: 40244840
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

by:bbdesign
ID: 40244857
@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

by:bbdesign
ID: 40244863
@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

by:nemws1
ID: 40244866
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
 

Author Comment

by:bbdesign
ID: 40244869
@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

by:bbdesign
ID: 40244870
pcefitness_beta did not exist beforehand.
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 2000 total points
ID: 40244871
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
 
LVL 23

Expert Comment

by:nemws1
ID: 40244884
Anxiously awaiting an update... hoping this last one worked for you. :)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40244907
>> 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

by:nemws1
ID: 40245061
>> 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 70

Expert Comment

by:Scott Pletcher
ID: 40245064
>>
>> 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

by:bbdesign
ID: 40245065
@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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40245074
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

by:nemws1
ID: 40245077
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

by:nemws1
ID: 40245082
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question