Restore MSDB database

[JtheRipper]

Hi there,

I want to restore my msdb database and would like to know if it is at all possible. It is a SQL Server 7 installation.
The reason is that we have lost DTS packages and I was hoping that to restore the msdb database I will get my packages, schedules etc back, but I run into problems. We se Legato Networker to do our backups. When I try to restore the msdb datbabase "over" the existing msdb database I receive the message that I cannot overwrite a system database. I then tried to restore the database with another name, shutdown the sql instance, copy the backup copy's data files over the msdb's and restart but then I receive the message that SQL cannot open the msdb database.

Can this be done or am I attempting the impossible?

Any help please,

Thanks,
J.

 

[JayKusch]

Give this a try ...

Restore the msdb database.

In the following example, the msdb database contains one data file, Msdbdata.mdf, and one log file, Msdblog.ldf, from the full database backup file, Msdb_db.bak.

Run the following command from a command prompt to start SQL Server:

d:\mssql7\binn\sqlservr -c -f -T3608 -T4022

In SQL Server Query Analyzer, execute the following stored procedure to detach the msdb database:

exec sp_detach_db N'msdb'
go

In SQL Server Query Analyzer, use the following syntax to restore a backup of the msdb database:

RESTORE DATABASE msdb FROM disk='d:\mssql7\backup\msdb_db.bak'
WITH MOVE 'msdbdata' TO 'd:\mssql7\data\msdbdata.mdf',
MOVE 'msdblog' TO 'd:\mssql7\data\msdblog.ldf',
REPLACE
go

NOTE: SQL Server shuts down automatically after you restore the msdb database.


Thanks

J. Kusch

 

[JtheRipper]

Hi,

Thanks for the reply, I have been away for a while bit will try the above suggestion. We do not do SQL backups, but use a 3rd party tool to do the backups. I have restored the database as msdb2 (I cannot restore over a system database using the 3rd party tool). Will the above mentioned suggestion still work if I use my msdb2 database?

Thanks,
J.

 

[JtheRipper]

I managed to recover my msdb database. I restored the msdb database under another name, detached the msdb and "new" msdb database, and attached the msdb database using the "new" msdb's datafiles.

Thanks,
J.

 

[JayKusch]

You bet!

Thanks

J. Kusch