BackupScript DotNetNuke Module

Conception

Our first publicly released module GrupoSur.BackupLite (now Evotiva.BackupNative) is great performing native SQLServer database backups (among other useful features).

Now we wanted a tool that allows us to create/update/replicate all site contents (database and files) with minimal intervention of the ISP.

This module is that tool. If you have a backup of your site built with it, you just need an already created database (empty or not) and the empty (or not) web site (with the necessary write permissions set for the asp.net user) to fully restore/replicate your site.

Features

Installation

Download the PA from Evotiva's download page.

Install the module as any regular standard DNN module.

You should manually add it to any page (like any regular DNN module).

Prior to DNN 4.4 was possible to add it to an existent built-in Host pages, like "SQL" or "File Manager", etc., but those pages have been turned read-only by the Core team.

Upgrading the module

You must review and save the module's setting after performing an upgrade.

Security Tips

• Add the module to "host/admin" pages only. NEVER put it on any kind of "public" page.

• Rename the default backup folder name.

• Rename the default backup file names.

    Q: since the module is listed on the Module list (at Control Panel on the top), so everybody that have Edit access can add this module right ? How to protect that Admin or Host is the only role that can use Backup module ?

    A: Once configured you can browse to Host / SQL and execute:
         update {databaseOwner}{objectQualifier}DesktopModules set isadmin=1
     where friendlyname='Evotiva.BackupScript'
        After that, the module will 'disappear' from the Control Panel on the Top. The side effect is that you will also lose its "Settings" menu item.

How does it works

What can be scripted

Database Objects

The module scripts the following database objects and their relations/bindings:

• UserDefinedDataTypes, Defaults, Rules, UserDefinedFunctions, Tables, Views, StoredProcedures

Table’s Contents

The module fully supports the following data types:

• "tinyint", "smallint", "int", "bigint", "decimal", "real", "float", "numeric", "money", "smallmoney", "nchar", "nvarchar", "char", "varchar", "uniqueidentifier", "datetime", "smalldatetime", "varbinary", "binary", "bit"

The module also supports all the BLOB data types:

• "ntext", "text", "image"

Any database size is fully supported. The module can deal with huge database containing huge Blob data.

SqlDataProvider Files

The module can save all the .sql and .SqlDataProvider files under

(DNN Core files)

\Providers\DataProviders\SqlDataProvider

The configured (web.config) DNN installation template (DotNetNuke.install.config by default) is also saved.

(3rd. Party modules)

\DesktopModules\<modulename>\Providers\DataProviders\SqlDataProvider
\DesktopModules\<modulename1>\<modulename2>\Providers\DataProviders\SqlDataProvider

Note: other files under other "not standard" (these) folders will not be saved.

What cannot be scripted

There are some things that cannot be scripted/backup/restore, like "timestamp", "user-defined", "sql_variant" and  "table" data types columns. But you probably will not have that kind of data.

The module ignores BLOB data for tables without a defined Primary Key as they would be impossible to restore. But you probably will not have a table without a PK and with BLOBs.

You can use the integrated "Table's PKs validator" to find tables in this situation.

When the module detects this problem, the generated table scripts will include this kind of comment "-- Ignoring BLOB data for table XXXX (no PK defined.)"

Please note that as the module reads and scripts your objects and data they may be changing. So, there is a possibility of inconsistent data being scripted.

You can guess the column that should be the primary key looking the result for (run from Host > SQL):

-- replace xxxxxxxxxxxxx with the table name

SELECT col.name, col.colid, col.isnullable, typ.name
  FROM syscolumns col
  JOIN systypes typ on col.xtype=typ.xtype
 WHERE id = OBJECT_ID('{objectQualifier}xxxxxxxxxxxxx')

Sample sql code to create some missing primary keys (run from Host > SQL):
ALTER TABLE {databaseOwner}{objectQualifier}grmRepositoryComments

   ADD CONSTRAINT [PK_{objectQualifier}grmRepositoryComments] PRIMARY KEY CLUSTERED
   (
      [ItemId]
   ) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}grmRepositoryObjects

   ADD CONSTRAINT [PK_{objectQualifier}grmRepositoryObjects] PRIMARY KEY CLUSTERED
   (
      [ItemId]
   ) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}Forum_PM
ADD CONSTRAINT [PK_{objectQualifier}Forum_PM] PRIMARY KEY CLUSTERED
(
[PMID]
) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}Forum_PM_Posts
   ADD CONSTRAINT [PK_{objectQualifier}Forum_PM_Posts] PRIMARY KEY CLUSTERED
   (
      [PMID]
   ) ON [PRIMARY]
GO
 

BackupNative module can backup your entire database "at once" (native SQL Server backup).

Other limitations

When using legacy Zip format: 65535 entries / 2Gb max zip file size.

When using ZIP64 format: lot of entries, 4gb max zip file size.

An upcoming version will support the new SQL Server 2005 data types: nvarchar(max), varchar(max), and varbinary(max)

Requirements

The module was tested on older DNN versions like DNN 3.0.13, 3.1.x, 4.0.2, 4.0.3. and also work fine on any DNN version up to the most current ones.

Note: BackupScript 3.x (and above) does not run in DNN3.

It supports SQL Server 2000 and SQL Server 2005.

Like DNN itself, the module requires write permissions over the file system.

When configured to script the database objects, current version relays on SQL-DMO (sqldmo.dll)  library or SMO assemblies (DNN4 only) to get some information from the database.

DMO library should be installed on the web server for this setting to work.

No SQLServer installed on the IIS machine is needed by the module. Only the sqldmo.dll is needed (when this feature is enabled). This dll is automatically installed with the sql server client (and of course also when installing the sql server, but the fact is that just the sql server client installed is needed.). The dll  can also be installed without installing the sql client. See http://support.microsoft.com/kb/q248241/ and http://support.microsoft.com/?kbid=326613 for more information.

 

Disabled feature (release 2.0.2): On DNN4 you can select SMO as database objects scripting library instead of DMO. SMO library should be installed on the web server for this setting to work.

SMO does not allow partially trusted callers, thus using SMO is 100% managed code but still needs the site running on full trust level.

 

NOTE: You can vote to get SMO fixed (Microsoft): http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262072

So, ONLY when configured to script the database objects, this module requires DNN (your web site) to be configured on "Full trust" mode (trust level="Full") because needs the DMO or SMO library.

This is generally set in the machine.config and is normally set this way, but this may not be true for some servers.

When scripting database objects using the DMO library, current version, doesn’t supports a "connection string" (configured at the web.config file) written in a SQL Server 2005 Express way (e.g. "Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;" providerName="System.Data.SqlClient").
It supports Connection String for SQL Server 2000/2005.
 

NOTE: By default DMO is not available on SQL Server 2005 Express, and trying to access a SQL Server 2005 db with the older (SQL Server 2000) versions of the SQL DMO is not possible. You will need at least the "Microsoft SQL Server 2005 Backward Compatibility Components"
If you successfully installed DMO on your server, you will need to change the connection string to something like (do not forget to include the trailing ";" character):
e.g.:
"Server=myserver;Database=mydatabase;uid=user;pwd=password;"
"Server=SERVER\SQLEXPRESS;Database=dotnetnuke;uid=johndoe;pwd=thepass;"
"Server=XXXXXX;Database=YYYYYY;Integrated Security=True;"
"Data Source=.\SQLEXPRESS;Initial Catalog=mydatabase;uid=sa;pwd=mypass;"
"Data Source=.\SQLExpress;Initial Catalog=DotNetNuke;Integrated Security=True;"

You can simply disable the "script database objects" setting and the module will work fine. See this section.

Since 1.2 version, the DMO/SMO library is no longer needed by the module to script the table's data or to perform restore operations.

Table's content scripting should work without any problem on any ISP (*) and running your site in medium trust level.
 

On demo (unregistered) mode, the module scripts three tables (EventLog, Modules and TabModules) to enable you to test this functionality at your real site BEFORE purchasing.

The restore utility is also 100% "DMO/SMO free". You should be able to restore your database objects and/or data on any ISP (*) and run your site in medium trust level

(*) YES, even at GoDaddy Shared server plan and other very restrictive ISPs!!!. See GoDaddy Configuration

We’ve tested the module at GoDaddy “Deluxe Plan”, on DNN 3.3.5 and 4.3.5  MANUALLY INSTALLED (successfully backup the site: table’s data + portal files)
Also we successfully restored the site (the module’s backup files) locally on a dev. machine, following the on-line module’s manual instructions.

