Run the following script:
CREATE DATABASE sizetest
GO
USE sizetest
GO
CREATE TABLE data (col1 char(8000))
INSERT INTO data VALUES ('A')
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
INSERT INTO data SELECT * FROM data
GO
BACKUP DATABASE sizetest TO DISK = 'e:\temp\sizetest_1.bak'
DROP TABLE data
BACKUP DATABASE sizetest TO DISK =
'e:\temp\sizetest_2.bak' |
The first backup generates the following output:
Processed 664 pages for database 'sizetest', file
'sizetest' on file 2.
Processed 2 pages for database 'sizetest', file 'sizetest_log'
on file 2.
BACKUP DATABASE successfully processed 666 pages in 0.549
seconds (9.924 MB/sec). |
while the second backup generates the following output:
Processed 152 pages for database 'sizetest', file
'sizetest' on file 2.
Processed 4 pages for database 'sizetest', file 'sizetest_log'
on file 2.
BACKUP DATABASE successfully processed 156 pages in 0.221
seconds (5.782 MB/sec). |
Note that the second backup actually backed up less pages (156),
since we deleted all the data. The first backup file size is
7.7 MB, while the second is 2.6 MB. If you restore the
database using the second backup file, your database files would
still require the same amount of free space as the first backup (~
9 MB), because that was the size of the database files during the
time of backup.
If you want to reduce the size of the database files in this
case, you would need to run DBCC SHRINKDATABASE,
DBCC
SHRINKFILE or set the database to auto-shrink. Note
that there are caveats to doing this if the deletion and growth is
normal for the database. Constantly shrinking and growing the
database file will cause it to fragment on the disk, imposes a disk
and processor overhead during the shrinking process, and a disk
overhead again during the growth process.
Document history
6/27/2008 | Initial release. |