Two SQL Server 2008 data bases. Problem restoring the new one.
These backup & restore queries from the original database work fine:
~~~~~~~~~~~~~~
Backup Database jandmmfg
TO Disk = ‘F:\SA-SQL-BAK\jandmmfg.ba
WITH FORMAT;
~~~~~~~~~~~~~~~~~~~~~~~~~~
Restore database jandmmfg from disk
= ‘F:\SA-SQL-BAK\jandmmfg.ba
With recovery, replace
~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~
New database -- jandmweb problems with restore:
~~~~~~~~~~~~
Restore database jandmweb from disk
= ‘F:\SA-SQL-BAK\J&M_Website
With recovery, replace
~~~~~~~~~~~~
Error message:Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
Msg 3156, Level 16, State 4, Line 1
File 'jandmmfg' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
Msg 3156, Level 16, State 4, Line 1
File 'jandmmfg_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
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
~~~~~~~~~~~~~~
Backup Database jandmmfg
TO Disk = ‘F:\SA-SQL-BAK\jandmmfg.ba
WITH FORMAT;
~~~~~~~~~~~~~~~~~~~~~~~~~~
Restore database jandmmfg from disk
= ‘F:\SA-SQL-BAK\jandmmfg.ba
With recovery, replace
~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~
New database -- jandmweb problems with restore:
~~~~~~~~~~~~
Restore database jandmweb from disk
= ‘F:\SA-SQL-BAK\J&M_Website
With recovery, replace
~~~~~~~~~~~~
Error message:Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
Msg 3156, Level 16, State 4, Line 1
File 'jandmmfg' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
Msg 3156, Level 16, State 4, Line 1
File 'jandmmfg_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you lcohan:
I tried restoring without a query and just using the UI like you said. It has more option and it worked.
I tried restoring without a query and just using the UI like you said. It has more option and it worked.
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".
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".
ASKER