Post reply

Backup best practices help required

  • 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?

  • May 19, 2009 at 5:35 am

    #996841

    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

  • May 19, 2009 at 5:40 am

    #996846

    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.

  • May 19, 2009 at 6:55 am

    #996904

    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??

  • May 19, 2009 at 7:04 am

    #996910

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

  • May 19, 2009 at 7:08 am

    #996917

    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.

  • May 19, 2009 at 7:12 am

    #996922

    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.

  • May 19, 2009 at 7:22 am

    #996936

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

  • May 19, 2009 at 7:25 am

    #996943

    Ok, many thanks for your help, much appreciated.

  • May 19, 2009 at 8:27 am

    #997023

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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