June 23, 2004 at 9:26 am
Hi Everyone,
I was redifining the backup procedure and I found that the msdb and master databases recovery options are set to 'Simple'. Is that ok? I do think that Sql Server installed those options by default but I wonder why the master database is set to 'Full'.
Do I really need transaction log for those databases? Can I just backup once or twice a day?
Just for your info, my production database is set to 'Full' and is backup once a day with transaction log backup every hour.
Thanks,
Erik
June 23, 2004 at 9:38 am
From Books Online:
'Only a full database backup can be performed on the master database.'.
You can change the recovery model for the msdb database, but whether a full recovery model is needed depends on the amount of entries made into this database. Note that msdb stores backup and restore history, jobs history etc.
Re your production database, you must be aware that in the worst case scenario, you will lose 1 hr worth of work. Check with management if that is acceptable.
Peter Yeoh
Need smaller SQL2K backups? Try MiniSQLBackup
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
June 24, 2004 at 2:06 am
Ah... Changing SQL Servers default options on system databases can only lead to trouble, or in some cases instant death.
Leave them as they are. Master really only needs backing up when you add or remove a database or something major like that, msdb tracks all your backups and other funky sql server settings. As a general rule I back up these system databases once a day, even if no changes occur, I can't see any reason to back them up more often than that, it's your other databases with the data you need to keep.
Master will get you back up and running quickly should your server die, but its very installation dependant (ie: you can't restore master from one server over another that's been setup different and expect SQL Server to boot). If you installed SQL Server in d:\mssql on box#1 and in e:\MicrosoftSQLServer on box#2 and copied master from #1 to #2 it would die. But yeah, if you suffer a hdd failure and need to go back a bit from tapes, master will help you big time.
Julian Kuiters
juliankuiters.id.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply