Database recovery via Coldfusion SQL console
Recently one of my machines crashed which was the only local machine that had a certain MSSQL database on it. It's a rather old and unused system, but missed greatly when it's not working.
For whatever reason that I won't get into here I had an issue with an ISP where I couldn't get access to my database to copy it back locally. This really annoyed me but I was determined not to let this beat me.
I still had access to the site where I had created a SQL console (a page where I could enter SQL code and it would get executed). I decided that I could use this to query the database and then with this data I could create a script to create the database locally.
Here is the SQL console:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>SQL Console</title>
<link rel="stylesheet" href="sql_console.css" type="text/css">
</head>
<cfif IsDefined("FORM.SQL")>
<!--- Execute submitted SQL statement --->
<cftry>
<cfquery name="SQL" datasource="#dbdatasource#">
#PreserveSingleQuotes(FORM.SQL)#
</cfquery>
<cfcatch>
<cfset ErrorMessage = "#CFCATCH.message#<br>#CFCATCH.detail#">
</cfcatch>
</cftry>
</cfif>
<body style="margin:5px"><cfoutput>
<form name="console" action="console.cfm" method="post">
<table border="0" cellpadding="4" cellspacing="1" bgcolor="##000000" width="100%">
<!--- SQL input --->
<tr>
<td bgcolor="##99CCCC" valign="top">SQL</td>
<td bgcolor="##FFFF99" colspan="2" valign="top"><textarea name="sql" cols="100" rows="10"><cfif IsDefined("FORM.SQL")>#FORM.SQL#</cfif></textarea> <input type="submit" value=" Run > "></td>
</tr>
<!--- SQL messages/errors --->
<tr>
<td bgcolor="##99CCCC" rowspan="2" valign="top">Messages</td>
<td bgcolor="##CCFFCC" valign="top">Messages</td>
<td bgcolor="##CCFFCC" valign="top">Errors</td>
</tr>
<tr>
<td bgcolor="##FFFFCC" valign="top" width="50%">
<cfif IsDefined("FORM.SQL") and not IsDefined("ErrorMessage")>
Query executed successfully
<cfif IsDefined("SQL.RecordCount")><br>#SQL.RecordCount# records returned/processed</cfif>
<cfelse>
</cfif>
</td>
<td bgcolor="##FFFFCC" valign="top" width="50%">
<cfif IsDefined("ErrorMessage")>
#ErrorMessage#
<cfelse>
</cfif>
</td>
</tr>
<!--- SQL results --->
<tr>
<td bgcolor="##99CCCC" valign="top">Results</td>
<td bgcolor="##006699" colspan="2" valign="top">
<cfif IsDefined("FORM.SQL") and IsDefined("SQL.ColumnList")>
<table border="0" cellpadding="3" cellspacing="0">
<tr>
<cfloop index="col" from="1" to="#ListLen(SQL.ColumnList)#"><td bgcolor="##006699">#ListGetAt('#SQL.ColumnList#', col)#</td></cfloop>
</tr>
<cfset bgcolor="006699">
<cfloop query="SQL">
<tr>
<cfloop index="col" from="1" to="#ListLen(SQL.ColumnList)#"><td bgcolor="###bgcolor#">#Evaluate("SQL.#ListGetAt('#SQL.ColumnList#', col)#")#</td></cfloop>
</td>
</tr>
<cfset bgcolor=Iif(bgcolor eq "006699", DE("003366"), DE("006699"))>
</cfloop>
</table>
</cfif>
</td>
</tr>
</table>
</form>
</cfoutput></body>
</html>
<html>
<head>
<title>SQL Console</title>
<link rel="stylesheet" href="sql_console.css" type="text/css">
</head>
<cfif IsDefined("FORM.SQL")>
<!--- Execute submitted SQL statement --->
<cftry>
<cfquery name="SQL" datasource="#dbdatasource#">
#PreserveSingleQuotes(FORM.SQL)#
</cfquery>
<cfcatch>
<cfset ErrorMessage = "#CFCATCH.message#<br>#CFCATCH.detail#">
</cfcatch>
</cftry>
</cfif>
<body style="margin:5px"><cfoutput>
<form name="console" action="console.cfm" method="post">
<table border="0" cellpadding="4" cellspacing="1" bgcolor="##000000" width="100%">
<!--- SQL input --->
<tr>
<td bgcolor="##99CCCC" valign="top">SQL</td>
<td bgcolor="##FFFF99" colspan="2" valign="top"><textarea name="sql" cols="100" rows="10"><cfif IsDefined("FORM.SQL")>#FORM.SQL#</cfif></textarea> <input type="submit" value=" Run > "></td>
</tr>
<!--- SQL messages/errors --->
<tr>
<td bgcolor="##99CCCC" rowspan="2" valign="top">Messages</td>
<td bgcolor="##CCFFCC" valign="top">Messages</td>
<td bgcolor="##CCFFCC" valign="top">Errors</td>
</tr>
<tr>
<td bgcolor="##FFFFCC" valign="top" width="50%">
<cfif IsDefined("FORM.SQL") and not IsDefined("ErrorMessage")>
Query executed successfully
<cfif IsDefined("SQL.RecordCount")><br>#SQL.RecordCount# records returned/processed</cfif>
<cfelse>
</cfif>
</td>
<td bgcolor="##FFFFCC" valign="top" width="50%">
<cfif IsDefined("ErrorMessage")>
#ErrorMessage#
<cfelse>
</cfif>
</td>
</tr>
<!--- SQL results --->
<tr>
<td bgcolor="##99CCCC" valign="top">Results</td>
<td bgcolor="##006699" colspan="2" valign="top">
<cfif IsDefined("FORM.SQL") and IsDefined("SQL.ColumnList")>
<table border="0" cellpadding="3" cellspacing="0">
<tr>
<cfloop index="col" from="1" to="#ListLen(SQL.ColumnList)#"><td bgcolor="##006699">#ListGetAt('#SQL.ColumnList#', col)#</td></cfloop>
</tr>
<cfset bgcolor="006699">
<cfloop query="SQL">
<tr>
<cfloop index="col" from="1" to="#ListLen(SQL.ColumnList)#"><td bgcolor="###bgcolor#">#Evaluate("SQL.#ListGetAt('#SQL.ColumnList#', col)#")#</td></cfloop>
</td>
</tr>
<cfset bgcolor=Iif(bgcolor eq "006699", DE("003366"), DE("006699"))>
</cfloop>
</table>
</cfif>
</td>
</tr>
</table>
</form>
</cfoutput></body>
</html>
With this is place I ran the query to extract the database data for the tables:
select ist.table_name, column_name, ordinal_position, data_type, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, IST.table_type
from information_schema.columns ISC
inner join INFORMATION_SCHEMA.TABLES IST on ISC.table_name = IST.table_name
order by 1,3;
from information_schema.columns ISC
inner join INFORMATION_SCHEMA.TABLES IST on ISC.table_name = IST.table_name
order by 1,3;
I then copyed this data from the server page output into Excel. I then did a little bit of tidying and saved it as a CSV file locally.
Then I created the coldfusion script to create the database tables on my local server:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Create Table</title>
</head>
<body>
<p>We create the table here:</p>
<cfset DSN = "youDSN" />
<cfset primaryKeySet = 0 />
<cffile action="read" file="yourPath\yourCSVFileName.csv" variable="dataSchema">
<cfset currentTable = "" />
<!---loop over the columdata --->
<cftransaction>
<cftry>
<cfloop index="columnData" list="#dataSchema#" delimiters="#chr(10)#" >
<!--- ignore the CSV header line --->
<cfif listgetat(trim(columnData),4) NEQ "TABLE_NAME" AND listgetat(trim(columnData),5) NEQ "VIEW">
<br />Process: <cfoutput>#columnData#</cfoutput> <br />
<!--- n.tong@talkwebsolutions.co.uk | check to see if the database has been created --->
<cfif currentTable NEQ listgetat(trim(columnData),4)>
<cfset currentTable = listgetat(trim(columnData),4)>
<br /><strong style="color:#0000FF">NEW TABLE </strong> <strong style="color:#FF0000"><cfoutput>#listGetAt(columnData,4)# - </cfoutput></strong>
<!--- as this is a new table/view we try to create the 1st field as the primary key, if this fails then we just create the table with no primary key--->
<cftry>
<cfquery name="qCreateUserListTable" datasource="#DSN#">
CREATE table [#trim(currentTable)#] (
#listGetAt(columnData,1)# #ucase(listGetAt(trim(columnData),2))# IDENTITY (1, 1) NOT NULL
)
</cfquery>
<cfcatch type="database"> [NO PRIMARY KEY SET]
<cfquery name="qCreateUserListTable" datasource="#DSN#">
CREATE table [#trim(currentTable)#] (
#listGetAt(columnData,1)# #ucase(listGetAt(trim(columnData),2))# <cfif listgetat(trim(columnData),4) EQ "NO"> NOT NULL</cfif>
)
</cfquery>
</cfcatch>
</cftry>
<cfset primaryKeySet = 1 />
</cfif>
<!--- ignore the 1st row here as it has been created with the table creation --->
<cfif primaryKeySet EQ 0>
<!--- now we have created the table we should alter the table with the new fields --->
Adding column: <strong><cfoutput>#listFirst(columnData)#</cfoutput></strong>
<cfquery name="qCreateUserListTable" datasource="#DSN#">
ALTER <cfif listgetat(trim(columnData),5) EQ "VIEW">view<cfelse>table</cfif> #trim(currentTable)#
ADD [#listGetAt(trim(columnData),1)#] #listGetAt(trim(columnData),2)#<cfif isNumeric(listLast(columnData))>(#listlast(trim(columnData))#)</cfif>
</cfquery>
<cfelse>
<cfset primaryKeySet = 0 />
</cfif>
</cfif>
<cfflush />
</cfloop>
<cfcatch type="any">
<cftransaction action="rollback">
<cfdump var="#cfcatch#" />
</cfcatch>
</cftry>
<cftransaction action="commit">
</cftransaction>
</body>
</html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Create Table</title>
</head>
<body>
<p>We create the table here:</p>
<cfset DSN = "youDSN" />
<cfset primaryKeySet = 0 />
<cffile action="read" file="yourPath\yourCSVFileName.csv" variable="dataSchema">
<cfset currentTable = "" />
<!---loop over the columdata --->
<cftransaction>
<cftry>
<cfloop index="columnData" list="#dataSchema#" delimiters="#chr(10)#" >
<!--- ignore the CSV header line --->
<cfif listgetat(trim(columnData),4) NEQ "TABLE_NAME" AND listgetat(trim(columnData),5) NEQ "VIEW">
<br />Process: <cfoutput>#columnData#</cfoutput> <br />
<!--- n.tong@talkwebsolutions.co.uk | check to see if the database has been created --->
<cfif currentTable NEQ listgetat(trim(columnData),4)>
<cfset currentTable = listgetat(trim(columnData),4)>
<br /><strong style="color:#0000FF">NEW TABLE </strong> <strong style="color:#FF0000"><cfoutput>#listGetAt(columnData,4)# - </cfoutput></strong>
<!--- as this is a new table/view we try to create the 1st field as the primary key, if this fails then we just create the table with no primary key--->
<cftry>
<cfquery name="qCreateUserListTable" datasource="#DSN#">
CREATE table [#trim(currentTable)#] (
#listGetAt(columnData,1)# #ucase(listGetAt(trim(columnData),2))# IDENTITY (1, 1) NOT NULL
)
</cfquery>
<cfcatch type="database"> [NO PRIMARY KEY SET]
<cfquery name="qCreateUserListTable" datasource="#DSN#">
CREATE table [#trim(currentTable)#] (
#listGetAt(columnData,1)# #ucase(listGetAt(trim(columnData),2))# <cfif listgetat(trim(columnData),4) EQ "NO"> NOT NULL</cfif>
)
</cfquery>
</cfcatch>
</cftry>
<cfset primaryKeySet = 1 />
</cfif>
<!--- ignore the 1st row here as it has been created with the table creation --->
<cfif primaryKeySet EQ 0>
<!--- now we have created the table we should alter the table with the new fields --->
Adding column: <strong><cfoutput>#listFirst(columnData)#</cfoutput></strong>
<cfquery name="qCreateUserListTable" datasource="#DSN#">
ALTER <cfif listgetat(trim(columnData),5) EQ "VIEW">view<cfelse>table</cfif> #trim(currentTable)#
ADD [#listGetAt(trim(columnData),1)#] #listGetAt(trim(columnData),2)#<cfif isNumeric(listLast(columnData))>(#listlast(trim(columnData))#)</cfif>
</cfquery>
<cfelse>
<cfset primaryKeySet = 0 />
</cfif>
</cfif>
<cfflush />
</cfloop>
<cfcatch type="any">
<cftransaction action="rollback">
<cfdump var="#cfcatch#" />
</cfcatch>
</cftry>
<cftransaction action="commit">
</cftransaction>
</body>
</html>
I ran the code, looked in my database and all the tables had been created. Okay I had lost the data but as it's a logging system its not that much of a loss.
Comments
[Add Comment]
In this situation possible your software was damaged or attack viruses or hackers,if so advise try-<a href="http://www.recoverytoolbox.com/sql.html">sql database recovery</a>,i used it long time and quite pleasured,because how i know it is free,and because it can be used to repair data from corrupted databases in the MS SQL Server format (files with the *.mdf can search for and single out housekeeping data that can be used to repair some data modulesextension),can process huge databases, their file size can reach 16 TB,save recovered data as SQL scripts, it is also possible to split data into files of any size,can save extracted information directly to the SQL server. It allow to recovery SQL Server faster,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista.
# Posted By Alex
| 31/10/08 13:11
| Report abusive comment
Sorry my link was incorrected-[url=http://www.recoverytoolbox.com/sql.html]sql database recovery[/url]
# Posted By Alex
| 31/10/08 13:12
| Report abusive comment
[Add Comment]
