Solved

Two SQL Server 2008 data bases. Problem restoring the new one.

Posted on 2012-03-19
4
366 Views
Last Modified: 2012-03-19
These backup & restore queries from the original database work fine:
~~~~~~~~~~~~~~
Backup Database jandmmfg
TO Disk = ‘F:\SA-SQL-BAK\jandmmfg.bak’
WITH FORMAT;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Restore database jandmmfg from disk
= ‘F:\SA-SQL-BAK\jandmmfg.bak’
With recovery, replace
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
New database   --  jandmweb    problems with restore:
~~~~~~~~~~~~
Restore database jandmweb from disk
= ‘F:\SA-SQL-BAK\J&M_Website\jandmweb.bak’
With recovery, replace
~~~~~~~~~~~~
Error message:Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg.mdf' cannot be overwritten.  It is being used by database 'jandmmfg'.
Msg 3156, Level 16, State 4, Line 1
File 'jandmmfg' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg.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\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg_log.ldf' cannot be overwritten.  It is being used by database 'jandmmfg'.
Msg 3156, Level 16, State 4, Line 1
File 'jandmmfg_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg_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.
~~~~~~~~~~~~~~~~
I don't know why the error message even mentions jandmmfg when query is for jandmweb. I have a feeling that that is the problem though.

Thank you for any help
0
Comment
Question by:jampost
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 37738994
So you have 2 db's on the same SQL 2008 Server - or you want to restore one on that SQL 2008 server but the files to be restored over are used by an existing live DB on that same SQL Righ?

Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg_log.ldf' cannot be overwritten.  It is being used by database 'jandmmfg'.


Well you could to restore it using new physical file names or in a new different folder than above Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\

To do that I found it easiest from the UI where after you click the restore and select the backup set to restore under EACH file name just click the three dots [...] and type in a new folder and/or file name.
0
 

Author Comment

by:jampost
ID: 37739208
I have SQL server running and I want to have two separate databases on this PC. I do have both data bases on another PC at a different location. I backup & restore constantly. The new database was created at the other location, and I am having a hard time restoring it at this location. I assume, in order to restore a database, the database must already exist and just needs updating; and I am trying to restore one that is not already there, and SQL Server is choking on it.  Does that sound logical?
0
 

Author Closing Comment

by:jampost
ID: 37739228
Thank you  lcohan:

I tried restoring without a query and just using the UI like you said. It has more option and it worked.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37739245
It sounds all good and easy to do indeed.
Lets take the dbs one by one say server1.db1 server2.db2
You are sayning that "I assume, in order to restore a database, the database must already exist and just needs updating; " - not mandatory to have them created on the new server however...as SQL logins are tied to the SQL server AND databases it would be good (nut not mandatory) to have all logins from Server1 added to Server2 before you do the restore.

So you have Server1.db1 backup1 from Server1 and want to restore on Server2 then I suggest open SSMS, right click "Databases" on server2 then clieck "Restore Database" and follow the steps by adding a Database name (it can be different than the source) then select "From Device" and click the [...] to the right select the backup1 file and check the box where it is listed to select it. This will read the original backup set and now you can select "Options" under general on the left pane and here is where you can change the file path/name under "Restore As"  - just click the three dots [...] and type it in.
You can also select the "Overwrite...." WITH REPLACE and generate script instead of hitting OK to have it for future use - here you will see how with move is done.

Same thing for Server1.db2 backup2 then restore to Server2 under identical or different name - remenber you need to type it in the first step of "Restore Database".
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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