TIP: test the module BEFORE purchasing (it's FREE to try), and let us know any issue you may find.

FAQ

Q: I’m really interested in this awesome module. How can I know if it will work on my ISP?

A: Thanks! Just download it, install it and use it. If it’s powerful FREE features works, then all the other features will work too. If it fails, please contact us to research the problem and find if it is really an ISP incompatibility issue or something misconfigured.

Q: I get this error when trying to script my database. What does it means?

     ---> System.Exception:  Error connecting to SQLServer (DMO) after retrying 15 times.
     ---> System.NullReferenceException: Object reference not set to an instance of an object.

A: This error looks like you don't have access to the needed SQLDMO library. May be the dll is not installed or may be it's blocked to your web site. Please check with your ISP if it is possible to access the sqlsmo.dll from your web site.

In any case you can disable the objects structure scripting and backup your data (table's contents) only because doesn't need any special library

Read more about sqldmo.dll on the Requirements section.

Q: I get this error when trying to script my table's contents. What does it means?

     ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'REFERENTIAL_CONSTRAINTS', database 'master', owner 'INFORMATION_SCHEMA'.
A: This is a known error at GoDaddy Shared server plan. The access to your database information (which tables do you have with which structure, etc.) is blocked at SQL Server level.  In this case you should check the "use custom schema views" setting.

Read more on the Requirements section and GoDaddy Configuration

Q: I get this error when trying to backup  What does it means?

     Database script generation failed.

     ---> System.UnauthorizedAccessException: Access to the path "C:\www\inetpub\mysite.com\backup\WORK\" is denied.

A: . It means that the ASP.NET user (the windows user under with DNN runs, that is “NETWORK SERVICE” on IIS 6, when not "impersonated") does not have the required read/write access on the module’s configured folder branch (In this case, \backup and beyond). Check the Requirements section.
You have to set read/write access (file system permissions) to the ASP.Net user (“network service” on IIS6) for the configure backup folder branch.
If your site is hosted, it depends on the ISP (some of them provides an administrative web console. In other cases you may have to ask them to do it for your)
If you have local access to the server this is done from the: right-click / “sharing and security..” pop-up menu option
 

Q: How can I know more details about a database backup or restore operation?

A: For each database backup and restore operation a ".log" file (same name of the backup file) is written. There you can read the list of the performed tasks.
     The same information is shown "on screen" after a database backup or restore operation.

Q: Can I get a limited time FULL version of your module so I can fully test it on my real environment?

A: Sure, please contact us, but note that the current "free" functionality of the module is enough to fully test it.

Q: I’m interested in your module to install it on my 8 domains. Should I purchase 8 licenses?

A: Of course not. You can purchase an enterprise license that gives you the right to install it in as many of YOUR domains as you want.
     Please note that this license is not intended for hosters or resellers. You are not allowed to redistribute (free or not) your license.

Q: Should I really need one license for each domain on my DNN installation?

A: No, because current version backups your full database, not individual portals. So, the backup for one domain will include the other portal/domains too.
     Currently, you will only need one license for each physical database you use.
     More than 3 or 4 portals, justifies purchasing an Enterprise license (many portals/databases) which includes also the full source code (VS 2003 and VS 2500 solutions).

Q: Ok, I bought the module. How can I get my license?

A: With your purchase you get instant access to a time limited "enterprise" license. That is, you get instant access to all the module's features on any domain. Meanwhile, send us a mail with your full domain and main purchase information (invoice#, your name, email, etc.) and we’ll send you your definitive license string. Just paste it on the module’s Settings page. There ("Licensing Information" section) you’ll see your full domain name and a link that helps you to prepare the email. For more information see Configuration / Licensing information section.

Q: How can I know my full domain name?

A: You can get it form the module’s Settings page, "Licensing information" section.

Q: The module failed to backup my database. What’s next?

A: Please contact us and we’ll research the problem. It could be simply something misconfigured, something regarding to your web server, your ISP or something specific of your database. Every database is a word and weird things can be made inside them. We do our best considering as many cases/scenarios as we can imagine. If the problem cannot be fixed or workaround, you can get the refund.

Q: I would like to use this module to backup an entire portal, and then restore it to a new location.
e.g. I have a portal called "myfirstportal" and it uses the database "first", I want to back this up and then restore it to "mysecondportal" and the database "second". Is this possible? 

A: Sure, if there are no other portals inside the target database.

Q: Can I backup a particular portal (instead of all of them)?

A: If you mean "portal" as whole DNN; sure, you can do that (same as previous question). You can see restore samples (to a different site/database) on the module's documentation. If you mean "portal" as an individual "child" portal, the module currently cannot backup and restore individual portals within the database.

Q: Much of my DNN site uses XMod modules - will it back up the content and config of those too?

A: Yes, this module will "script" all the XMod tables too. Upcoming version will let you choose also which tables include/exclude. Currently all tables (all data) on the database are scripted.

Q: How should I configure the module to run at GoDaddy Shared Hosting Plan?

A: See GoDaddy Configuration

Q: Do you know where can I found instructions about how to install DNN at GoDaddy Shared Hosting Plan?

A: See GoDaddy Configuration

Q: Can I make this module work with  SQL Express 2005?

A: Yes. Please check the Requirements section.

Q: Can I use your product to move a site from one ISP to another?
A: YES. You just need an already empty (or not) database created and all your database objects and table's data can be recreated. In fact we've moved sites from one ISP to another using BackupScript (with ISP minimal intervention, just to create and empty db and to give rights to asp.net over the file system) and also using BackupNative (when we have access to the native database backup files and we can ask the ISP for restoring it). 

Configuration (Settings)

The module can import/export its setting (implements the standard DNN IPortable interface, from its actions menu) .
Note: You must edit and save the module settings the first time before using this feature.

Licensing information

Database Backup Options

Portal Contents Backup Options

General Options

FTP Options

Scheduler Options

GoDaddy Configuration

On our tests, DNN was manually installed (we couldn’t get it installed on the root, but was successfully installed on a \DNN folder).

We didn't used any provided "auto install" feature.

Some installation guides:

• (best) http://www.dotnetnuke.com/Community/ForumsDotNetNuke/tabid/795/forumid/107/threadid/84515/scope/posts/Default.aspx 

• http://www.northernstarsolutions.com//tabid/61/Default.aspx

• http://forums.asp.net/958251/ShowPost.aspx.

To install DNN in the root, you can follow these instructions: http://www.mikroproje.com/DotNetNukeArticles/tabid/283/articleType/ArticleView/articleId/89/language/en-GB/Default.aspx

Backup Settingss

Backup Database: Checked
Script Database Objects: Unchecked

Script (backup) table's date: Checked
Script Method: DMO
Use custom schema views: Checked

Restore Settings

Use custom schema views:

You may need to create the \backup (configured backup folder) on the GoDaddy's Control Panel; with read, web & write permissions.

Tor its child folders: \WORK, \WORK\Blobs and \WORK\Providers; is advised set only read & write permissions.

You can edit in advance (before restoring) the Ins_PortalAlias.sql script file or after restoring you can issue a command like this
to set the new alias (httpalias column) value:
e.g.  UPDATE {databaseOwner}{objectQualifier}portalalias
       SET httpalias = 'www.mymirrorsite.com'
     WHERE portalaliasid = 1

When restoring to a different domain, you will have to update the PortalAlias table to reflect the change.
After restoring you can perform this operations to set the new alias (httpalias column).

  -- first find the current alias(es) 
  SELECT * FROM {databaseOwner}{objectQualifier}portalalias; 

  -- then, update as needed:

  -- replace www.mynewdomain.com
  -- replace 1 with the right value
  UPDATE {databaseOwner}{objectQualifier}portalalias
       SET httpalias = 'www.mynewdomain.com'

     WHERE portalaliasid =  1;

You can use the "SQL Query Tool" integrated into the Restore Utility to check & update the PortalAlias table.

Prerequisitess

Like DNN itself, this module assumes the database already created and least with one user created for accessing it (in standard or trusted mode).

Please note that the restore process may take some time to complete, depending on your data volume.

Case I – Already running DNN site

This is a "Backup History" restore (check General Information section).

1. From the module’s "Backup History Manager", select the backup zip files to be restored.
   You can select one or two files. If your select two, they should be of different "kind" ("database backup" or "portal files" backup).
    

2. The Restore Utility will be launched.
   Check your options (will be enabled only the valid ones) and click the "Perform Restore" button.
   The restore options are pretty understandable. May be except "Database Owner" and "Object Qualifier" which are used only when creating the temporary database objects used by the restore utility. You should normally leave the default values.
   When needed (e.g. at GoDaddy shared hosted plan), check also "Use custom schema views".
   
    

3. The restore starts and will periodically update its status.
   Please note that the restore process may take some time, depending on your data volume.
    
   When completed, you will see on-screen the full database restore log which is also written to disk (e.g.  "mydatabase.sql.zip_RESTORE.log") on the same folder where the backup zip file is.
    

4. Restart the application using the provided link.

Notes

- The backup Zip files should be located on the configured backup folder (module's settings page).

- You don't have to manually create any HISRestoreInfo.resources file.
  The database connection string to be used during the restore operation is read from the 'live' DNN site's web.config file.

- You cannot restore the database objects running on this mode (you must call the restore utility on standalone mode).

- After a database restore you MUST restart the application using the link on this page.
  Form inside DNN, you can clear the cache and/or restart the application (browse to Host / Setting to perform this tasks).

- If you are restoring the portal files, remember that due to a asp.net limitation the module currently can restore ONLY the \Portals branch.
  If you want to restore all the portal files, uncompress the backup and use a FTP tool to upload the needed/wanted files or use the restore option (checkbox) to Uncompress ALL portal files to .\WORK\Site (later you can e.g. by FTP ir ISP's Contol Panel, move what you want/need to its real location).

Case II – Empty / new DNN sitee

This is a "Standalone" restore (check General Information section).

TIP: Please review the companion "Quick Guide" document for a simplified backup/restore procedure.

Prerequisites – Database (database & database user))

Remember that like DNN itself, this module assumes the database already created and least with one user created for accessing it (in standard or trusted mode).

Sample manual creation of the database with one user:

USE master

CREATE DATABASE mydatabase

go

EXEC sp_addlogin 'myuser', 'mypassword'

go

EXEC sp_defaultdb 'myuser', 'mydatabase'

go

USE mydatabase

EXEC sp_grantdbaccess 'myuser', 'myuser'

go

EXEC sp_addrolemember 'db_owner', 'myuser'

go

Prerequisites – Web site

Like DNN itself, this module needs write access over the web site files and folders because it has to create/delete temporary files and folders.

We will suppose an empty web site (but already created). 

Restoring the database

Setup steps

You can put your backup zip files in any folder under the web root. The only requirement is that "BackupScriptRestore.aspx" should be on the same folder.

After restoring your database, you may need to change some information on the "PortalAlias" table to match your new web site name and/or virtual directory.

You can check the DNN documentation for more information about the "PortalAlias" table.

PortalAlias table section).

Let's say you want your backup zip files are under /backup.

Web site structure: 

 (WebRoot)

+--- backup

|

+--- bin

1. Browse to <mysite>/backup/default.aspx
   e.g. http://www.mysite.com/backup/default.aspx
    

2. The Restore Utility will be launched.
   Check your options (will be enabled only the valid ones) and click the "Perform Restore" button.
   The restore options are pretty understandable. May be except "Database Owner" and "Object Qualifier" which are used only when creating the temporary database objects used by the restore utility and needed by the integrated SQL Query Tool. You should set those values same as in your original web.config file.
   
    

3. The restore starts and will periodically update its status.
   Please note that the restore process may take some time, depending on your data volume.
    
   When completed, you will see on-screen the full database restore log which is also written to disk (e.g.  "mydatabase.sql.zip_RESTORE.log") on the same folder where the backup zip file is.
    

4. Use the integrated "SQL Query Tool" to check & update the PortalAlias table.  
    

5. Restart the application using the provided link.

Notes

- The backup Zip file(s) to be restored should be located on the same physical folder as this ASPX file.

- OPTIONALLY you can manually create a HISRestoreInfo.resources file (on this same physical folder).
  The database connection string to be used during the restore operation is read form this HISRestoreInfo.resources (NOT from the original RestoreInfo.resources within the backup ZIP file).
  If the HISRestoreInfo.resources file is not found, you MUST write (on screen) the appropriate database connection string.

- If you want to restore the database objects please disable (e.g. rename) the Global.asax file and replace the standard DNN web.config file with a minimal one. Also may be necessary disable (e.g. move somewhere else) all the DLLs not used by the restore utility.

- If you are restoring both database objects and table's data is recommended to perform the restore in two stages.
  On the first one just check the 'Create objects' option and after its successful restoration run the tool again to restore the table's data only ('Restore data' checkbox).

- If you don't have the database objects creation scripts remember that all the required tables must be created before the restore, otherwise it will fail (see Case III).

- If you are restoring a database backup performed on a different machine and/or ISP and/or domain:
  + remember that you may need to change the connection string BEFORE performing the restore.
  + remember to change the PortalAlias table to reflect the new portal's domain (more information on PortalAlias table section).

  + You can use the "SQL Query Tool" integrated into the Restore Utility to check & update the PortalAlias table.


- If you are restoring the portal files, remember that due to a asp.net limitation the module currently can restore ONLY the \Portals branch.
  If you want to restore all the portal files, uncompress the backup and use a FTP tool to upload the needed/wanted files.

- What if I don't have the database objects creations scripts?

You have to follow Case III.

Restoring the portal files

Just unzip the "myfiles.zip" file under the web root. The \Portals branch can be uncompressed by the Restore Utility.

Case III – Empty / new DNN site without the database objects creation scripts

NOTE: since BackupScript 3.x, this case is obsolete beacuse you can always restore on Case II if you included the dataprovider files in the backup ("Script DataProviders" checkbox enabled on the module's settings page). This setting will work no matter if DMO or SMO is available on the source server.

Note that only will be created the structure for tables defined in the dataproviders. Any "external" table cannot be recreated (unless you enable the "script database objects" setting).

TIP: Please review the companion "Quick Guide" document for a simplified backup/restore procedure.