backup file size

  • check exact size of your database not right click on database then property and check size not from option.

    SELECT DB_NAME(database_id) AS DatabaseName,

    Name AS Logical_Name,

    Physical_Name, (size*8)/1024 SizeMB

    FROM sys.master_files

    WHERE DB_NAME(database_id) = 'AdventureWorks'

    GO

  • zahid_7777 (8/10/2012)


    Right clicking in the database selecting task then backup, assigning job by selecting script action to job generated by sql server and schedule. Not maintenance plan or writing any script personally. I am steel learning. But may be database backups are growing because service is adding the new backup with the old backups not replacing older one. I have selected expired on 0 (zero) days.

    The sql server default is NOINIT which will write all backups into the same file, if you're using the same filename each time. Either

    • use a different filename each time by dynamically appending the date maybe?
    • Or use INIT to overwrite the backup set (not the best way)

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • Thanks. I will do as you suggested. I hope this is very useful. One thing,

    * should I mention zero day or more days for expire last backup option to reduce space.

  • You can do, but i don't use it.

    I, and I'm sure many others, create files based on date, much easier to manage.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • Backups by date/time are definitely easier to deal with, especially if your SQL Server is toasted or you're at a DR site and you want to see what you have.

    Take a look at the maintenance solutions from Ola Hallengren[/url] and/or SQLFool - I use a slightly modified version of Ola's scripts for backups and for cleaning up older files.

  • I think your backup job is not over writing the old backup or you are not deleting the old one’s, that's the reason your backup file is increased. Try to do a new backup manually to the same drive/disk with diff name, you may find the difference.

  • Thank you very much.

Viewing 7 posts - 16 through 21 (of 21 total)

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