Query to restore backups - 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

Transact-SQL (2005)

Query to restore backups

Author

Topic

tomex1
Starting Member

47 Posts

Posted - 2008-06-04 : 16:44:36

Hello,
Can anyone tell me the SQL statement to restore an existing backup to an existing database?

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 16:49:20

RESTORE DATABASE dbName
FROM DISK = 'F:\Backup\dbName.bak'
WITH REPLACE

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tomex1
Starting Member

47 Posts

Posted - 2008-06-04 : 17:21:28

I got this error message when I tried it.

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\wce_site.mdf" failed with the operating system error 3(error not found).
Msg 3156, Level 16, State 3, Line 1
File 'wce_site' cannot be restored to 'c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\wce_site.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.2\MSSQL\DATA\wce_site_log.ldf" failed with the operating system error 3(error not found).
Msg 3156, Level 16, State 3, Line 1
File 'wce_site_log' cannot be restored to 'c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\wce_site_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.

Does that mean anything to you?

quote:
Originally posted by tkizer

RESTORE DATABASE dbName
FROM DISK = 'F:\Backup\dbName.bak'
WITH REPLACE

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 17:27:15

Is the backup file from another server?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tomex1
Starting Member

47 Posts

Posted - 2008-06-04 : 17:46:26

Yes it is.

quote:
Originally posted by tkizer

Is the backup file from another server?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 17:56:31

Then you might need to specify the MOVE option in the WITH part if the paths aren't the same.

Here's an example:

RESTORE DATABASE dbName
FROM DISK = 'F:\Backup\dbName.bak'
WITH REPLACE, MOVE 'dbName_Data' TO 'F:\Data\dbName_Data.mdf',
MOVE 'dbName_Log' TO 'F:\Log\dbName_Log.ldf'

Run RESTORE FILELISTONLY to see what logical names to use.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tomex1
Starting Member

47 Posts

Posted - 2008-06-04 : 18:06:53

Many thanks. I just tried to run the RESTORE FILELISTONLY but I got an error message. Where do I run this statement? Is it in the Master database?

quote:
Originally posted by tkizer

Then you might need to specify the MOVE option in the WITH part if the paths aren't the same.

Here's an example:

RESTORE DATABASE dbName
FROM DISK = 'F:\Backup\dbName.bak'
WITH REPLACE, MOVE 'dbName_Data' TO 'F:\Data\dbName_Data.mdf',
MOVE 'dbName_Log' TO 'F:\Log\dbName_Log.ldf'

Run RESTORE FILELISTONLY to see what logical names to use.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 18:08:20

It doesn't matter where you run it.

RESTORE FILELISTONLY
FROM DISK = 'F:\Backup\dbName.bak'

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tomex1
Starting Member

47 Posts

Posted - 2008-06-04 : 18:18:10

I think I am nearly there. I got this error message:

Msg 3234, Level 16, State 2, Line 1
Logical file 'aegean_site_Data' is not part of database 'aegean_site'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

And this was the SQL statement I used:

RESTORE DATABASE aegean_site
FROM DISK = 'C:\aegean_site.bak'
WITH REPLACE, MOVE 'aegean_site_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\aegean_site.mdf',
MOVE 'aegean_site_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\aegean_site_log.ldf'

Thanks in advance

quote:
Originally posted by tkizer

Then you might need to specify the MOVE option in the WITH part if the paths aren't the same.

Here's an example:

RESTORE DATABASE dbName
FROM DISK = 'F:\Backup\dbName.bak'
WITH REPLACE, MOVE 'dbName_Data' TO 'F:\Data\dbName_Data.mdf',
MOVE 'dbName_Log' TO 'F:\Log\dbName_Log.ldf'

Run RESTORE FILELISTONLY to see what logical names to use.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 18:34:39

According to the error, you are not using the correct logical names. Run RESTORE FILELISTONLY to see what they are.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tomex1
Starting Member

47 Posts

Posted - 2008-06-04 : 18:38:11

I got this error message when I tried using RESTORE FILELISTONLY :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'FILELISTONLY'.

quote:
Originally posted by tkizer

According to the error, you are not using the correct logical names. Run RESTORE FILELISTONLY to see what they are.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 18:39:24

Post your command.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tomex1
Starting Member

47 Posts

Posted - 2008-06-04 : 18:46:06

Now I've got the logical names but I still get the following error:
Msg 3234, Level 16, State 2, Line 1
Logical file 'wce_site_Data' is not part of database 'aegean_site'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I used this statement:
RESTORE DATABASE aegean_site
FROM DISK = 'C:\wce_site.bak'
WITH REPLACE, MOVE 'wce_site_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\wce_site.mdf',
MOVE 'wce_site_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\wce_site_log.ldf'

quote:
Originally posted by tkizer

According to the error, you are not using the correct logical names. Run RESTORE FILELISTONLY to see what they are.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 18:53:39

Could you delete the aegean_site database and then run the restore command?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tomex1
Starting Member

47 Posts

Posted - 2008-06-04 : 19:02:15

It's getting there. Now I get this error message:

Msg 1834, Level 16, State 1, Line 1
The file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\wce_site.mdf' cannot be overwritten. It is being used by database 'TandE_site'.
Msg 3156, Level 16, State 4, Line 1
File 'wce_site' cannot be restored to 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\wce_site.mdf'. 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.

quote:
Originally posted by tkizer

Could you delete the aegean_site database and then run the restore command?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-04 : 19:13:56

Dude, you need to restore with Move option to other location. You have to try yourself as error clearly says.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 19:29:59

Why are you trying to use file names that are already in use by other databases?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources