Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

calculating the size required for backup of database Expand / Collapse
Author
Message
Posted Monday, September 6, 2010 11:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 7, 2010 10:40 PM
Points: 1, Visits: 12
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
Post #981319
Posted Monday, September 6, 2010 11:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, March 8, 2014 8:39 PM
Points: 396, Visits: 483
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.
Post #981322
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse