Re-create SQL Server database from aspm project

Tips submitted by ASPMaker users

Re-create SQL Server database from aspm project

Postby alainpp » Sat Oct 24, 2009 11:23 pm

This is to re-create a SQL Server database from your aspmaker project.

Yesterday I did lose one of my SQL server's databases (don't ask why or how... I did, and yes I tried to use a recovery file program, also 3 and nothing), the project was ready to use, all files were generated, modified.... ready. That database had like 50 tables and each table 20 to 30 fields. I could recreate the database manually using aspmaker but then I noticed that aspmaker save all projects in a XML format and I did create an ASP code to generate a script that re-creates that database.

NOTES:

1. This is only to re-recreate a SQL Server Database, not access.
2. This does not recreate Views on your database, it will generate them as table, so, you need to delete the generated code for those Views and do it again manually.
3. If you use Dynamic user levels, you will need to delete that code too and recreated them on aspmaker.
4. You only need to change the path to your aspm project and run the code on your browser.
5. Not all Data type are supported, only: smallint, int, float, money, datetime, nvarchar, nvarchar(max). You can add others, just modify the code.

Enjoy and post some comments

********************************************************************************************************************************************


<%

set xmldoc_release = Server.CreateObject("Microsoft.XMLDOM")
xmldoc_release.async=false
xmldoc_release.setProperty "ServerHTTPRequest", true ' *** esto es por si el xml esta en otro servidor, o sea en un http
xmldoc_release.load("C:\inetpub\wwwroot\aspm_project.agp")
xPath = "//Table"
set Tables = xmldoc_release.selectNodes(xPath)


for each Table in Tables '** este loop es por si el release tiene mas de 1 disco

x_tabla = Table.GetAttribute("TblName")

x_script_sql_principio = x_script_sql_principio&" CREATE TABLE [dbo].["&x_tabla&"](<br>"

for each Field in Table.childNodes 'tracks

' ********* FldType
' smallint = 2
' int =3
' float = 5
' money = 6
' datetime = 135
' nvarchar = 202
' nvarchar(max) = 203, aqui debo poner max en vez del valor que pone aspm que es -1 para infinito
'
' si field es key o no FldIsPrimaryKey valores: 0/1
' [int] IDENTITY(1,1) NOT NULL

if int(Field.GetAttribute("FldType"))=int(2) then ' smallint
x_field_type = "[smallint] NULL"
elseif int(Field.GetAttribute("FldType"))=int(3) then ' int
if int(Field.GetAttribute("FldIsPrimaryKey"))=int(0) then ' no es el key primario
x_field_type = "[int] NULL"
else ' si es el key primario
x_field_type = "[int] IDENTITY(1,1) NOT NULL"
' genero el script sql para key
x_script_key = "CONSTRAINT [PK_"&x_tabla&"] PRIMARY KEY CLUSTERED<Br>(<Br>["&Field.GetAttribute("FldName")&"] ASC<br>)"
end if
elseif int(Field.GetAttribute("FldType"))=int(5) then ' float
x_field_type = "[float] NULL"
elseif int(Field.GetAttribute("FldType"))=int(6) then ' money
x_field_type = "[money] NULL"
elseif int(Field.GetAttribute("FldType"))=int(135) then ' datetime
x_field_type = "[datetime] NULL"
elseif int(Field.GetAttribute("FldType"))=int(202) then ' nvarchar
if int(Field.GetAttribute("FldIsPrimaryKey"))=int(0) then ' no es el key primario
x_field_type = "[nvarchar]("&Field.GetAttribute("FldSize")&") NULL"
else ' si es el key primario
x_field_type = "[nvarchar]("&Field.GetAttribute("FldSize")&") NOT NULL"
' genero el script sql para key
x_script_key = "CONSTRAINT [PK_"&x_tabla&"] PRIMARY KEY CLUSTERED<Br>(<Br>["&Field.GetAttribute("FldName")&"] ASC<br>)"
end if

elseif int(Field.GetAttribute("FldType"))=int(203) then ' nvarchar(max)
x_field_type = "[nvarchar](max) NULL"
else ' no encontre, pongo nvarchar(50)
x_field_type = "[nvarchar](50) NULL"
end if

x_script_sql_medio = x_script_sql_medio&" ["&Field.GetAttribute("FldName")&"] "&x_field_type&","&"<br>"

next

x_script_sql_medio2 = x_script_sql_medio2&x_script_key&"WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br>) ON [PRIMARY]"

x_script_final = x_script_final&x_script_sql_principio&x_script_sql_medio&x_script_sql_medio2&"<br><br>"

x_script_key = ""
x_script_sql_principio = ""
x_script_sql_medio2 = ""
x_script_sql_medio = ""

next

response.write x_script_final

%>
alainpp
 

Return to User Submitted Tips (ASPMaker)