Backup best practices help required

Question

Backup best practices help required

artoo

SSC-Addicted

Points: 445
More actions
May 19, 2009 at 5:29 am

#204386

I have an instance of SQL Server 2000 that has several production databases on it. Is it better to have one maintenance plan to backup all databases at once or have separate plans for each database?

Viewing 10 posts - 1 through 9 (of 9 total)

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

CrazyMan SSChampion Points: 12560 More actions · Answer 1

This is a very open Question, Answers would be depends, can you give a bit more information about the database numbers and sizes, that will be more helpful

artoo SSC-Addicted Points: 445 More actions · Answer 2

There are 6 databases, 5 of them are between 50MB and 100MB and aren't used intensively, the sixth is about 500MB and is written to all day.

CrazyMan SSChampion Points: 12560 More actions · Answer 3

As per the database sizes, this should take only seconds to complete, are you backing directly to tape drives, is that over the network or attached to the SQL server, If its is over the network what the network speed? it seems that you are backing up to your local disk and then copying to the tape??

artoo SSC-Addicted Points: 445 More actions · Answer 4

I'm backing up to a USB external disk. Network is 100Mbps.

CrazyMan SSChampion Points: 12560 More actions · Answer 5

This is a bit Odd, try this, try to back up from to your local disk, then try to copy the backup manually to the USB disk, if this takes longer then there is a problem with your network bandwidth.

As and aside, its not safe to back database up on a USB disk.

artoo SSC-Addicted Points: 445 More actions · Answer 6

Sorry, perhaps I haven't been clear. All I want to know is whether it is better to have lots of separate backup jobs (i.e. one for each database) or one job to cover the lot.

CrazyMan SSChampion Points: 12560 More actions · Answer 7

Ahh, I would say separate back up for each database.

artoo SSC-Addicted Points: 445 More actions · Answer 8

Ok, many thanks for your help, much appreciated.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 9

I don't want to get into the USB discussion, but I'd recommend having a single backup process for the server. It should backup the databases seperately, yes, but you should have just one process to call and monitor.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning