Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 3 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
Am I correct in assuming SQL Authentication is the same as Form Authentication? – Major Productions LLC Apr 15 '11 at 0:03
Form authentication is for accessing the webpage on IIS through something other than the IUSR account on the web server machine. SQL Authentication is when you define an account within SQL Server to use for access purposes. There is no matching account on the OS running the SQL Server instance. If you have an account listed in your connection string you are likely using SQL Authentication. if you have SSPI you are likely using Windows Authentication. – Top__Hat Apr 15 '11 at 12:22

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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