I am using SQL Server 2000 Data Transfer Service (DTS) wizard for import database from another existing SQL Server 2000 database. I also choose "Copy objects and data between SQL Server databases" option when import.

After import finished, I saw that size of new imported database are smaller than original database. Is this normal? I worried if that any object are not imported to new database, such as stored procedure or view.

How can I make sure if the new database's object are same with old database?


Well, you created new empty tables and inserted all of the data into those tables in one shot. This means that the new tables do not have any of the fragmentation and page splits that would have happened as the original database was populated much more gradually and probably was subjected to a lot of updates and deletes. Hence, the same data will fit on a potentially much smaller number of pages, since the data is much "cleaner."

You can use a tool like Red Gate SQL Data Compare, if it still supports SQL Server 2000. There are alternatives if you search, of course, and you could write your own scripts to do it if you're really budget-conscious, but please read this first. Any of these tools (again, that support 2000) can help ensure that all of the data made it. Assuming, of course, that the source data hasn't changed substantially since you moved the data.

In all honesty, though, I would much rather perform a backup and restore than rely on all this stuff - even if it means I have some wasted space in the copy.

  • In regards to Red Gate SQL Date Compare: The official support for SQL server 2000 has been dropped, however I can confirm that it still works on SQL server 2000 instances. Source: used the tool during a SQL server 2000 DB migration januari last. – Reaces May 23 '14 at 8:35
  • I have used StarInix DB Compare, and I see that the difference is about PK and FK. I also have compare data count, and it's match one to another. Thanks for this helpfull answer. – Yusuf Irzan May 26 '14 at 4:34

Yes, this is normal. When you created the database objects and inserted data into tables, the database pages were populated serially, row after row.

Understanding Pages and Extents

When you use the database and for example delete a table, it doesn't mean that the database MDF file will become smaller for the amount of deleted data. The MDF file will contain the deleted table data, just marked to be overwritten.

There is a nice article by Brent Ozar that will help you understand the database files: How Does SQL Server Store Data?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.