October 20, 2020 at 12:58 pm
I am a newbie to SQL Server administration and config. Apologies if I appear to confuse/mix terminologies here.
I am trying to find a way to take a snapshot of current state of database - lock it in some sort of a filegroup/data file - treating it as master data. Track further DML/updates on the data into a new filegroup /data file. I dont intend to change the table definitions and only the data can change in the intermediary state.
Is it possible to achieve this in SQL Server?
I am trying to find a solution of creating master snapshot at filesystem level and be able to restore master snapshot.. at any time.
Thanks in advance!
October 20, 2020 at 1:06 pm
Sounds like you need database snapshots.
John
October 20, 2020 at 9:18 pm
Or a simple backup/restore process - you can backup the current database and restore as a 'master' database and set the new database as read-only (to prevent any changes to the master database).
Database snapshots would work as well for a limited time-frame. Depending on the amount of changes being made to the current database you could see issues with keeping a snapshot long term. Short-term as in less than a couple of days - at most...again, depending on activity.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 22, 2020 at 8:43 am
I am a newbie to SQL Server administration and config. Apologies if I appear to confuse/mix terminologies here.
I am trying to find a way to take a snapshot of current state of database - lock it in some sort of a filegroup/data file - treating it as master data. Track further DML/updates on the data into a new filegroup /data file. I dont intend to change the table definitions and only the data can change in the intermediary state.
Is it possible to achieve this in SQL Server?
I am trying to find a solution of creating master snapshot at filesystem level and be able to restore master snapshot.. at any time.
Thanks in advance!
You can schedule to take snapshot which will take a copy of database at that point. The base database will still continue to grow and will have all the changes.You can restore from snapshot but only on the same DB and have to delete all other snapshot.
Can you help us understand more about your requirement?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply