calculating the size required for backup of database

  • 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

  • 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