Wednesday, June 11, 2008

How to Deploy Your Database along with Your App

I can recommend several ways to deploy your database along with your app
  • easiest way is using some application like Red-Gate SqlPackeger or Easy SQL Deploy Plus . if you don't have time or you are afraid of forgetting something use these software they have a good trial time so you can choose with eyes wide open. with this tools you can even insert pre required data in tables.
  • You can use database scripting to create a new database on the destination machine. after getting the script run it using your desired language, for example by SqlCommand on .Net, remember there should be some order in executing the queries, for example you should create database first then tables,views,stored procedures. the order can be different on different situations for example if you have any user defined data type that used in a table you should create it before creating table, be careful about function and other things. do not forget to insert data if necessary by some INSERT command
  • Another possible way is using attach/detach feature, right click on your database name and from Tasks 's submenu select Detach... and then OK, make sure you know the location of files before detaching. now we should attach it on destination machine if destination machine has management studio or any other tool for attaching database on it just use them another way to attach database is coding, create a WinApp/ConsoleApp in your desired language and using "CREATE DATABASE" attach your not use "sp_attach_db" because it will be removed in a future version of Microsoft SQL Server. one of the benefits of this method is transfering every thing(almost) to distination db like table data or CLR related things
  • You can also use backup/restore feature to deploy your database. take a backup of database (in the management studio right click on the database and select Tasks and then backup... hit the Add button, select where to save backup file and then OK) like the attach/detach you can use management studio and other tools to restore database on destination machine, if you using management studio right click on databases node and select Restore Database... enter the database name in "to database" field select from device, click the browse button that is indicated by "..." click Add button select your file (the backup file) hit the OK in "Restore Database" dialog check the database you have selected and then OK. for doing this programatically you can just use RESTORE DATABASE . like the last method most of your database info will be transfered to destination machine.