Post reply

ntbackup & sql maintenance plan overlap - a bad idea?

  • June 6, 2007 at 2:22 am

    #117486

    Hi

    I'm setting up a backup plan for our MS SQL 2000 SP4 Server. What I would like to do is:

    1. Perform a full DB backup at 02:00 every day to d:\backup

    2. Perform transaction log backup every hour between 00:00 to 23:59 to d:\backup

    3. Perform an ntbackup of d:\backup\*.* at 04:30 to a network share, that has a tape drive attached to it.

    Now, at some point, the transaction log backup and ntbackup will overlap. I know the full DB backup @ 2am only takes an hour to complete, so it will be "out of the way" well before the ntbackup starts. The transaction log backup usually takes < 5 minutes to complete. Will the overlap of ntbackup and the transaction log backup cause a problem? I am toying with the idea of scheduling a simple bat script to move the current d:\backup contents to another folder, against which the ntbackup is performed, if the ntbackup/transaction log backup overlap is a problem... Any thoughts or recommendations? Thank you Steve.

  • June 6, 2007 at 4:18 am

    #711047

    Hi Steve,

    I think NT backup will automatically skip open files / files in use by default. There is a health warning on using the registry key setting to override this, as this can cause file corruption.

    http://support.microsoft.com/kb/159218

    I have a similar scenario, I only backup the .BAK and .TRN files since the last backup,  but I use BackupExec and since we have little or no overnight activity, I stop backing up the transaction log just before the nightly maintenance jobs, which are then followed by a full database backup, and restart the transaction log backup at about 6am. (I am also shipping the .TRN and .BAK files to another server where they are restored).

    I have made an effort to avoid clashes between the database backups and the tape backups as this is possible in my scenario.

    David

    If it ain't broke, don't fix it...

  • June 8, 2007 at 2:49 am

    #711607

    Hi David

    Thanks for your time & reply. Having configured the above and got the backup working to what seems a reliable state, it's been decided that replication may offer a more "available" backup method...

    Thanks again, will post if I have any problems/questions!

    Steve.

Viewing 3 posts - 1 through 2 (of 2 total)

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