Post reply

Different size backups?

  • May 19, 2009 at 2:46 pm

    #205056

    Hi all,

    1. When I take a manual backup from SSMS, the database backup size is 0.7GB.

    2.When I schedule a nightly backup job via SQL Server Agent, using the below script:

    BACKUP DATABASE [baseline] TO DISK = N'C:\Base backup' WITH NOFORMAT, NOINIT, NAME = N'Baseline-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    The database backup size is 29GB. I use the same script from the manual backup by script action to New Query Window, I copy the script from there. I right click on job and create a new job- New step- Type (Transact SQl script TSQL) - Database (Baseline) - Command (I paste the script here from the manual backup).

    But I dont understand why the backup size is big when I schedule it.

    Please help me. Thanks a lot.

  • May 19, 2009 at 2:58 pm

    #997260

    I think it is because you are saying NOINIT.

    NOINIT

    Indicates that the backup set is appended to the specified media set, preserving existing backup sets. NOINIT is the default.

    Please double check if that is the issue

    -Roy

  • May 20, 2009 at 6:59 am

    #997557

    Hi Roy,

    I removed Noinit from the command for last night backups, I still see the big size backups this morning.

    Any other suggestions?

    Thanks alot

  • May 20, 2009 at 7:07 am

    #997564

    How big are your databases? You might check the scheduler on the job, it could be running this query lots of times.

    One question about the code:

    TO DISK = N'C:\Base backup'

    That's not exactly a file and not exactly a folder. Are you sure you're getting the backup in the location that you think you are? Maybe you need to change it to something like:

    TO DISK =N'C:\Base Backup\baseline.bak'

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 20, 2009 at 7:07 am

    #997566

    I think that noinit is the default behavior (I could be wrong about it). In any case if you don't want to preserve the old backups you can use the init option and it will overwrite the backup file and won't append a new backup into it.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 20, 2009 at 7:13 am

    #997569

    Thanks for the replies. The database sizes varies from 1GB to 50GB..

    I'll include INIT and schedule the backups tonight.

    Thanks again

  • May 20, 2009 at 7:32 am

    #997583

    You can look at the script being used by SSMS, just click on the script icon. That should allow you to figure out what it's doing differently than you.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 21, 2009 at 1:14 pm

    #998505

    Thanks, the INIT worked.

Viewing 8 posts - 1 through 7 (of 7 total)

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