August 12, 2010 at 3:31 am
Hello every one,
I program a website in ASP.net in VWD2008 and using SQLserver 2005 when I deploy the MS Sql database to the production by generate a T-SQL script and the database table populate with data after a time I want to change the database table and redeploy it please tell me how to redeploy the database without losing the data .
step by step please..
Iam very grateful for your help.
I have an idea but I dont kow if its correct or not:(please tell me)
I import aproduction database and change it (for example add new table to it) then generate new T-SQL for it and deploy the new one ???
August 12, 2010 at 6:34 am
The basic idea is that you check whether an object already exists. If it doesn't you add it.
IF NOT EXISTS (
SELECT *
FROM Information_Schema.Tables
WHERE Table_Name = 'YourTable'
)
CREATE TABLE YourTable( ... )
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2010 at 11:35 am
Simple answer to that question is, it depends.
It depends on type of change you are doing, for all changes recommend full db backup for small databases; or roll back script for big databases.
Example of possible changes...
1) If adding new objects; then simply having script to create those objects is enough. It will not cause any harm to existing database.
2) If altering existing objects ..
2a) i.e. adding new fields to the tables, or dropping fields. Then you'll need to back up the table, alter the field in question, confirm changes, delete backup table (or just take full backup of db, and change the tables).
2b) i.e. altering non-table type objects, then simply running alter script.
3) Deleting objects; then you'll need to figure out any references to the object. Delete in proper order.
These are just small example, the key is document each change you have made to your database. And properly script each change, don't just rely on generating script/running script. It works fine for new database but for later modifications and alters you should create scripts for each change so it is easy to test and deploy changes.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply