How do I restore a Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How do I restore a Database

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2010-03-01 : 12:37:31

I want to create a copy of a database from Server A to Server B
I backed up the database on Server A, and then copied the .BAK file to a directory on Server B.

Then I ran this query.

RESTORE DATABASE ProjectServer_Reporting
FROM DISK = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ProjectServer_Reporting.bak'
With replace

and I get a whole bunch of errors...Help!!

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProjectServer_Reporting.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'ProjectServer_Reporting' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProjectServer_Reporting.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProjectServer_Reporting_log.LDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'ProjectServer_Reporting_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProjectServer_Reporting_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.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-01 : 13:42:04

use the WITH MOVE option to place the data files in your data directory.

do a restore filelistonly to find the names of the files

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-06 : 19:25:02

Server A and B are the same or different network ?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-06 : 20:13:26

quote:
Originally posted by namman

Server A and B are the same or different network ?

Doesn't matter. OP needs to specify valid file locations by using WITH MOVE in the restore script. Backups contain information regarding the file location from the source.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-07 : 01:59:19

I think you are right, russell.

But my question is for different purpose.

If server a and b are in the same network, using copy wizard is much easier.

if he/she prefers using backup-restore method, it is still easier to user restore database wizard, rather than script, at least for me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-07 : 14:31:33

The copy wizard is NOT much easier. It doesn't give you an exact copy! Always use BACKUP/RESTORE instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources