Q
Problem solve Get help with specific problems with your technologies, process and projects.

Shrinking SQL Server 2000 backup size

Ways to reduce SQL Server backups are offered by expert Greg Robidoux. They include cutting the size of the transaction log, rebuilding clustered and nonclustered indexes and creating smaller test databases.

I have a database on SQL Server 2000. When I take a full backup of the database it generates a backup file of about 1.3 GB size, but I don't believe this DB has that much data. I only need the information for tables, Stps, views and functions, nothing else. The sole purpose of my backup is to create a test DB on my local machine. How can I reduce the size of the backup?
There is not much you can do to manage the size of the SQL Server 2000 backup files. When you create a backup, the entirety of the database is backed up. Some things to check are how large the transaction log is and whether this can be truncated to reduce the size of the transaction log. Also, you can try to rebuild your clustered and non-clustered indexes to see if reorganization reduces the number of pages that SQL Server needs to store the data.

Another approach to create a smaller database for testing is to script out the objects from your production database...

and recreate on your test database. Then just move some of the data either by using DTS or BCP.

This was last published in August 2006

Dig Deeper on SQL Server Backup and Recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close