Weekly Dynamic: SQL Snapshots and Dynamics GP

Sometimes when implementing Dynamics GP you do a lot of backing up and restoring. Large implementations can require multiple test environments, development environments, etc. Sometimes you need to push a large number of transactions through for testing over and over again, restoring the environment each time.

Well all of those backups and restores take time. A faster alternative is to use functionality in SQL Server to take a snapshot of a company. Snapshots have lots of limitations that make them unsuitable for typical backup/restore needs but for activities that require multiple reloads they are a lifesaver. Why? Snapshot restores are fast. Smoking fast. 4 second restores fast.

In a test on an old, single processor machine, the default TWO company and the Dynamics DB took 45 seconds to backup and another 45 seconds to restore. A snapshot took 6 seconds to backup and 4 seconds to restore. What's more, the snapshots don't slow down much as the db size increases. We've seen GP databases that take an hour for a full backup and restore still create a snapshot in under 10 seconds and restore in under 10 seconds.

Unfortunately Snapshots only work on the Enterprise Edition of SQL Server but if you want to know more about both the power and the limitations of snapshots, here is a great overview.

Here is the SQL Code I used to create and restore my test snapshots:

CREATE DATABASE DYNAMICS_dbss ON
( NAME = 'GPSDYNAMICSDat.mdf', FILENAME =
'c:\DYNAMICS_dbss.ss' )
AS SNAPSHOT OF DYNAMICS;
GO

CREATE DATABASE TWO_dbss ON
( NAME = 'GPSTWODat.mdf', FILENAME =
'C:\TWO_dbss.ss' )
AS SNAPSHOT OF TWO;
GO

-------------------------------------------------

RESTORE DATABASE DYNAMICS FROM DATABASE_SNAPSHOT = 'DYNAMICS_dbss'
go
RESTORE DATABASE TWO FROM DATABASE_SNAPSHOT = 'TWO_dbss'
go


[H.T. to Ross Carlson for this]