Backing up the SoftPro SQL database, isn''t that done automatically was the question of the week. Well yes and no is the answer, if you have a SQL database many times it is not included in your backup. Especially if you''ve switched to a Cloud based backup.
In my humble opinion The entire SoftPro Database should be backed up in this fashion on a regular basis even if your main backup catches it. There is too much valuable data in these files not to be specially protected.
So what do we do?
Well there are a few possible answers to that question, here we backup a SQL Express database, version is 2008. There are better tools if you have SQL standard edition. In my travels SoftPro runs fine on Express edition, although I like a few of the tools in Standard edition, so that''s what my shop runs.
Open Microsoft SQL Server Management Studio, opening the instance running SoftPro.
Click on New Query
Paste the following text in the New Query window. Or Better yet Download the Zip of SqlBackup for the 2 files necessary to put this all in place. The Web copy and paste is not being my friend right now.
--// Copyright © Microsoft Corporation. All Rights Reserved.
--// This code released under the terms of the --
--// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases]
@databaseName sysname = null,
@backupType CHAR(1),
@backupLocation nvarchar(200)
AS
SET NOCOUNT ON;
DECLARE @DBs TABLE
(
ID int IDENTITY PRIMARY KEY,
DBNAME nvarchar(500)
)
-- Pick out only databases which are online in case ALL databases are chosen to be backed up
-- If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
where state=0
AND name=@DatabaseName
OR @DatabaseName IS NULL
ORDER BY Name
-- Filter out databases which do not need to backed up
IF @backupType=''F''
BEGIN
DELETE @DBs where DBNAME IN (''tempdb'',''Northwind'',''pubs'',''AdventureWorks'')
END
ELSE IF @backupType=''D''
BEGIN
DELETE @DBs where DBNAME IN (''tempdb'',''Northwind'',''pubs'',''master'',''AdventureWorks'')
END
ELSE IF @backupType=''L''
BEGIN
DELETE @DBs where DBNAME IN (''tempdb'',''Northwind'',''pubs'',''master'',''AdventureWorks'')
END
ELSE
BEGIN
RETURN
END
-- Declare variables
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBNAME varchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop int
-- Loop through the databases one by one
SELECT @Loop = min(ID) FROM @DBs
WHILE @Loop IS NOT NULL
BEGIN
-- Database Names have to be in [dbname] format since some have - or _ in their name
SET @DBNAME = ''[''+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+'']''
-- Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),''/'','''') + ''_'' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),'':'','''')
-- Create backup filename in pathfilename.extension format for full,diff and log backups
IF @backupType = ''F''
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ''['',''''),'']'','''')+ ''_FULL_''+ @dateTime+ ''.BAK''
ELSE IF @backupType = ''D''
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ''['',''''),'']'','''')+ ''_DIFF_''+ @dateTime+ ''.BAK''
ELSE IF @backupType = ''L''
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ''['',''''),'']'','''')+ ''_LOG_''+ @dateTime+ ''.TRN''
-- Provide the backup a name for storing in the media
IF @backupType = ''F''
SET @BackupName = REPLACE(REPLACE(@DBNAME,''['',''''),'']'','''') +'' full backup for ''+ @dateTime
IF @backupType = ''D''
SET @BackupName = REPLACE(REPLACE(@DBNAME,''['',''''),'']'','''') +'' differential backup for ''+ @dateTime
IF @backupType = ''L''
SET @BackupName = REPLACE(REPLACE(@DBNAME,''['',''''),'']'','''') +'' log backup for ''+ @dateTime
-- Generate the dynamic SQL command to be executed
IF @backupType = ''F''
BEGIN
SET @sqlCommand = ''BACKUP DATABASE '' +@DBNAME+ '' TO DISK = ''''''+@BackupFile+ '''''' WITH INIT, NAME= '''''' +@BackupName+'''''', NOSKIP, NOFORMAT''
END
IF @backupType = ''D''
BEGIN
SET @sqlCommand = ''BACKUP DATABASE '' +@DBNAME+ '' TO DISK = ''''''+@BackupFile+ '''''' WITH DIFFERENTIAL, INIT, NAME= '''''' +@BackupName+'''''', NOSKIP, NOFORMAT''
END
IF @backupType = ''L''
BEGIN
SET @sqlCommand = ''BACKUP LOG '' +@DBNAME+ '' TO DISK = ''''''+@BackupFile+ '''''' WITH INIT, NAME= '''''' +@BackupName+'''''', NOSKIP, NOFORMAT''
END
-- Execute the generated SQL command
EXEC(@sqlCommand)
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
END
Then Click the Execute button 
(If you receive errors, search the text for '''' (double single quote) and replace with '' (single quote) there is something in WordPress that is causing the single quote to be doubled in the post.)
In short order "Command(s) completed successfully should display
If the command did not complete
successfully, be sure to Refresh object Explorer by clicking in it and pressing F5
In that case, if the Stored Procedure highlighted above (dbo.sp_BackupDatabases) exists, delete it (press the delete button) and try again. Use the error messages to locate the problem. We know of no issues with this script, Microsoft wrote it, so it should be good.
The script created a tool we can use to easily backup the database using a batch file or other scripting language file, we will make a simple batch file. The delete by date section of the batch file will only run on newer operating systems that include the forfiles command. (newer than vista or server wise windows2008)
Before we start let''s gather some information. We are going to need to know three things;
-
The SQL Server Name and Instance name if any
-
The Location we want to backup databases to
-
How many days of the backup do you want to save on the hard disk
So open Notepad, I use NotePad++ which is way better than Notepad. Copy the following batch file into Notepad.
@Echo Off
: Change the SQLServer to your SQL Server and Instance
Set SQLServer=Server09SoftPro
: Change BackupTo= to the location you wish to save your backups. Don''t forget the trailing backslash.
Set BackupTo=F:_backupsSLTSSoftProDataBaseBackup
: How many days of backups do you want to keep on the live system. We’re not dealing with weekends here, straight days.
Set DaysToSave=10
: Don''t edit below here if you don''t get what’s going on.
Echo on
forfiles /p %BackupTo% -M *.bak /D -%DaysToSave% /C "cmd /c del @path"
@Echo Off
: Disable the above line and enable the below line on old OS to keep one day
: Del %BackupTo%*.bak
sqlcmd -S %SQLServer% -E -Q "EXEC sp_BackupDatabases @backupLocation=''%BackupTo%'', @backupType=''F''"
: Cleanup
Set SQLServer=
Set BackupTo=
Set DaysToSave=
:End of SoftProSQLBackup.Bat
Change the lines below the 2 ":Change" comments to be your SQL server, where you want the backup to go, and how many days to keep.
Save the file as "SoftProSQLBackup.bat", 
normally you should not include the double quotes in this case if you are using Notepad, be sure to use them, or Notepad will try to help you and save the file incorrectly as a text file.
Where to save it is your choice, I saved it to the save folder the backups go to.
Now it''s time to test it. Double click on the newly created "SoftProSQLBackup.bat" file using Windows Explorer.
A Command line window will pop up and probably not stay around very long
Ignore the first line, there are no *.bak files to delete just yet.
Windows Explorer will update showing the newly created files
The next time you run it, the file names will change as will the Date Modified. Make sure your Cloud backup, or other backup will include this folder and you are good to go.
Well maybe not quite good to go. Next we need to Schedule this batch file to run via the Task Scheduler. I''m not going to go into much depth doing this, as the Task Scheduler varies depending on the Operating System you are using. I write this using Windows Server 2008 R2.
Start Task Scheduler the easy way Type it in the search box
You can also find it in control Panel, either directly or under Administrative Tools, depending on your OS.
Click on Create Basic Task on the Actions Panel.
Give it a name and a Description if you wish. The Name is necessary.
Click Next
Daily is the minimum recommended run time.
Click Next
Give it a time and date to run, preferably before the main backup starts. Let it run every day, and don''t worry about time zones. If you need to worry about them, you''re probably not reading this.
Click Next
We want to Start a Program, Click Next
Click Browse and navigate to the batch file we created
Click Next
Click Finish
Your automated backup of the ProForm SQL database is complete. This method backs up all databases if you want to limit the databases backed up by this script visit this Microsoft Knowledge base article.
The method I used to select files by date, opens a new command shell for every file, real fast. If your SQL server contains thousands of databases, you’ll want to use a different for loop. opening 10 or even 40 command prompts real quick isn’t a big deal. Five hundred of them, probably not a good idea.
