Export ASPNETDB data to another Database.

Question

Hi there. I am developing in Visual Web developer 2008. I have SQLEXPRESS 2005 and SQL Management Studio 2008 installed on my PC. I purchased a Database MS SQL 2008 on DiscountASP.net. Since the host provides only 1 database and my project has 2 database. One is the ASPNETDB that contains the roles and user etc (created using the Website Configuration Wizard) and the other is my database containing data to my website and is named MainDB.

As Host allows only 1 database so i exported my ASPNETDB's tables and stored procedures to my MainDB using aspnet_regsql.exe, but the problem is that stored procedures and tables are exported to my MainDB but data is not exported, i mean there are no users in the tables.

My Question is how to export everything of ASPNETDB including stored procedures, tables and data to my MainDB??

Answer 1

Make sure your machine keys match in web.config. There's a file called InstallCommon.SQL that will create the objects (if any are missing). It would probably be easiest to try out the full version of SQL2008 and use the data transfer wizard. Otherwise, one way to go would be to use this as a template for your data:

--ApplicationName   LoweredApplicationName  ApplicationId   Description

INSERT INTO [aspnet_Applications]
           ([ApplicationName]
           ,[LoweredApplicationName]
           ,[ApplicationId]
           ,[Description])
     VALUES
           ('/app/', '/app/', '0000...', NULL)
GO

--ApplicationId', 'UserId', 'UserName', 'LoweredUserName', 'MobileAlias', 'IsAnonymous', 'LastActivityDate

INSERT INTO [aspnet_Users]
           ([ApplicationId]
           ,[UserId]
           ,[UserName]
           ,[LoweredUserName]
           ,[MobileAlias]
           ,[IsAnonymous]
           ,[LastActivityDate])
     VALUES
           ('0000...', '0000...', 'DarthVader', 'darthvader', NULL, 0, '2009-12-31 20:30:10.222' )
GO


--ApplicationId RoleId  RoleName    LoweredRoleName Description

INSERT INTO [aspnet_Roles]
           ([ApplicationId]
           ,[RoleId]
           ,[RoleName]
           ,[LoweredRoleName]
           ,[Description])
     VALUES
           ('0000...', '0000...', 'Admin', 'admin', NULL )
GO


--ApplicationId, UserId, Password, PasswordFormat, PasswordSalt, MobilePIN, Email, LoweredEmail, PasswordQuestion, PasswordAnswer, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart, Comment

INSERT INTO [aspnet_Membership]
           ([ApplicationId]
           ,[UserId]
           ,[Password]
           ,[PasswordFormat]
           ,[PasswordSalt]
           ,[MobilePIN]
           ,[Email]
           ,[LoweredEmail]
           ,[PasswordQuestion]
           ,[PasswordAnswer]
           ,[IsApproved]
           ,[IsLockedOut]
           ,[CreateDate]
           ,[LastLoginDate]
           ,[LastPasswordChangedDate]
           ,[LastLockoutDate]
           ,[FailedPasswordAttemptCount]
           ,[FailedPasswordAttemptWindowStart]
           ,[FailedPasswordAnswerAttemptCount]
           ,[FailedPasswordAnswerAttemptWindowStart]
           ,[Comment])
     VALUES
           (
            '0000...', 
            '0000...', 
            'ja;difuaoihfklahjfj=', 
            1, 
            'lakijf;ladkljfg;lasd=', 
            NULL, 
            '[email protected]', 
            '[email protected]', 
            NULL, 
            NULL, 
            1, 
            0, 
            '2009-12-31 20:30:10.222', 
            '2009-12-31 20:30:10.222', 
            '2009-12-31 20:30:10.222', 
            '2009-12-31 20:30:10.222', 
            0, 
            '2009-12-31 20:30:10.222', 
            0, 
            '2009-12-31 20:30:10.222', 
            NULL
            )
GO


--UserId    RoleId

INSERT INTO [dbo].[aspnet_UsersInRoles]
           ([UserId]
           ,[RoleId])
     VALUES
           ( '0000...', '0000...' )
GO