Home on the web ~ Dan Van Fleet

Information on SoftPro ProForm with some general computer tips and techniques, with a bit of me.

Home on the web ~ Dan Van Fleet - Information on SoftPro ProForm with some general computer tips and techniques, with a bit of me.

Automated SoftPro SQL Database Backup

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 clip_image002 (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

clip_image004

clip_image006

If the command did not complete
clip_image008

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;

  1. The SQL Server Name and Instance name if any
  2. The Location we want to backup databases to
  3. 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", imagenormally 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.

clip_image010

 

clip_image012

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.

 

clip_image014

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.

 

clip_image016

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.

clip_image018

Click on Create Basic Task on the Actions Panel.

clip_image020

Give it a name and a Description if you wish.  The Name is necessary.

Click Next

clip_image022

Daily is the minimum recommended run time.

Click Next

clip_image024

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

clip_image026

We want to Start a Program, Click Next

 

clip_image028

Click Browse and navigate to the batch file we created

Click Next

clip_image030

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.

Help support the site when shopping on Amazon, click through any ad to get to Amazon, ignore that product, then buy whatever you're there for. Thanks for helping out.
Tag: , ,

Your email address will not be published. Required fields are marked *

*

css.php