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.
Best Answer
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.
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.