SQL Server 2008 R2 restore query

[Former Member]

I have restored a db from PRD to QAS using the DB restore process. Take Compressed DB backup of PRD. Copy the .bak and proceed with the subsequent restore.  Now after the restore is finished. The data file structure on the source and destination system are the same.  Now when i go and check db02, the PRD and QAS datafile sizes are different. All 4 data files(including .mdf) on PRD(source system) show 95000 MB whereas on QAS all 4 data files show 154609 MB.

I am still a novice in SQL RDMBS so looking for any hints/suggestions you may have here.  Do i have to shrink the data files each manually or the whole DB as such or do i have to perform a reorg of sorts inorder to reclaim the additional space each data file is occupying additionally on each DB.    Also while restoring we did a regular restore. Does compressed DB restore have any specific restore processes. I could not find any guideline to this extent and hence checking with you. If my current problem has nothing to do with shriking or reorg. What else could be the problem.

Both source and target systems are on NW Release 701 SP8 and DB on 10.50.1804 (SQL Server 2008 R2). Both are 64 bit systems and the only difference is in PRD all data files reside on Clustered disks with NTFS whereas in QAS they reside on local disk with NTFS. Any help is appreciated.

Regards

Kalyan

[clas_hortien]

Hi,

what are the sizes of the files on disk (explorer). Are they there different too ? Are both SQL Server Enterprise Edition or is QAS a Standard Edition ?

Regards

  Clas

[Former Member]

Hi Kalyan,

Please see the below MSDN blog,

http://blogs.msdn.com/b/saponsqlserver/archive/2011/08/22/sql-server-page-compression-became-default...

Check may be Page compression is enabled in the prod which is not available in QA...

Thanks,

Jagadish.