1

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.

4

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.

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.

  • sp_change_users_login is deprecated. You have to use ALTER USER with LOGIN – Kin Shah Feb 7 '14 at 22:21
  • 2
    Yes it's depreciated but there still isn't a good replacement for the REPORT functionality. And while depreciated it does still exist (especially in 2008) so it CAN be used. I will agree though it's time to get used to ALTER USER instead. – Kenneth Fisher Feb 7 '14 at 23:03

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.