Wednesday, September 2, 2009

SqlAzure Migration tool

I was wandering wether we have to wait too long to have some tool to easily transfer our DB Schema to the cloud.
The anwser came just one day ago and it is here: http://sqlazuremw.codeplex.com/
LIghtweight, simple, easy to use, this tool generates (DROP) Create t-sql statements compatible with SqlAzure. The Scripts generated are bit large (Well it depends on your schema) and it takes some time to execute all, but it works.

I am still searching for a way to easily (or not, but automated) TRUNCATE entire DB and recreate tables again. Everything I found over internet is kind-of useless with Azure since there are no stored procedures like "sp_MSForEachTable" or so. The closest match I found is executing some statement like this:

DECLARE @TableName NVARCHAR(2048)
DECLARE @ConstraintName NVARCHAR(2048)
DECLARE Constraints CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
OPEN Constraints
FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']');
FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName;
END
CLOSE Constraints
DEALLOCATE Constraints

DECLARE Tables CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
OPEN Tables
FETCH NEXT FROM Tables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DROP TABLE [' + @TableName + ']');
FETCH NEXT FROM Tables INTO @TableName;
END
CLOSE Tables
DEALLOCATE Tables

But it works only partially on Azure and if you do not have lots tables and constraints. For some reason Azure can't drop some of the constraints.

No comments: