Home / server / Questions / 259865
Accepted
Major Productions
Asked: 2011-04-15 13:24:25 +0800 CST

Transferring SQL Server 2008 R2 tables/data from dev box to server

  • 772

I'm nearing the end of site development, and am now facing transferring my table structure and data from my development machine to the live server. SQL Server 2008 R2 is already installed and ready to go on the live server, so at least that's out of the way, but I'm unsure how to proceed. Is there a build script, or something simpler than manually recreating the tables and inserting the data? And if there is, will it keep my db connection string the same, assuming the login info is the same?

deployment sql-server-2008-r2

3 Answers

  1. Best Answer

    Backup the database and restore it to the new server.

    Right click DB, backup to .bak Copy .bak to new server in a temp folder. Open Studio and do a restore. Pick your .bak file, give the restore the same DB name and you should be good to go.

    • 3

  2. Don't forget to create your user in the live database instance if you are using SQL Authentication. If you are using Windows Authentication you still need to map a group with your Windows/AD account or manually map it in security.

    Once your user is setup run the following query against your database to reset the user GUIDs to match you restored database.

    exec sp_change_users_login ‘auto_fix’, ‘username‘

    If you are creating a new user and mapping that for access you do not need to do this.

    • 3

  3. Your connection string will change if the production server name is different than your development server.

    Aside from that, DanBig's suggesstion of taking the backup from the development box and restoring it to the production server is excatly what I would recommend.

    • 2