I created a procedure to copy a SQL database to another database in the same instance. It works pretty simple by first creating the database schema with all tables, indexes etc. in a new database and then inserting the rows from the original database tables using INSERT INTO NewDatabase.dbo.Tablename (field1, field2...) SELECT field1, field2... FROM OriginalDatabase.dbo.Tablename.
That works pretty well, however when looking the database size the new database is much smaller in disk size than the original database. I counted all records in all tables and they all are the same, so the data is all the same. Only the size difference concerned me, at first it looks like a loss of information. Maybe somebody has some insight what SQL Server stores besides of the actual data and for what purpose, and should I be concerned about the size difference of the database copy?
Christian Isberner
Software Consultant