I have a SQL Server database on a production server. The size of database is growing very fast and now going to reach 40 GB now in one and a half year (Above expectation). I am not database admin but have some relevant responsibilities. I checked the software and database. With my findings, the temp data is being removed and scripts will be modified accordingly to handle this size of data issue.
Now I want to reduce the database size. I tried shrink command to reduce the size but it reduces the database not more than 2 percent.
When I export its data using the Import-Export tool in SQL Server Management Studio, the target database filled up with complete data. And the size of target database reduced dramatically around below 50% (i.e. goes to 14 / 15 GB).
My process for this import export is as below:
Generate scripts for tables/functions/procedures creation from source database
Use those scripts on blank target database to create same tables (with constraints), functions and procedures
Then disable all constraint on target database
Use SQL Server’s Import/Export tool and import data from source to target database
After completion of importing of all data, enable all constraints on target database
Done
This process make the exact replica of source database. The target database however comes with very small in size when I compare it with the source database. After this import/export process, when I remove the temp data, and then shrink the database, the size of database reduced to below 10GB only. (I performed this process on test server)
My question is, can I fully trust this new target database? Can I replace the source database with this target database and use it on production server?
