I am new to SQL Server, I got a task to create a snapshot of existing database for every previous month until current month and do a snapshot once a month from now on.

I created a job to make snapshots once a month, but I am having problem how can i do this for past months.


How precise in time does this snapshot have to be? To the hour? Second? You still didn't answer that in your first comment.

So you are using the "Database Snapshot" technology. IMO, this will lead you into trouble in the long run. You add more and more overhead to your database for each snapshot you have. Having database snapshot existing for a longer time period isn't something that I would recommend. For more information, I suggest you read up on how database snapshot are implemented, with the copy-on-write technology. https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-ver15

I recommend you re-work your approach and use backups. Whether or not you will have to complement your full backups with log backups depend on the answer to my above questions.

As for past time, you have to go find old backups. There is nothing inside SQL Server that can produce a snapshot of a database from a previous point in time. (Except if you have some date columns and/or temporal turned on for each table, but puzzling that together is likely a huge undertaking.)

Having some date column for only some tables won't help you create snapshots for older data. Hunt down old backups and use them instead.

| improve this answer | |
  • I used this code to create database snapshots. ` DECLARE @SQL VARCHAR(MAX)=''; DECLARE @MONTH VARCHAR(2)= MONTH(GETDATE()) Declare @YEAR VARCHAR(4) = YEAR(GETDATE()) SELECT @SQL ='CREATE DATABASE DB_'+@MONTH+''+@YEAR+ ' ON (NAME=BIC_data, FILENAME=''C:\Snapshots\db'+@MONTH+'_'+@YEAR+'_SNAPSHOT.ss'') AS SNAPSHOT OF db1` EXECUTE (@SQL) I do have date columns, but not for each table. For example i have a InsetDate column in one of my tables. So could i use that column value as a date range to create snapshots? – magji Mar 20 at 13:00
  • I expanded my answer above, based on your comments. – Tibor Karaszi Mar 20 at 17:07

You cannot create a snapshot of a moment other than the present state of the whole database. As Microsoft says:

The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.

It also states that

Database snapshots always work on an entire database.

Therefore you can't filter what data is going to be present on the snapshot, the snapshot is a representation of the state of all data at the moment you create a snapshot. Your strategy of creating snapshots monthly should be performed from this month forth. To access the previous months data you're gonna have to restore backups.

I strongly recommend you read the Database Snapshots doc, mainly the Prerequisites for and Limitations on Database Snapshots section.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.