1

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 ?

  • 3
    If the database is 2TB then you need 2TB to restore it...no choice unless you purge data from it first and shrink. – user507 Jun 9 '16 at 1:51
5

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

| improve this answer | |
  • @Shanky..have few more questions. As Data files are scattered around many drives for a database..is there a way i can backup each file and combine together a whole backup to restore or restore one by one ? – Puskar Jun 16 '16 at 23:38
3

You will need at least the same amount of space on your new server. If you are restoring it from disk you will also need the space for the backup files in addition to the restored files. The other thing you can check is to see how much free space is available in the physical files. If there is significant free space and you don't anticipate needing that space you could shrink the files prior to taking the initial backup. However keep in mind that doing a shrink causes fragmentation so you will need to leave some free space to perform a reindex.

I think you best bet is to get additional drive space allocated to your new server.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.