With GP’s support for SQL Express, a lot of people are going with this free option instead of the full fledged SQL Server. A few things need to be tweaked here and there – but generally it works well. I’ve covered a security gotcha before, and today I’ll cover one of the most important things that people forget when using SQL Express – Database Backups.
SQL Express does not come with the SQL Server Agent which is mostly used to create DB Backups for GP Databases. You have to automate it yourself. Below are the steps I’ve used at a couple of places to automate GP Backups when using SQL Express.
The high-level architecture of the simple backup system involves using a Windows Scheduled Task, with Stored Procedures for backing up the databases. You can also build an advanced system – more on that below.
1. Create a batch file that calls the SP to backup the databases, like this –
sqlcmd -s"sqlexpress instance name" -E -Q"exec spBackupdatabase"
2. Create a SP, that backups up the dynamics and company databases, like this –
CREATE PROC[dbo].[spBackupDatabase] As
DECLARE @filename nvarchar(2500)
SET @fileName = 'C:\GreatPlainsBackups\'+ Cast(DATEPART(m, GetDate())as nvarchar)
+ '-' + Cast(DATEPART(d, GetDate()) as nvarchar)
+ '-'+ Cast(DATEPART(yy, GetDate()) as nvarchar)+'-Dynamics.Bak'
BACKUP DATABASE [Dynamics] TO DISK = @fileName
GO
Set @fileName = 'C:\GreatPlainsBackups\'+ Cast(DATEPART(m, GetDate())as nvarchar)
+ '-' + Cast(DATEPART(d, GetDate()) as nvarchar)
+ '-'+ Cast(DATEPART(yy, GetDate()) as nvarchar)+'-FXDD.Bak'
BACKUP DATABASE [COMPANY_NAME] TO DISK = @fileName
GO
3. Create a scheduled Task to launch the batch file at a schedule that suits you
You could do advanced things like below, with some more programming -
a. Backing up all databases on SQL express
b. Removing backups older than 7 days
c. Sending email to configured users
We’ve built a custom application that does that in Dot.Net 2.0, if any of you need that, let me know – and I’ll put it out here for your reference.