Post reply

calculating the size required for backup of database

  • September 6, 2010 at 11:33 pm

    #144962

    Hi,

    How to calculate the size required to take backup of a database in mssql 2000.the results returned by sp_spaceused differ too much from the actual size taken on the disk.

    Thanks in advance

  • September 6, 2010 at 11:40 pm

    #1217105

    Run the following script in your database

    SELECT RTRIM(name) AS NameOfFile,

    size/128.0 as TotalSizeInMB,

    CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS SpaceUsedInMB,

    size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB, filename AS PhysicalLocation

    FROM dbo.sysfiles

    In the output sum the values of the column SpaceUsedInMB. This will be the size of your backup. If your database is in FULL recovery model then sum all the values in the column and if it is in SIMPLE sum the values of only your data files.

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

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