I remember a few years back when I used to grumble about writing a SQL query that included a JOIN. For some reason back then I could never get my head around JOINs.
Today is a totally different story. I wrote a deployment script that did the following:
1) dropped a database,
2) restored a database from a backup file,
3) Created a database table,
4) copied the contents of a table into this table,
5) disabled and re-enabled some triggers,
6) dropped some constraints and columns from a table
7) and renamed some columns.
It’s not like I woke up this morning with SQL Server knowledge. For many years I have been adding extra features and changing functionality to our line of business database and over time my SQL confidence has grown.
For every single one of the steps above I googled and looked up the SQL syntax (every time I write an insert or update statement I look up the syntax, one day it will stick in my brain!) I think the main reason is once you have used SQL for a while you get to see how it works and can split it down into small steps.
As I blogged the other day I am currently working on improving a bad database and today I wanted to test the deployment process. All my changes are in a SSDT project so I took a backup of my database and tried to publish.
Error! Your changes will result in data loss, no surprises there as I was expecting that error. The main culprit for this was a trigger I wrote but I didn’t find that out until the end of the day.
As I was working on a backup I could do what I like including destructive changes. I tried creating a pre deployment script. I thought if that runs first, I can persuade SSDT that it matches SQL Server and therefore no data is being lost.
This didn’t work, but I had created the start of my script mentioned above. I had steps 3 and 4. Lets try running my script first and then try running the deployment of my SSDT project. To get this to work I am going to have to rebuild my database a few times, I did this manually with SQL Management studio a couple of times until I thought, I could add this to my script – that’s step 1 and 2 done.
Then I got some errors about triggers so I tried disabling them and re-enabling them after it had finished. That’s step 5.
Finally I needed to drop some columns, this resulted in an error about a constraint. A few trial and error run through’s to find out which constraints I needed to drop and then I could drop the columns.
The last step was to rename some columns. I thought after this I can run publish from visual studio. No it still complained about data loss. I dropped another trigger and then I could run everything without any errors.
Wohoo! Aren’t I clever. A simplified version of my code is below
USE master –can’t drop the database if its open!
DROP DATABASE DBName
RESTORE DATABASE DBName FROM DISK=’E:\DBName.bak’
MOVE ‘DBName_dat’ TO ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBName.mdf’,
MOVE ‘DBName_log’ TO ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBName.ldf’,
USE DBName –swap back to the database you just restored
CREATE TABLE TName2 (
ID INT NOT NULL,
Name NVARCHAR(50) NOT NULL,
Address NVARCHAR(50) NOT NULL,
City NVARCHAR(50) NOT NULL,
Postcode NVARCHAR(50) NOT NULL)
DISABLE TRIGGER TR_Trigger ON TName1
INSERT INTO TName2 (Id,Name)
SELECT Id, Name FROM TName1
ALTER TABLE TName1
DROP CONSTRAINT [DF_Id], [DF_Name]
ALTER TABLE TName1
DROP COLUMN Id, Name
sp_RENAME ‘TName1.City’, ‘Area’ , ‘COLUMN’
–drop that last trigger
DROP TRIGGER TR_Trigger2
ENABLE TRIGGER TR_Trigger ON TName1
This post has already been read 916 times!