Backup User, Master and Msdb DBs to the same .bak file
-
We are currenlty running SQL Server 2k Standard Edition (SP3) on Windows 2003. I would like to do a full backup on Sunday Night and a Differential Backup on Monday thru Saturday Nights. I would like to backup the Master and Msdb Databases on each backup. I was told I could backup the User, Master and Msdb Databases to the same .bak file. Is this possible? In the Backup Dialog, do I need to click the Add Button and enter the same .bak file for each backup, and select the Append To Option. (I used the Overwrite Option on the first backup only.) Below is what I think I need to do:
On Sunday Night:
1) Full Backup of User DB, click Add Button, select .bak name and select Overwrite Option.
2) Full Backup of Master DB, click Add Button, select same .bak name and select Append Option.
3) Full Backup of Msdb DB, click Add Button, select same .bak name and select Append Option.
On Monday thru Saturday Nights:
1) Differential Backup of User DB, click Add Button, select a different .bak name (for each night), and select Overwrite Option.
2) Full Backup of Master DB, click Add Button, select same .bak name and select Append Option.
3) Full Backup of Msdb DB, click Add Button, select same .bak name and select Append Option.
Also, when I perform a restore of the .bak file would the Backup Catalog show the individual backups (user, master and msdb databases) so I may select each database individually to restore?
Thanks in advance, Kevin
-
Just because you can do something doesn't mean it's a good idea
It would probably be better to backup to seperate files. That way there is less chance of a backup corrupting the backup file and preventing you from restoring any databases. The backup files would be smaller making tape restore and file copy times shorter.
Also, given the size and importance of the system databases you should really be doinf full maintenance and backup each night.
--------------------
Colt 45 - the original point and click interface
-
While I agree with almost everything Phil said regarding the size and importance of the sytem databases and backing up to separate files, nightly backups may not be totally necessary. On some of my servers with little change (i.e. no new users, jobs, etc.), I back them up to disk once a week and to tape that same day (on other servers, I do nightly backups of the system DBs). The tapes are off-site for three weeks but the file remains on disk so in the event of hardware failure, I simply recall the tape. You really have to look at what your application's particular needs are to determine the proper times.
Terry -
Don't forget SQL Agent job history, DTS Package logging, etc...
Monthly service level reports look a bit wonky when there are gaps due to lost data
--------------------
Colt 45 - the original point and click interface -
master is so small, <25 Mb, and msdb is usually <50Mb. I do it daily and so should you. In this world of cheap disk and cheaper tape in the Gb and Tb range whats an additional 500 Mb per server per week ? By not doing so you are being 'penny wise', but 'pound foolish' !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Agree with Phil and Rudy. Additionally think about other sysadmins when you are on vacation or decide to take another job. Consistent backup file names reflecting the database names in the consistent directories will make other sysadmin's job easier. If someone else' SQL Server is down and I am asked to restore to another machine from tapes I would ask a backup support to look for the filename contains Master or filename contains MSDB on the backup tapes.
Regards,Yelena Varsha
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply