-->

Tuesday, October 21, 2008

Automated GP Database Backups with SQL Express

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

clip_image002

clip_image004

clip_image006

clip_image008

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.

2 comments:

Anonymous said...

Can you post the dotnet application? It will be useful.

Abir said...

As soon as looking for a great MBA essay croping and editing support, make certain you take a look at a number of items. First of all, it could price tag a reasonably any amount of money, so it is critical to search when you choose whatever Review of Paperhelp.org for your learners this producing program incorporate some extra make use of some others providers.