restore from media - 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)

restore from media

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-07-08 : 08:06:23

currently if I want to restore 1 db to another i do

RESTORE DATABASE userr
FROM DISK = 'd:\sqlbackups\user.bak'
WITH MOVE 'user' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\userr.mdf',
MOVE 'user_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\userr_log.ldf',

REPLACE

now I am backing up my db to a media - How can I do the same restore above but from that media (I don't remember what I made the media point to -- how can I find this out)

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-08 : 15:55:11

Restore database 'yourdb'
from device [mediaset name] with {'options'}

Need an SQLDB consultant?
check www.veeningsengineering.nl

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-08 : 15:57:49

I dont know if it is 'from device' or just from [mediaset name] try it out, one of them works;-)

Need an SQLDB consultant?
check www.veeningsengineering.nl

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-08 : 20:00:37

Check sql server log for db backup event, you'll fine device name there.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-07-09 : 01:40:21

but do I have to move the backup files as in the above if i'm restoring to another db?

how can i see the actual path of the device?

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-09 : 02:46:12

You don't need te know the physical path from your device, but you can check it at management, backup devices.

you jeust need to give up the name of your backup device, and you still need to use 'with move'
for more info you should check books online subject: "Restore from"
there it's all explained.

Good luck.

Need an SQLDB consultant?
check www.veeningsengineering.nl

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-07-09 : 03:39:12

so i used

RESTORE DATABASE userr
FROM device = 'userobackup'

and I get an error device is not a recognized device type - what does this mean>?

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-09 : 04:23:19

try:
RESTORE DATABASE userr
FROM device userobackup with <specified options>

or

RESTORE DATABASE userr
FROM device 'userobackup' with <specified options>

or

RESTORE DATABASE userr
FROM userobackup with <specified options>

Need an SQLDB consultant?
check www.veeningsengineering.nl

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-07-09 : 04:55:03

thanks now i get

The media set has 3 media families but only 1 are provided. All members must be provided.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources