February 27, 2008 at 12:19 pm
we are in full recovery mode, but (I inherited this db) there is no full backup of msdb. Is there any way for me to roll back msdb to a point in time? We had an automated transaction that someone changed and it deleted over 100 jobs that we had. If there is any way that you can think of, please help.
February 27, 2008 at 8:06 pm
I may not have any chance.
However, you may try the following approach only if all of your transaction logs stay since creating msdb.
Have a transaction log backup;
Re-create a new msdb from other server;
Restore your transaction backup;
I never try this way. Please correct me if anyone think it is wrong.
February 27, 2008 at 11:24 pm
The LSNs wouldn't match. SQL won't do the log restore.
Besides, if there's no full backup ever been taken, you can't do a log backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2008 at 1:55 pm
Yeah, we figured it was a lost cause. I have since implemented a plan to backup msdb and master. I was shocked when I found out there wasn't one in place. Thanks guys.
February 28, 2008 at 1:59 pm
Just a note of caution - check all your servers and databases for backups. If something needs fixing and you're unprepared, they're going to what to know why! "I was shocked when I found out there wasn't one in place" won't be the best excuse for someone who just lost their data.
-- You can't be late until you show up.
March 11, 2008 at 1:11 pm
Time to put one in place now. Maintenance plan comes in handy. Just backup "all databases", or "all system" and "all user" databases. In most cases, you also don't need to worry about new user databases being created.
_____________
Donn Policarpio
March 11, 2008 at 8:22 pm
Learning from our mistakes.. i've also experience the same situation where i backup only those existing databases in the server so when there are new databases restored in the server they are not included in the backup. Lucky for me they requested a latest backup of the database not a backup days ago..
"-=Still Learning=-"
Lester Policarpio
March 12, 2008 at 8:51 am
I can sympathize. When I started my new job there was no DBA before me. No one knew that you needed to back up the system databases so I immediately created a job to do that on all servers. Just another reason why it is important to have a knowledgable DBA around.:hehe:
March 13, 2008 at 7:21 am
I agree with maintenance plans. All system databases weekly, all user databases, minimum, nightly (that's OUR strategy and may not fit your needs). With All user databases checked, if someone creates a database, it's automatically included in the job. So if you aren't the only one creating the databases, you're still covered.
-- You can't be late until you show up.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply