Move a huge databases from one SQL Server to another

Question

I have customers databases located on my SQL Server 2008 machine. The size of these databases are around 300 GB. I would like to move them all to another SQL Server 2008.

Is there a way to shrink them before moving them? And what is the best way to move them easily?

Will I have to create same users on the new one?

Please try to provide me details as possible.

Answer 1

If you are using Enterprise edition, then you can use backup compression in sql server 2008.

In sql server 2008 R2 and up, even compression is offered in standard edition.

To move database, you can do a backup and then restore on the destination server.

Alternatively, you can just script out the schema and then move data using BCP OUT and BCP IN. You can even zip those files and move them across the destination server.

Will I have to create same users on the new one?

Yes, you have to run sp_help_revlogin. on source server and then copy the output on the destination server. This will create the same logins with the SID's from original server. So when you restore your database, you wont have to sync up your users.

One more thing to add :

Make sure that Instant File Initialization is enabled on the server. That will dramatically reduce the restore time.

Answer 2

First make sure your transaction log is as empty as possible (not small, empty). If the database is in SIMPLE RECOVERY then make sure that there are no open transactions. If the database is in FULL or BULK LOGGED RECOVERY make sure there are no open transactions then take a transaction log backup. Then you can take a full backup of the database, move the backup, then restore on the new instance.

Backups are generally considerably smaller than the database and by making sure that your transaction log is as empty as possible this will reduce the size of the backup itself. also if you are using the enterprise edition of SQL Server 2008 you can use the COMPRESSSION option of the backup command to further shrink the file.

Fair note, shrinking the database (data or log files) will have no affect on the size of the backup file and will be counter productive when you restore it.

You won't have to create new users but probably will have to create new logins. You can use any number of scripts out there to generate the logins. I have one here. Also if any of the logins already exist and are SQL Server logins they will probably not have the same SID so the users in the database will not automatically attach to them. You can use the system stored procedure sp_change_users_login to fix those cases. Windows logins will have the same SID and will automatically link up with the users in your database.