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?

    Requires Free Membership to View

    Login

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 first published in August 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top