Backing up System Databases

Question

Backing up System Databases

Richard Cranston

Hall of Fame

Points: 3680
More actions
November 17, 2004 at 1:23 pm

#162812

I set up a Job that executes the following to backup User Databases:
BACKUP DATABASE [BINT_Prod] TO DISK = N'\\ribackupsrv\BACKUP\BINT_Prod' WITH INIT , NOUNLOAD , NAME = N'BINT_Prod backup', SKIP , STATS = 10, NOFORMAT
This backs up the User Database "BINT_Prod" to a server "ribackupsrv".
What is the best way to backup the system databases in a Job?
Can I use this script to backup the "Master", "msdb" Database?
Is it imperative to backup the "model", "Northwind", "pubs" Databases also?
Thanks for the feedback!!!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 1

BACKUP DATABASE [master] TO DISK = N'\\ribackupsrv\BACKUP\masterfull.bak' WITH INIT , NOUNLOAD , NAME = N'master full backup', SKIP , STATS = 10, NOFORMAT

BACKUP DATABASE [model] TO DISK = N'\\ribackupsrv\BACKUP\modelfull.bak' WITH INIT , NOUNLOAD , NAME = N'model full backup', SKIP , STATS = 10, NOFORMAT

model should only be backuped once, and when you change something in it. Because it is the basic db on which all other db's you create are based. Extra backups only use space but then again "space" ?

and last : (because it contains the backup history)

BACKUP DATABASE [msdb] TO DISK = N'\\ribackupsrv\BACKUP\msdbfull.bak' WITH INIT , NOUNLOAD , NAME = N'master full backup', SKIP , STATS = 10, NOFORMAT

If on a production server : remove pubs and northwind !

If on a development server : you might keep them because some MS examples are using pubs or northwind.

You can recreate them from your sql2k-disk if needed. I would not create backups.

BTW I always make local backup-files and then xcopy them to a secured place. When the xcopy fails, I still have a valid backup. In your situation, when the remote server is down or unavailable, your backups fail !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 2

Richard Cranston

Hall of Fame

Points: 3680

November 17, 2004 at 1:54 pm

#530548

THANK YOU!!

Jimmy Jen SSCrazy Points: 2915 More actions · Answer 3

If you are going to backup more than once and you don't want to overwrite the existing backup file, you can use the following script which adds date and time to the backup filename. See example below:

Declare @backup_name varchar(500), @backup_date varchar(14)

--the following SET statement returns yyyymmddhhmmss

set @backup_date = convert(varchar(8), getdate(), 112) + left(convert(varchar(8), getdate(), 108), 2) + substring(convert(varchar(8), getdate(), 108), 4, 2) + right(convert(varchar(8), getdate(), 108), 2)

set @backup_name = '\\ribackupsrv\BACKUP\Master_db_' + @backup_date + '.BAK'

Backup database Master to disk = @backup_name WITH INIT, NOUNLOAD, NAME = 'master full backup', SKIP, STATS = 10, NOFORMAT

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 4

Thanks again for that script information about the datetime stamp!!

By the way, how often should master and msdb be backed up; I currently back up our User Databases once a day.

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 5

I also backup master and msdb everytime a scheduled backup is taken (full/diff/log) of any userdb.