July 19, 2002 at 9:24 am
billsalkin wrote:
Prior to the nightly backup job we stop SQL server (to grab the mdf/ldf files) and then restart SQL.
-------------------
I am not trying to criticize your process, but that seems drastic to me. Why not just schedule the full backup prior to when they backup the files? Just let the backup grab the .bak and .trn files. There is no need to specifically grab the .mdf and .ldf files.
There is no need to stop and start the SQL Server service.
July 19, 2002 at 9:44 am
I agree. No reason to get the mdf/ldf. Just get your backup flat files.
As far as MSDB, who knows. The only thing that's annoying is any maintenance plan that includes msdb (all databases) will report a failure because msdb can't backup the transaction log.
Steve Jones
July 26, 2002 at 8:15 am
I just leave MSDB in simple mode, but do a full backup of MSDB shortly after my other backups are finished.
I do transaction log backups of the 'real' databases each hour, and a MSDB backup at xx:15. This MSDB backup captures the latest information about all the other backups, which for me is important.
I would like to do point in time recovery of MSDB, but accept that Microsoft is working against me on this. My compromise is the hourly full backup of MSDB. I know I can restore from this, and it will have the information I am likely to really need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 6, 2003 at 12:57 pm
quote:
I really see no reason to have MSDB in "FULL" mode. It is not a transaction heavy database. And what sort of transactions are even there? It records when jobs execute and whether they were successful or not. It stores schedules. It stores alerts. It stores data about your operators. It stores DTS package data.This is not data that has changes made to it every hour. A complete nightly backup is fine for MSDB. Transaction log backups are overkill.
Make your DBA life less complicated. Leave MSDB in simple mode.
I can tell you why to use full mode! Because msdb contains all the BACKUP data. In my case that data changes every HALF hour.
I thought I was pretty well covered, with disk-to-disk full backups (150+ databases) every night (then copied to tape), and log backups every half hour. Then last April, I had my very first database-corruption crash with SQL Server. Things wouldn't restore. Before too long, I figured out that the crash had occurred WHILE BACKING UP MSDB--the worst possible time. So msdb was corrupted, and the last good one was 24 hours old.
I then had to restore the full backups, by hand, and then write quickie procedures (different for every DB, since the file names are timestamped, and the times are mostly different for each DB) to restore approximately 48 log files for each DB. Multiply by 150+ DBs and you get the picture.
If I'd had log file backups for msdb, which I could restore, things would have gone MUCH faster--even though I still would have to specify each DB by hand.
Anyway, about 24 hours later, we were up, but I swore "never again!" I wrote a stored proc that can restore any or all the DBs from the correct backup files.
Since then, I've done multiple full backups of msdb during the day, wasting substantial disk space, until I recently upgraded to SQL 2K. The upgrade messed up my backups, and caused log file backup jobs to fail, etc. (Nice of MS to warn me--NOT!) But after I fixed that, I saw that msdb could be set to full mode! Happy day!
Even the BOL says: "If you use the backup and restore history information in msdb when recovering user databases, it is recommended that you use the Full Recovery model for msdb."
But then MS put a lump of coal in my stocking, resetting it to Simple after every reboot! (A pox on all their houses!) That's when a site search pulled up this thread for me.
Now I will have to use a proc to set it to Full, AND then immediately do a full msdb backup, right after every nightly reboot. (Nightly, for various reasons--long story.)
I wish that whoever came up with this brain-dead "reset" idea, without making it an OPTION (Goodness! Elementary, my dear Watson!), could have done those database recoveries for me. Then maybe he'd see the light.
P.S. Thanks to everyone that posted solutions!
February 17, 2003 at 12:16 pm
After some more pain & suffering, I have discovered that when SQL Server 2000 (SP3) starts up, msdb is set to Simple mode a little bit AFTER the startup procs are set running! So you have to stick a delay into the startup proc (one minute does it for me, and I'm sure it could be a lot less) before you can set msdb to Full recovery mode. Aarrgghh! Hopefully this will help someone....
February 18, 2003 at 12:48 pm
FYI for anyone interested: Two MS KB articles are very informative. And it turns out a startup proc is not recommended by MS; and this situation existed in 7.0 also. Some correspondence with an MS developer, in which I pointed out just how ridiculous this is, has also shed a little more light on the subject. Here's what he said:
"The [following] fist article explains the lag as it is SQL Agent that is doing the reset (so really you should put the reset/dump into Agent as a start up job). It is doc’ed as an issue at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;257856
http://support.microsoft.com/default.aspx?scid=kb;en-us;303229
It is something we plan to sort out in the next release where we allow much greater distiction between user & system objects (and we are looking at a new backup/restore advisor that will be much easier to user & then customize than SQLMAINT....)"
"It was a nasty hack to try and put less load on DBA’s (in managing MSDB) and as you point out, in busy shops it causes more pain than it saves. Agent resets the MSDB status as part of its startup - it won’t run jobs until the reset is done."
"The next version is one heck of a lot better..."
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply