MSDB restore before user DB''s

Question

Post reply

MSDB restore before user DB''s

Steve Sootoo

Grasshopper

Points: 14
More actions
April 13, 2006 at 9:22 am

#167694

Help!!!
I have a SQL server corruption. I have restored everything except my user database. The problem I have is this: The MSDB backups are occuring nightly at 11PM. My user db backups are occuring nightly at 11:30pm.
I have restored the MSDB from 4/12 which does not know about the full user db backup at 11:30 4/12. The most recent BU it knows about is from 4/11.
How do I add to the MSDB from a location?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

Choose the from device option EM in the backup dialogue, navigate to where the latest backup is and you should be fine.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

What do you mean by: "...add to the MSDB from a location"? Do you mean 'restore more information'? If so you can't.

It's late, but you should have scheduled your MSDB backup and other system backups for AFTER user backups. Also, when you have a problem...you should immediately attempt to backup all log files, that would give you the ability to do a point-in-time recovery.

I think you are out of luck and will have to restore the user database from the previous user backup.

-SQLBill

Steve Sootoo Grasshopper Points: 14 More actions · Answer 3

I realize the schedules are screwed up, now. I did not design this piece, I only have to make it work....

EM would work, if I had access to it. This is an MSDE DB, hence no EM. I would like to do this through T-SQL or osql scripts.

Any ideas?

jbrewer Default port Points: 1453 More actions · Answer 4

You can work around this if you restore your user database files by selecting from device through Enterprise Manager.

jbrewer Default port Points: 1453 More actions · Answer 5

Sorry, I just read that there is no EM on this server. I have registered a database server like this to another machine with EM on it and I was able to use the EM tools by doing this.

Steve Sootoo Grasshopper Points: 14 More actions · Answer 6

So the only way to restore the user DB to the latest level would be to detach the DB and attach it to a SQL server running EM, then restore through EM?

Isn't there a way to update MSDB with a query? That 's all that EM would be doing in the background, right? Reading the file list and updating the information within MSDB.

jbrewer Default port Points: 1453 More actions · Answer 7

No you do not have to detach and attach. If you register your MSDE database server on a pc running EM, it will act just like EM is on the server where your data is located. You are just running the jobs from your PC. I have EM on my PC. I register all of my databases from 50 servers on it. Then I can check the servers from my PC without having to go to each server and check them. I do restores, backups, all maintenance this way. It saves a lot of time to do it all from one place instead of going to 50 servers one at a time.

There should be a way to do a query but I have not been able to locate the correct syntax to restore from a device.

jbrewer Default port Points: 1453 More actions · Answer 8

I found the syntax to do the restore.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp

{DISK | TAPE } =

'physical_backup_device_name' | @physical_backup_device_name_var

Allows backups to be restored from the named disk or tape device. The device types of disk and tape should be specified with the actual name (for example, complete path and file name) of the device: DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mybackup.dat' or TAPE = '\\.\TAPE0'. If specified as a variable (@physical_backup_device_name_var), the device name can be specified either as a string constant (@physical_backup_device_name_var = 'physcial_backup_device_name') or as a variable of character string data type, except for the ntext or text data types.

You would use the physical disk location to restore from.

Steve Sootoo Grasshopper Points: 14 More actions · Answer 9

Steve Sootoo

Grasshopper

Points: 14

April 14, 2006 at 8:55 am

#632645

Thanks, I'll give that a try and get back to you.