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.
| Author |
Topic |
|
jsiedliski
Yak Posting Veteran
61 Posts |
 Posted - 2004-04-01 : 18:21:12
|
| I had a server disk failure and after I recovered all files, I noticed that the backup of the MSDB database is not current.I did, however, obtain a copy of the latest msdbdata.mdb (and .ldb) files. Since this is a system db, I don't think it will just let me attach it.Is there any way to recover the DTS Packages from this file?Additional Info:1. Disk Failure occurred on SQL Server box2. Recovered all original .mdb and .ldb files (master, msdb, user db's etc.)3. Built out a new server (new installation of SQL)4. Was able to attach the user databases5. Trying to recover the latest DTS packages (.bak file of MSDB is outdated).PLEASE Help:) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-01 : 18:25:08
|
| Stop the MSSQLSERVER service. Replace the msdb MDF and LDF files. Start the MSSQLSERVER service. When you stop the service, you can overwrite the files. sp_detach_db and sp_attach_db do the same thing except while the service is running.Now go schedule a regular msdb backup. I backup master, msdb, and model either weekly or nightly, depending upon the system.Tara |
 |
|
|
jsiedliski
Yak Posting Veteran
61 Posts |
Posted - 2004-04-01 : 19:12:47
|
| It seems to work, but then when I go to look at the DTS packages, nothing is there.Also, when clicking on the tables under MSDB (in EM), I get an error. Is there anything else I have to do (like updating system tables in Master)?When I tried sp_attach & sp_detach with MSDB, I got a msg stating that the system db's can not be attached. Could it just be that I am hosed?Thanks,Jack:) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-01 : 19:14:47
|
| The MDF and LDF files that you found must not have been recent enough to include the DTS packages. It sounds like you are hosed in fact. Treat msdb as if it were the master database especially if you've created jobs or DTS packages. Back it up regularly.Tara |
|
|
|
jsiedliski
Yak Posting Veteran
61 Posts |
Posted - 2004-04-01 : 19:23:28
|
| I am totally with you....on another server, I back up Master and MSDB (scheduled job) once per week.Here is some additional info. I know there were DTS packages at the time the server went down. Also, as of 1 MIN ago, the MSDB database went into "suspect" mode. I can not see any db objects (tables, views, etc.)Here is a link to Microsoft which describes the error I saw at one point. Not a 100% match, becuase I am not doing a resotre from .bak but rather swapping out the db files.http://support.microsoft.com/default.aspx?scid=kb;en-us;319701Any additional help would be greatly appreciated.:) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-01 : 22:22:41
|
| Many times when disk failures occur, they send chatter. If your disks are in an array it will corrupt data across the entire array. It sounds like your msdb files are corrupted.Try this. Attach the msdb files, but attach it as another database name "testdb". Run a DBCC CHECKDB on the database. It should come up with some errors, but if your lucky it will be able to fix them.Backup the database and restore into msdb.If that doesn't work, I don't know what to tell you.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-02 : 11:09:10
|
quote:
Originally posted by derrickleggett
Many times when disk failures occur, they send chatter. If your disks are in an array it will corrupt data across the entire array. It sounds like your msdb files are corrupted.Try this. Attach the msdb files, but attach it as another database name "testdb". Run a DBCC CHECKDB on the database. It should come up with some errors, but if your lucky it will be able to fix them.Backup the database and restore into msdb.If that doesn't work, I don't know what to tell you.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
That's an awesome suggestion. I'm amazed I didn't think of it.  |
|
|
|
|
|
|
|
|
MSDB Recovery