3

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?

Major Productions
  • 185
  • 6
  • 17

3 Answers3

3

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.

DanBig
  • 11,423
  • 1
  • 29
  • 53
3

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.

Top__Hat
  • 962
  • 1
  • 7
  • 12
  • Am I correct in assuming SQL Authentication is the same as Form Authentication? – Major Productions Apr 15 '11 at 00:03
  • 1
    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
2

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.

Richard West
  • 2,978
  • 12
  • 44
  • 49