Post reply

Using INIT/NOINIT with Maintenance Plan Backups

  • July 22, 2005 at 2:18 pm

    #102656

    I'm trying something new with a set of databases on an Extranet SQL Server. The reason for deviating from my standard backup procedure is that there are an ever-growing number of databases that will live on this server.

    Typically, we perform a Full backup, with INIT, on each database each morning (midnight). Later in the morning I do a transaction log backup, with INIT, (around 6AM). Beginning at 8AM, and each hour after, I do a Transaction Log backup, with NOINIT.

    With the new extranet server I decided to try creating a Maintenance plan(s) for all of the user databases. I'd really like to keep the INIT/NOINIT schedule but I haven't found any way to make that happen with the EM Maintenance Plan wizard. The one thing that I did do in EM was to delete the full and transaction logs which are older than 1 day (I suppose that this is as close as I can get to the INI/NOINIT).

    Is there something that I could add to the Full/TL Backup job steps which would allow the backup to perform the backup using INIT/NOINIT?

    Thanks

    PS I probably should mention that typically I use a Backup media - with that extranet backups I'm using a disk/directory backup. So, this is another question:

    Is there some script that could dynamically generate Backup media for all user databases within an instance and then perform the above backup type/schedules?

    Thanks again!

  • July 25, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • July 25, 2005 at 8:28 am

    #576810

    Database maintenance plans (as generated and managed within Enterprise Manager) do not support the "no init" switch on the backup command. (At least not directly; I'm not familiar with the various "backup media" options, and there may be configurable stuff in there.)

    Drilling down a bit, when you define a database maintenance plan, it creates and manages one or more SQL Agent jobs. Those jobs call xp_sqlMaint, which shells out to the sqlmaint utility, and that does all the subsequent work. This utility is documented in BOL, and is worth reviewing... but there are no [documented] parameters or switches that allow you to control init/noinit, so I think you're out of luck.

    (The "delete old files" doesn't really emulate init/noinit, it just helps to cut down the clutter and running our of hard drive space from too many backup files on the drive.)

       Philip

     

  • May 5, 2008 at 1:06 pm

    #810870

    create your maintenance task, view sql script of the task then copy it and create a new subtask with "execute sql statement" and paste it in there. There you can modify NOINIT to INIT.

  • July 15, 2008 at 6:37 am

    #843024

    Grasshopper, once I create my SQL 2000 Maint Plan (for Txn Log Bkups to run every 30 min's 5am-5pm) How do I view the SQL script as you suggest?

    (I too would like to add NOINIT to my BACKUP LOG command)

    thx ina dvance

    BT

  • July 15, 2008 at 10:36 am

    #843238

    I think script option is available only in SQL 2005.

    Manu

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

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