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.
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
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.
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.
Daily is the minimum recommended run time.
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.
We want to Start a Program, Click Next
Click Browse and navigate to the batch file we created
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.