The Surveillance monitoring and management product (ASP.NET/SQL Express), a browser based application, we are releasing as a product (on-premise solution) and the same is also available hosted as a SaaS Application.
So for both scenarios, i wanted to develop a feature to allow customers to backup and restore their data and the same i wanted to use in software install kit, when we do software upgrade.
There are many solutions i read in the internet, which pretty much goes over, hosting the web application and do the backup/restore using backend database tools, or use bulk copy/insert. Or there are samples of writing code that will very specific to my application.
The code I wrote for this application, I feel very generic and could be used for any similar size (data) application. Ofcourse there is lot of room for improvement.
The concept:
We have 2 stored procedures, ExportDBAsXML and ImportDBFromXML, the ExportDBAsXML exports all the data in the database to a xml string (how big size this can handle.. i have to test.. but so far it is working out good for me), and ImportDBFromXML imports thata data from the xml string (exported from the ExportDBAsXML) to the database.
Two Sql Scripts used during the installation for software upgrade, the backup.sql and restore.sql uses the ExportDBasXML and ImportDBFromXML respectively.
The Code is my documentation.. so mail me.. if its not clear enough…
——————————————————————————–
Backup.sql
———————————————————————————
USE [YOUR DATABASE]
DECLARE @return_value int,
@outputXML nvarchar(max)
– Get all data from all the tables in the database
EXEC @return_value = [dbo].[sp_ExportDBAsXML]
@outputXML = @outputXML OUTPUT
– check if the temporary table exists, and if exists drop it
If Object_Id(‘t_backupData’) is Not Null
DROP TABLE t_backupData
– Create a temporary table and insert the data exported from the database as xml
CREATE TABLE t_backupData(oldData nvarchar(max))
INSERT t_backupData VALUES (@outputXML )
– Drop temporary table created by ExportDBAsXML (second check — not necessary but as safety check)
– Delete any old t_userTables temporary table
IF EXISTS( SELECT name FROM sysobjects where xtype = ‘U’ and name like ‘t_userTables’)
DROP TABLE t_userTables
——————————————————————————–
Restore.sql
———————————————————————————
USE [YOUR DATABASE]
DECLARE @return_value int,
@returnValue int,
@returnMessage nvarchar(1000)
DECLARE @oldData nvarchar(MAX)
– Read the data from the temporary table
SELECT @oldData = oldData FROM t_backupData
EXEC @return_value = [dbo].[sp_ImportDBFromXML]
@outputXML = @oldData,
@returnValue = @returnValue OUTPUT,
@returnMessage = @returnMessage OUTPUT
DROP TABLE t_backupData
——————————————————————————–
ExportDBAsXML
———————————————————————————
CREATEPROCEDURE [dbo].[sp_ExportDBAsXML]
(@outputXML nvarchar(max) OUT)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @userTableName nvarchar(50)
IF EXISTS( SELECT name FROM sysobjects where xtype = ‘U’ and name like ‘t_userTables’)
DROP TABLE t_userTables
SELECT name INTO t_userTables
FROM sysobjects where xtype = ‘U’ and name <> ‘t_userTables’ and upper(name) like ‘tableStartTag%’
— tableStartTag — replace this with your table name standards.. also helps to filter the tables you want to do the backup.
If Object_Id(‘tempdb..#xmltable’) is Not Null
DROP TABLE #xmltable
CREATE TABLE #xmltable (string XML)
DECLARE @tableCount int
SELECT @tableCount = count(*) FROM t_userTables
WHILE(@tableCount > 0)
BEGIN
SELECT TOP 1 @userTableName = name FROM t_userTables
INSERT #xmltable EXEC (‘SELECT * from ‘ + @userTableName + ‘ FOR XML AUTO, TYPE’)
DELETE t_userTables WHERE name = @userTableName
SELECT @tableCount = count(*) FROM t_userTables
END
SET @outputXML = ”
SELECT @outputXML = CONVERT(nvarchar(max),string)+ @outputXML FROM #xmltable WHERE string is not NULL
SET @outputXML = ‘<YOUR_XML_ROOT_TAG>’ + @outputXML + ‘</YOUR_XML_ROOT_TAG>’
DROP TABLE #xmltable
DROP TABLE t_userTables
END
——————————————————————————–
ImportDBFromXML
– there are few extra checkings for Data Version, you can use it or chenge it as necessary
– YOUR_Application table maintains the information about the applicaton information such as version number, date etc.
———————————————————————————
Create PROCEDURE [dbo].[sp_ImportDBFromXML]
(@outputXML nvarchar(max) ,
@returnValue int OUT,
@returnMessage nvarchar(1000) OUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @userTableName nvarchar(50)
DECLARE @sql nvarchar(max)
SET @returnValue = -1
SET @returnMessage = ‘Failed to Import Data’;
DECLARE @DATA_Version nvarchar(50)
DECLARE @CURRENT_Version nvarchar(50)
SELECT @CURRENT_Version = ISNULL(APP_Version,’0.0.0.0000′) FROM YOUR_Application
– Delete any old t_userTables temporary table
IF EXISTS( SELECT name FROM sysobjects where xtype = ‘U’ and name like ‘t_userTables’)
DROP TABLE t_userTables
– Read XML data to get version and table information
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @outputXML
– Get the tables from the XML Data
SELECT
DISTINCT localname INTO t_userTables
FROM
OPENXML(@idoc, ‘/YOUR_XML_ROOT_TAG/’,1) WHERE parentid = 0 AND upper(localname) like ”tableStartTag%’ AND upper(localname) <> ‘YOUR_APPLICATION’
IF NOT EXISTS(SELECT APP_Version FROM OPENXML(@idoc, ‘/YOUR_XML_ROOT_TAG/YOUR_Application’,1) WITH YOUR_Application)
SET @DATA_Version = ‘0.0.0.0000′
ELSE
SELECT @DATA_Version = ISNULL(APP_Version,’0.0.0.0000′) FROM OPENXML(@idoc, ‘/YOUR_XML_ROOT_TAG/YOUR_Application’,1) WITH YOUR_Application
EXEC sp_xml_removedocument @idoc
– to prevent forward compatiblity – who know what changes in the future
IF @DATA_Version > @CURRENT_Version
BEGIN
SET @returnValue = -1;
SET @returnMessage = ‘Imported Data Version : ‘ + @DATA_Version + ‘ not supported in this Version : ‘ + @CURRENT_Version;
RETURN @returnValue;
END
DECLARE @table_Exists int
SELECT @table_Exists = COUNT(*) FROM t_userTables
WHILE(@table_Exists > 0)
BEGIN
SELECT TOP 1 @userTableName = localname FROM t_userTables
IF @userTableName = ‘YOUR_Application’
GOTO CONITNUE_LABEL
IF @userTableName = ‘t_userTables’
GOTO CONITNUE_LABEL
– just a sample of special table, not necessary if you don;t have any
– special table check
IF @userTableName = ‘Your_specialtable’ AND @DATA_Version < ‘YY.YY.YY.YYYY’
BEGIN
– Special check if the table name is Your_specialtable and data version is less than YY.0
SET @sql = ‘DELETE FROM Your_specialtable’
EXEC (@sql)
SET @sql = ‘DECLARE @idoc int ; EXEC sp_xml_preparedocument @idoc OUTPUT, ”’ + @outputXML + ”’ ;’ +
’INSERT INTO Your_specialtable’ +
’ SELECT * ‘ +
’ FROM OPENXML(@idoc, ”/YOUR_XML_ROOT_TAG/’ + @userTableName + ”’,1) WITH Your_specialtable; ‘ +
’EXEC sp_xml_removedocument @idoc ; ‘
EXEC (@sql)
END
ELSE
BEGIN
SET @sql = ‘DELETE FROM ‘ + @userTableName
EXEC (@sql)
SET @sql = ‘DECLARE @idoc int ; EXEC sp_xml_preparedocument @idoc OUTPUT, ”’ + @outputXML + ”’ ;’ +
’INSERT INTO ‘ + @userTableName +
’ SELECT * ‘ +
’ FROM OPENXML(@idoc, ”/YOUR_XML_ROOT_TAG/’ + @userTableName + ”’,1) WITH ‘ +
@userTableName + ‘;’ +
’EXEC sp_xml_removedocument @idoc ; ‘
EXEC (@sql )
– Update Application ID if the data imported is older than XX.XX.XX.XXXX
IF @DATA_Version < ‘XX.XX.XX.XXXX’
BEGIN
– Again this is if only required, assuming each table has someway to uniquely identify its application ID
IF EXISTS(SELECT name FROM syscolumns where id = object_id(@userTableName) and name like ‘ApplicationId’)
BEGIN
set @sql = ‘update ‘ + @userTableName + ‘ set applicationid = dbo.getapplicationGUID(””) WHERE applicationid IS NULL’;
EXEC (@sql)
END
END
END
CONITNUE_LABEL:
– Remove the table from the temporary Table
DELETE t_userTables WHERE localname = @userTableName
SELECT @table_Exists = COUNT(*) FROM t_userTables
END
DROP TABLE t_userTables
SET @returnValue = 0
SET @returnMessage = ‘Sucessfully Imported Data’;
– execute your stored procedure to setup default values
– I maintained a seperate stored procedure to insert/update all the default values (applicatin meta data) and also re-wiring the administrator Access rights.
EXEC dbo.sp_SetupDefaultValues”
return @returnValue
END
———————————————————————————
