How can I take nightly snapshots of SQL server database schema without having to use version control?

A developer aware of the importance of managing database changes knows that database versioning is a must. Having a database versioned in source control enables you to recreate the database with the same structure it had at a certain point in the past.

The most common solution for database versioning is using a source control system. But it’s not the only possible solution. Database structure versions can be created even without having a source control system.

Having a versioned database is not the end of the story. If all the differences between the database version 20130110 and the live database are needed to be seen, the version control system itself cannot provide this information.

What scenarios can be used for database versioning without source control?

  1. Create database backups regularly. Restore the backup and extract the DDL scripts. This is actually not a solution, but the last resort you can use
  2. Create a table where all schema changes are logged – changes to tables, indexes, keys, views, stored procedures, functions and all other objects that require DDL. This can be done using triggers
  3. Generate a database DDL script every night. As you will have all DDL scripts at hand, all you have to do is find the right one and find the optimal way to deploy it. Do you have to drop and recreate the objects you are changing? Can you simply ALTER them?

  4. If the goal is to manage database schema changes in a simple way, the solutions above are not the answer

  5. Creating database snapshots will take less disk space than database scripts, and can be scheduled and thus run automatically.. A database snapshot is a binary file that contains only the database structure. When you need an older version of your database or a specific object, just open the snapshot, compare it to the live database and synchronize

ApexSQL Diff is a tool that creates database snapshots, compares and synchronizes SQL Server database tables, views all other SQL Server objects from these snapshots, live databases, database backups and source control systems. It can also deploy a specific SQL database version directly to or from source control. Unlike snapshots created by SQL Server, ApexSQL Diff database snapshots are not limited by the SQL Server edition, nor have to remain in the same SQL Server instance as the source database

To schedule a database snapshot creation using ApexSQL Diff:

  1. Create a batch file and save it as Snapshot.bat

    C:\Program Files\ApexSQL\ApexSQLDiff2015\” /s1:NOJRO\SQL2014 /d1:AdventureWorks2014 /sn2:E:\Snapshots\NOJRO@SQL2014_AdventureWorks2014_2015-06-16.axsnp /ex

    This example creates a snapshot of the AdventureWorks2012 database on the NOJRO\SQL2014 instance and stores it as E:\Snapshots\NOJRO@SQL2014_AdventureWorks2014_2015-06-16.axsnp file. To avoid conflict, the snapshot name is unique and contains the creation date

  2. In Windows Control Panel, open Task Scheduler
  3. Click Create task
  4. Specify the task name and description on the General tab

    General tab - Create Task

  5. Set the time the snapshot will be created on the Triggers tab. Set the task’s occurrence to Daily

    Triggers tab - New trigger.png

  6. In the Actions tab create a new action, and browse for the Snapshot.bat file

    Actions tab - New action

And that’s all there is to it

Now, if any old version of any database or object is needed, just open the snapshot created on a specific date using ApexSQL Diff and easily synchronize your live database with it

Select the desired snapshot as a source file

Versioning the database structure doesn’t have to be complicated or involve using a source control system. With ApexSQL Diff, it is simpler, saves disk space, and can be automated.

April 4, 2013