?
Solved

Replicate a Database - HHHEEELLLLP!!!

Posted on 2012-09-10
15
Medium Priority
?
369 Views
Last Modified: 2012-09-10
Hi,
I am a bit new to this so please keep that in mind.

I have a database (Centrispoint).  I want to create a new database (Bridger).  I then want top copy the Centrispoint database to Bridger as  a copy.

Here is what I did using the Management Studio
1)  Backed up the Centrispoint DB (FULL) - OK

2)  Created the Bridger DB - OK

3)  Attempted to restore the Bridger DB using the Centrispoint DB.

Here is the "script generated:

RESTORE DATABASE [Bridger] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Centrispoint.bak'
WITH  FILE = 52,  NOUNLOAD,  STATS = 10
GO

I received an error:
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Bridger' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This is a correct statement from SQL Server!  How do I proceed?

Thanks in advance,
hefterr
0
Comment
Question by:hefterr
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 12

Expert Comment

by:jamesrh
ID: 38384449
When creating a new database from a backup file, it is not necessary to create the DB first.  Delete the Bridger DB.  Then do a regular restore specifying Bridger as the name of the DB and using the device option to point to the backup file.  SQL Server will create the new DB then fill it up.
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 38384451
Try using WITH REPLACE since you already created the new database.

ESTORE DATABASE [Bridger] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Centrispoint.bak'
WITH  FILE = 52,  NOUNLOAD,  STATS = 10, REPLACE
GO
0
 
LVL 12

Expert Comment

by:jamesrh
ID: 38384454
Just use the UI/Management Studio to do the restore.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 12

Expert Comment

by:jamesrh
ID: 38384463
tim's way will of course work.  I was trying to help you be familiar with the streamlined process in case this comes up again.
0
 
LVL 1

Author Comment

by:hefterr
ID: 38384519
@jamesrh
I am using the UI.  I tried what you indicated (deleted Bridger) and here is the GENERATED script :

RESTORE DATABASE [Bridger]
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Centrispoint.bak' WITH  FILE = 52,
NOUNLOAD,  STATS = 10
GO

And here is the error:
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Centrispoint.mdf' cannot be overwritten.  It is being used by database 'Centrispoint'.
Msg 3156, Level 16, State 4, Line 1
File 'Centrispoint' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Centrispoint.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Centrispoint_log.ldf' cannot be overwritten.  It is being used by database 'Centrispoint'.
Msg 3156, Level 16, State 4, Line 1
File 'Centrispoint_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Centrispoint_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Please advise.
Thanks...hefterr
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 38384592
1) NEVER use the gui for this -- much too dangerous.
2) SQL is saving you from destroying the existing Centrispoint db.
3) You need to give SQL the correct file names for the Bridger db.


-- with my best guess at file names -- chg them to the actual files you created if needed
RESTORE DATABASE [Bridger]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Centrispoint.bak'
WITH
    FILE = 52,
    MOVE 'Centrispoint' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Bridger.mdf',
    MOVE 'Centrispoint_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Bridger_log.ldf',
    STATS = 10
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38384603
If you want, you can change the logical file names after the restore to match the new database:

ALTER DATABASE Bridger
MODIFY FILE ( NAME = Centrispoint, NEWNAME = Bridger )

ALTER DATABASE Bridger
MODIFY FILE ( NAME = Centrispoint_log, NEWNAME = Bridger_log )
0
 
LVL 12

Expert Comment

by:jamesrh
ID: 38384635
@ScottPletcher, the GUI will also prevent you from overwriting the db.

@hefterr, you are using a combo of the GUI and SQL scripting so the most straightforward way for you to proceed is to use @ScottPletcher's solution.

Let me know if you have any other questions.
0
 
LVL 1

Author Comment

by:hefterr
ID: 38384657
@: ScottPletcher
If you want, you can change the logical file names after the restore to match the new database:
Q: What does this mean?

Q:  Shouldn't it be easy to do with the GUI.  That's what it's for.  I can give you a screen shot of my last panel if you want?
0
 
LVL 1

Author Comment

by:hefterr
ID: 38384673
@jamesrh
you are using a combo of the GUI and SQL scripting

I really am not.  I am using the GUI.  But creating the script to show you what is generated.  To make it more clear?  I can provide a screen shot if it would help?

hefterr
0
 
LVL 12

Accepted Solution

by:
jamesrh earned 1600 total points
ID: 38384720
I apologize, I did forget to indicate the necessary GUI version of the Move statement as they have added some intelligence to the GUI in 2008 which default the mdf and ldf file names for the new db to the most logical new names, in this case Bridger.mdf and Bridger.ldf.  In 2005 you need to click on the Option page in the upper left hand corner of the restore dialog and in the files section change the name of the mdf and ldf files from Centrispoint.mdf and Centrispoint.ldf to Bridger.mdf and Bridger.ldf in the Restore As column.  Make sure to leave the original file names alone.
0
 
LVL 1

Author Comment

by:hefterr
ID: 38384784
@ jamesrh
In 2005 you need to click on the Option page in the upper left hand corner of the restore dialog and in the files section change the name of the mdf and ldf files from Centrispoint.mdf and Centrispoint.ldf to Bridger.mdf and Bridger.ldf in the Restore As column.  Make sure to leave the original file names alone.

This did it!   Thanks!!
0
 
LVL 1

Author Closing Comment

by:hefterr
ID: 38384837
Thanks to all.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38384847
Which was the main point I stated in my post about the script:

>> 3) You need to give SQL the correct file names for the Bridger db. <<


>> Shouldn't it be easy to do with the GUI <<

It's really not.  The GUI sometimes just locks up.  Sometimes it actually does the RESTORE and then locks up.  

Also, you cannot be sure later exactly what you specified in the RESTORE.  

Finally, obviously you have to re-type the entire thing every time in the GUI rather than just re-running a known, good script.
0
 
LVL 1

Author Comment

by:hefterr
ID: 38385159
@ScottPletcher
Finally, obviously you have to re-type the entire thing every time in the GUI rather than just re-running a known, good script.

Point well taken.  I an not experienced as a DBA working for a very small company - doing it all.  I need the GUI to guide me along - but I understand your point.
0

Featured Post

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.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

864 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