SQL Azure : Migrating ASPNet Database to Sql Azure

If you ever wondered about how you will migrate your ASPNet DB to cloud, here is the solution.

1) Create a Blank  Database "ASPNet" using your SQL Azure connection.
2) Launch "Microsoft SQL Server Management Studio" and connect to your SQL Azure Database by using your credentials. Now Run following script against database created in Step. I have created this script by using "SQL Azure Migration Wizard" available at codeplex.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Applications]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Applications](

    [ApplicationName] [nvarchar](235) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [ApplicationId] [uniqueidentifier] NOT NULL,

    [Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

    [ApplicationId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Users](

    [ApplicationId] [uniqueidentifier] NOT NULL,

    [UserId] [uniqueidentifier] NOT NULL,

    [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [IsAnonymous] [bit] NOT NULL,

    [LastActivityDate] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

    [UserId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UserApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [UserApplication] FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[Applications] ([ApplicationId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UserApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [UserApplication]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Memberships]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Memberships](

    [ApplicationId] [uniqueidentifier] NOT NULL,

    [UserId] [uniqueidentifier] NOT NULL,

    [Password] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [PasswordFormat] [int] NOT NULL,

    [PasswordSalt] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Email] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PasswordQuestion] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PasswordAnswer] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [IsApproved] [bit] NOT NULL,

    [IsLockedOut] [bit] NOT NULL,

    [CreateDate] [datetime] NOT NULL,

    [LastLoginDate] [datetime] NOT NULL,

    [LastPasswordChangedDate] [datetime] NOT NULL,

    [LastLockoutDate] [datetime] NOT NULL,

    [FailedPasswordAttemptCount] [int] NOT NULL,

    [FailedPasswordAttemptWindowStart] [datetime] NOT NULL,

    [FailedPasswordAnswerAttemptCount] [int] NOT NULL,

    [FailedPasswordAnswerAttemptWindowsStart] [datetime] NOT NULL,

    [Comment] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

    [UserId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MembershipApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Memberships]'))

ALTER TABLE [dbo].[Memberships]  WITH CHECK ADD  CONSTRAINT [MembershipApplication] FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[Applications] ([ApplicationId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MembershipApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Memberships]'))

ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [MembershipApplication]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MembershipUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[Memberships]'))

ALTER TABLE [dbo].[Memberships]  WITH CHECK ADD  CONSTRAINT [MembershipUser] FOREIGN KEY([UserId])

REFERENCES [dbo].[Users] ([UserId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[MembershipUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[Memberships]'))

ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [MembershipUser]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Profiles]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Profiles](

    [UserId] [uniqueidentifier] NOT NULL,

    [PropertyNames] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [PropertyValueStrings] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [PropertyValueBinary] [image] NOT NULL,

    [LastUpdatedDate] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

    [UserId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UserProfile]') AND parent_object_id = OBJECT_ID(N'[dbo].[Profiles]'))

ALTER TABLE [dbo].[Profiles]  WITH CHECK ADD  CONSTRAINT [UserProfile] FOREIGN KEY([UserId])

REFERENCES [dbo].[Users] ([UserId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UserProfile]') AND parent_object_id = OBJECT_ID(N'[dbo].[Profiles]'))

ALTER TABLE [dbo].[Profiles] CHECK CONSTRAINT [UserProfile]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Roles](

    [ApplicationId] [uniqueidentifier] NOT NULL,

    [RoleId] [uniqueidentifier] NOT NULL,

    [RoleName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

    [RoleId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[RoleApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Roles]'))

ALTER TABLE [dbo].[Roles]  WITH CHECK ADD  CONSTRAINT [RoleApplication] FOREIGN KEY([ApplicationId])

REFERENCES [dbo].[Applications] ([ApplicationId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[RoleApplication]') AND parent_object_id = OBJECT_ID(N'[dbo].[Roles]'))

ALTER TABLE [dbo].[Roles] CHECK CONSTRAINT [RoleApplication]

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoles]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[UsersInRoles](

    [UserId] [uniqueidentifier] NOT NULL,

    [RoleId] [uniqueidentifier] NOT NULL,

PRIMARY KEY CLUSTERED

(

    [UserId] ASC,

    [RoleId] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoleRole]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))

ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [UsersInRoleRole] FOREIGN KEY([RoleId])

REFERENCES [dbo].[Roles] ([RoleId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoleRole]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))

ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [UsersInRoleRole]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoleUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))

ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [UsersInRoleUser] FOREIGN KEY([UserId])

REFERENCES [dbo].[Users] ([UserId])

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoleUser]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))

ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [UsersInRoleUser]

GO

-- BCPArgs:1:[dbo].[Applications] in "c:\SQLAzureMW\BCPData\dbo.Applications.dat" -E -n -b 10000 -a 16384

GO

-- BCPArgs:1:[dbo].[Users] in "c:\SQLAzureMW\BCPData\dbo.Users.dat" -E -n -b 10000 -a 16384

GO

-- BCPArgs:1:[dbo].[Memberships] in "c:\SQLAzureMW\BCPData\dbo.Memberships.dat" -E -n -b 10000 -a 16384

GO