This guide is for those who have a dedicated server and need to backup your DotNetNuke websites on a regular basis to prevent a disaster.
There is plenty of software out there that will take care of backing up your website but the problem is it is either A. Too expensive or B. Doesn’t backup everything you need.
Here is how I backup my websites and you can take some of these steps and bend them into what works best for you. These methods utilize simple command line and batch scripts to accomplish everything you’ll need. You will also need to install 7-Zip to handle the compression
Step 1: Backing up your databases
This script was primarily written for SQL Express in order to give you a scheduled backup that Full SQL has but is just as viable to use with the full version of SQL. The result of this script will leave you with a directory of .BAK files of every database in your installation with a date stamp.
DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50) DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200) SET @BackupDirectory = E:\SQL Data\Backup\' --Add a list of all databases you don't want to backup to this. DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'Northwind' OPEN Database_Cursor FETCH next FROM Database_CURSOR INTO @DB WHILE @@fetch_status = 0 BEGIN SET @Name = @DB + '( Daily BACKUP )' SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) SET @BackupFile = @BackupDirectory + + @DB + '_' + 'Full' + '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak' SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master' BEGIN SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak' SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' END ELSE BEGIN SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak' SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' END BACKUP DATABASE @DB TO DISK = @BackupFile WITH NAME = @Name, DESCRIPTION = @Description , MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description , STATS = 10 FETCH next FROM Database_CURSOR INTO @DB END CLOSE Database_Cursor DEALLOCATE Database_Cursor
Now that you have the ability to backup all your databases. We need to schedule this to run on a regular basis and clean up old versions so we don’t end up filling up our entire hard drive. For this step I’ve created a batch file that will
- Runs SQLCMD which comes with MS SQL connecting to (period = localhost. Your installation may be at .\SQLEXPRESS instead) your SQL installation and run the above SQL script
- Utilizes 7-Zip to go through every .BAK file in this directory and compress it to save space
- Deletes the no longer needed .BAK files
- Removes any file in this directory that is longer than 14 days.
sqlcmd -S . -i "E:\SQL Data\Scripts\Backup_All_Databases.sql" forfiles /p "E:\SQL Data\Backup" /s /m *.bak /c "CMD /c C:\Progra~1\7-zip\7z.exe a -tzip -mx=10 @PATH.zip @PATH" forfiles /p "E:\SQL Data\Backup" /s /m *.bak /c "CMD /C del /Q @FILE" FORFILES /p "E:\SQL Data\Backup" /s /m *.* /d -14 /c "CMD /C del /Q @FILE"
All that’s left now is to create a Scheduled Task to run the above batch file every night and you have SQL backups!
Step 2: Backing up your website
An important step that is OFTEN overlooked is backing up the website files associated with your website AS WELL as the databases. Backing up your websites can be just as easy as the databases. All you need is another batch file:
- Gets a date stamp in the format of 20110514 to use to append to file names
- Gets a list of all the directory names where your websites are stored. (Recommend that all your websites be stored in a directory by their domain name. ex: C:\webs\twentytech.net”). This will then send that directory to 7-Zip for compression and output the results of that zip file to your C:\Backup directory
- Removes any file in any of the sub directories that is older than 14 days.
for /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c%%a%%b) for /f %%a IN ('dir C:\webs\ /b /ad') do C:\Progra~1\7-zip\7z.exe a -tzip -mx=10 "C:\Backups\%%a\%%a_%mydate%.zip" "C:\webs\%%a" forfiles /p "C:\Backups" /s /m *.* /d -14 /c "CMD /C del /Q @FILE"