Saturday, February 25, 2012

Restore a backup to a different db name and path

I have the following situation:
Database1 on my sql server with the following path: C:\DBs
\Database1.mdf, etc... I made a backup of this database. Now I need
to restore this backup on a different pc into the following: Database2
with the following path C:\Customers\DBS\Database2.mdf, etc... I have
MSDE install on the pc so I dont have the enterprise manager that will
allow me to restore a db and rename the path and file name. What is
the script that I need to run using the osql command?
Thanks
Drop $35 bucks and get the Developer Edition. It has all the tools you need.
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition
------
"Monica" <Monica@.discussions.microsoft.com> wrote in message
news:42F8C3FE-6410-4083-A3EF-453895ABA12C@.microsoft.com...
>I have the following situation:
> Database1 on my sql server with the following path: C:\DBs
> \Database1.mdf, etc... I made a backup of this database. Now I need
> to restore this backup on a different pc into the following: Database2
> with the following path C:\Customers\DBS\Database2.mdf, etc... I have
> MSDE install on the pc so I dont have the enterprise manager that will
> allow me to restore a db and rename the path and file name. What is
> the script that I need to run using the osql command?
> Thanks
|||=?Utf-8?B?TW9uaWNh?= <Monica@.discussions.microsoft.com> wrote in
news:42F8C3FE-6410-4083-A3EF-453895ABA12C@.microsoft.com:

> I have the following situation:
> Database1 on my sql server with the following path: C:\DBs
> \Database1.mdf, etc... I made a backup of this database. Now I need
> to restore this backup on a different pc into the following: Database2
> with the following path C:\Customers\DBS\Database2.mdf, etc... I have
> MSDE install on the pc so I dont have the enterprise manager that will
> allow me to restore a db and rename the path and file name. What is
> the script that I need to run using the osql command?
> Thanks
something like
RESTORE DATABASE [Database2]
FROM DISK='full_path_of_backup_file'
WITH MOVE 'Database1' TO 'C:\Customers\DBS\Database2.mdf',
MOVE 'Database1_log' TO 'C:\Customers\DBS\Database2_log.ldf'
See BOL for the gory details
|||Thanks...
I tried that but now I get this:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'Database1' is not part of database 'Database2'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
|||=?Utf-8?B?TW9uaWNh?= <Monica@.discussions.microsoft.com> wrote in
news:6C6F4DE3-3AE6-4753-AB7D-4CB063CDA122@.microsoft.com:

> Thanks...
> I tried that but now I get this:
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'Database1' is not part of database 'Database2'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
Sorry, it would appear that the 'logical name' of your database is NOT
Database1. To find what the logical name is, do what the message says by
running the query
RESTORE FILELISTONLY
FROM DISK='full_path_of_backup_file'
Then, in what I wrote before
RESTORE DATABASE [Database2]
FROM DISK='full_path_of_backup_file'
WITH MOVE 'Database1' TO 'C:\Customers\DBS\Database2.mdf',
MOVE 'Database1_log' TO 'C:\Customers\DBS\Database2_log.ldf'
change MOVE 'Database1' to be MOVE 'logicalname1' and
MOVE 'Database1_log' to be MOVE 'logicalname2' (probably MOVE
'logicalname1_log') for whatever logicalnames are shown by RESTORE
FILELISTONLY
FWIW I have just run the following renaming-restore successfully for
myself
RESTORE DATABASE [Database2]
FROM DISK='C:\Documents and Settings\chris\My Documents\Databases
\Cambridge-41a-Backup-2005-06-03'
WITH MOVE 'Cambridge-41' TO 'C:\Documents and Settings\chris\My
Documents\Databases\Database2.mdf',
MOVE 'Cambridge-41_log' TO 'C:\Documents and Settings\chris\My
Documents\Databases\Database2_log.ldf'
(cut-and-pasted) so it's just a matter of making the appropriate changes
(making sure that the commas, quotes, etc are in the correct places).
HTH

No comments:

Post a Comment