SQL Server – Creating Database Snapshot for Every Previous Month

snapshotsql serverssms

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.

Best Answer

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.

Related Solutions

Sql-server – How to query for existing database snapshots in SQL server

A snapshot database will have an entry in sys.databases.source_database_id, so you can use something like the following to check for a current snapshot. Similarly, you could use the same check to DROP an existing snapshot before creating new.

IF NOT EXISTS(
    SELECT 
        * 
    FROM 
        sys.databases 
    WHERE 
        name = 'DatabaseA_Snapshot' 
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot] 
    ON (NAME=DatabaseA, FileName='<whatever>') 
    AS SNAPSHOT OF [DatabaseA]
END

SQL Server Agent – Job Step Fails Due to Missing Database Snapshot

Replace 'drop database [myDb_snapshot]' with:

declare @cmd nvarchar(max);
set @cmd = 'drop database [myDb_snapshot]';
exec (@cmd);

The entire SQL statement is evaluated for correctness prior to running it. Since the snapshot doesn't exist, it won't run. Dynamic SQL removes that limitation.

Best Answer

Related Solutions

Sql-server – How to query for existing database snapshots in SQL server

SQL Server Agent – Job Step Fails Due to Missing Database Snapshot

Related Question