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 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 20190116 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 that can be used

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 all DDL scripts will be at disposal, all that needs to be done is to find the right one and find the optimal way to deploy it. Do the objects that will be changed need to be dropped and recreated? Can they be altered?

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

4. 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 an older version of a database is needed 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\ApexSQL Diff\ApexSQLDiff.com" /s1:SQLSERVER2019 /d1:AdventureWorks2018 /sn2:"E:\Snapshots\AdventureWorks2018_2019-01-16.axsnp" /ex

   This example creates a snapshot of the AdventureWorks2018 database on the SQLSERVER2019 instance and stores it as E:\Snapshots\AdventureWorks2018_2019-01-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 the Create task command in the right panel:

   

4. Specify the task name and description under the General tab:

   

5. Switch to the Triggers tab and click the New button to initiate the New Trigger dialog. Set the time the snapshot will be created and the task’s occurrence to Daily:

   

6. Switch to the Actions tab and click the New button to create a new action, and browse for the Snapshot.bat file:

   

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 a live database with it:

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.