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.
Dig Deeper on SQL Server Backup and Recovery
Avoid restoring your SQL Server database to solve the SQL Server error message "server out of memory." The problem could be the memory settings.
Restore backups in SQL Server 2000 and SQL Server 2005 with this command.
Back up tables from a SQL Server database to a file with these commands.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.