SQL Server – Backup and Restore Large Database with Limited Disk Space

backupsql server

I am working on DB migration from server A to server B.

Server A:- DB size – 2 TB – Sql 2008R2 – Multiple Data files stored in diff drives.

Server B:- Disk space:- 999 GB – Sql 2014 Enterprise

Suggest me the best possible way.

I thought of using backup compression but later came to know it can be used only between same Sql server Editions. Is it TRUE ?

Also let me know if I use Sql compression, will there be any change between the original DB and compressed DB restored on the new DB ?

Best Answer

I would answer your specific question

I thought of using backup compression but later came to know it can be used only between same Sql server Editions. Is it TRUE ?

NO this is not true. From BOL document about Compression

Every edition of SQL Server 2008 and later can restore a compressed backup.

You can take backup with compression on any version of SQL Server that supports backup compression and can restore to any version you like provided the version on which you are restoring does not gives error about enterprise features

Also let me know if I use Sql compression, will there be any change between the original DB and compressed DB restored on the new DB ?

The database would not be changed at all, whatever would be changed is backup size. So if you have 2TB of database on source then when you take backup with compression it might be around 1 TB(A simple guess, size may vary) but when you restore the same backup on destination the size required would again be 2 TB.

Compression is just to make backup size small, it has NO affect on database original size