Post reply

Backing up database before rolling out schema changes

  • June 20, 2007 at 8:04 am

    #172953

    Any help with the following would be much appreciated.

    I have a number of production SQL servers (SQL 2000, SQL service pack 4).  These are all in a 24/7 working environment so the chance of downtime for administration, maintenance and applying schema changes is extremely remote.

    The databases and transaction logs are backed up to backup devices via SQL jobs (database once a day, transaction logs initialised once a day and then backed up every 15 to 30 minutes).  Backup files are valid for 24 hours

    The business environment dictates that schema changes need to be applied with frightening regularity and before these are applied I would really like to take a full backup of the database (a snap shot of the database prior to the changes being applied), leaving the backup files created by the scheduled backup jobs valid.

    To this end I am running the scheduled transaction log backup to clear the transaction log down as much as possible and then backing up the database and verifying the backup using

    BACKUP DATABASE [database name] TO DISK = '\\[Server name]\H$\[database name] \Pre_Changes_SR_[ddmmyyyy]\[database name] _[ddmmyyyy].BAK'  with  STATS

    RESTORE VERIFYONLY FROM DISK  ='\\[Server name]\H$\[database name] \Pre_Changes_SR_[ddmmyyyy]\[database name] _[ddmmyyyy].BAK'  with  STATS

    The backup absolutely kills the server, database users are unable to login to any of the databases on the server and timeouts are received.

    Does anyone have a suggestion for a quick and low impact method for getting the backup, or am I better off just relying on the existing scheduled database and transaction log backups.  The databases are 2 gig plus

     

  • June 20, 2007 at 9:05 am

    #714135

    If you are making backups daily and transaction log backups every 15 minutes, then an additional backup should not be necessary.  You can recover forward to the point in time just before you made a change using the full backup and the transaction log backups.

    One change I would suggest is to not backup up to backup devices, but just back up to time stamped files.  Keep a rolling 72 hours of transaction log backup files as a minimum.  A backup using a maintenance plan can handle this automatically.

    I am assuming that you are doing at least a daily backup to tape of your backup files.  As long as you have the tape backups, you can recover to any point in time.

     

  • June 20, 2007 at 9:25 am

    #714143

    I like Michael's advice, though it doesn't help recoverability if a schema change happens at 5pm and you've got 60-some off logs to restore. One thing you can add is a differential backup during the day to get you closer to the restores if there's a schema problem. I might run diffs every 4 or 6 hours to let you recover quicker.

    If you move to SQL Server 2005, you can snapshot the db before a schema change, which shouldn't mess up your backups.

  • June 20, 2007 at 9:36 am

    #714151

    Many thanks for the advice

  • June 20, 2007 at 8:29 pm

    #714235

    It isn't that hard to recover from transaction log backup files, even if you have hundreds of them.  I use a script that gets the contents of a directory, loads the file names into a table, and generates a restore command script from that.

    I recommend that you prepare a script like this before you actually need it.

     

Viewing 5 posts - 1 through 4 (of 4 total)

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