Sql-server – Imported database file size different with original database file

sql server

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?

Best Answer

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.