I've been doing re-engineering work of a project for the past couple of months. It's bit difficult to do re-engineering work rather than doing sometihng from the scratch because you'll have a hell of lot of limitations while doing re-engineering work.
These re-engineering work included some database changes like adding relationshipes among tables, changing datatypes, lengths of existing columns. Since the project is in the production enviornment I wanted to do these changes without effecting or dropping existing data in the database. I've used couple of tools.
When you are doing changed to the database objects. As an example when you are changing the datatype of an existing column you can generate the alter script for that change. it will create the alter script without effecting your data.
Step 1 : Changing the datatype of the column RegisteredDate from datetime to smalldatetime
Step 2 : Before saving the table design, right clink on a column and click on Generate Change Script...
Step 3 : You'll see the change script on a popup window
Analyze the genereted script
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Customer
(
Id int NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NULL,
WorkPhone varchar(50) NULL,
MobilePhone varchar(50) NULL,
RegisteredDate smalldatetime NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Customer)
EXEC('INSERT INTO dbo.Tmp_Customer (Id, FirstName, LastName, WorkPhone, MobilePhone, RegisteredDate)
SELECT Id, FirstName, LastName, WorkPhone, MobilePhone, CONVERT(smalldatetime, RegisteredDate) FROM dbo.Customer WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Customer
GO
EXECUTE sp_rename N'dbo.Tmp_Customer', N'Customer', 'OBJECT'
GO
ALTER TABLE dbo.Customer ADD CONSTRAINT
PK_Customer PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
If you analyse the generated script, there are 4 major operations.
1. create a temporary table with the new column definitions.
2. Inserts data to the temporary table from the existing table.
3. Drop the existing table
4. Rename the new/temporary table to the name of the existing table.
So you won't lose any data.
This is a feature provided by SQL Server but you can
download this database publishing tool as a separate software.
