Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: I asked a new coworker why they don't backup the DB, he said it is too big(!)

    We have a production DB that is 1.6 Terrabytes. The disk got corrupted yesterday and the DB was down all day waiting for the DB to recover. I am new to this company and I am still trying to understand their processes. I asked him why we are not taking backups or having backups taken for us at the colo. He said it was too big and it would take too long.

    Granted the first backup may take a while, but couldn't we just take incremental backups after the initial? I asked about SAN and he said even with SAN we have a problem since you need to "pause" the DB in order to take the snapshot (I am not too familiar with SAN at all and I am not sure how long this "pause" would need to be for a DB this large).

    What are our options? He claimed it was ok if this DB was down for 5 days, however the business people at our office could not get some reports for clients so I am starting to think it would be a problem if it was down for 5 days.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have they tested backup compression software? Using multiple filegroups?
    Is this a transactional database? Is it unimportant?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    Hi pootle,

    Recover mode is set to simple, there is 1 file group. When we were having disk issues I asked if they had a raid setup and got this response:

    On their side it is. Fiber/3par

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The recovery model doesn't really matter much - to some schedule or other full BUs are required unless you want to restore baszzilions of files at a time.

    I admit I haven't ever needed to backup file groups so I haven't used this in prod.
    How to: Back Up Files and Filegroups (Transact-SQL)

    We do backup databases just under 1TB using backup compression software - the native backups were taking too long too. We backup to locally attached storage.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I think they should be making backups, but when you get into these sizes, there can be various challenges to take into account.

    On the relatively modest hardware I have to deal with (and I am sure some of the "big boys" will get a laugh out of this) backing up 1.5 TB of data would take around 24 hours. we have no direct connection between our SAN, and the backup server, so all of that has to go across the ethernet, even if it is 1 Gbps backbone.

    During the backup, the transaction log can not be truncated. Even if the recovery model of the database is simple. The transactions are considered "active" until the database backup finishes. This is because at the end of a full backup, the transaction log is backed up, so that any changes that were made during the backup can be rolled forward tot he point when the backup completed. In this case, the first pages of the backup are taken 24 hours before the last ones. All the changes in that 24 hours need to be available to the backup, in order to guarantee a consistent restore.

    The incremental backups (Only differential in this case, since it is SIMPLE recovery mode) will record changes made since the last full backup. These will grow over time, and only shrink down after a full backup collects up the older changes. Where to keep these backups can be a challenge, if you don't actually have a tape subsystem to store them on.

    Challenges during the restore can be just as entertaining. The restoration will take however long to copy the dat from the source tot he destination (same as the backup), but will add the recovery of the database for that period (remember that the transaction log was kept for this purpose). The restore of the database could take (depending on how hard and fast the changes come in) up to two days, but more likely somewhere on the order of a day and a half. Here, I am assuming that "down all day" really translates to "down all business day".

    If the DBA says that the database can be down for up to 5 days, then someone has told him that is how much the company values this data. No DBA can make that decision on his or her own. The DBA should be able to lay out options along with their approximate costs, benefits, and risks to the data. The business people have to pick which option they are going to pay for. Even if it is not always a hard dollar cost.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your co-worker has been "drinking the SAN cool-aid" a bit too long. The SAN vendor would like you to buy their tools and software (which is fair, that's why they're in business). There is no need to pause the database in any way, shape, or form in order to get a backup... If the backup is done right, it will have no impact whatsoever on the end user. The users will literally never be able to tell if a backup is running or has been run without help from a DBA.

    At 1500 GB, your database is big enough to justify one of the high speed backup tools that do compression of the resulting backup file. I have a VERY STRONG bias towards LiteSpeed, for many reasons. There are a number of products that vie for second place, including Red Gate and Embarcadero.

    Using the basic backup tools provided with SQL Server can backup databases that size, but using the third party tools make it easier to do any backup, and far easier to create a backup plan that makes restores both fast and simple (which tends to make them more successful)!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't have a strong bias, but we use Red Gate and (in the main) are very happy with it. And it is built by Britains, not some work shy American layabouts.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Red Gate has a good product that is a strong contender for second best. It is definitely the least expensive of the "high end" backup tools. Their support has been very lackluster for me (I speak English as my primary language), but they get the job done if you have sufficient patience (which I usually don't where restores are concerned).

    If Red Gate's backup/restore product had been built by the team the originally built the SQL Server backup and restore engine, then it would certainly be a candidate for the best "third-party" backup tool.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •