Keeping a "snapshot" of the database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Keeping a "snapshot" of the database

Author

Topic

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2003-01-31 : 11:56:34

Hello,

I created a web app for the front end of a catalog database. Something the customer now wants is to be able to create a "snapshot" of the database at a given time. Most likely this will happen when they go from (for example) the 2003 catalog to the 2004 catalog, but they want to be able to do it at any time.

When they create this snapshot they still want to be able to view the data and the administrator still wants to be able to modify it through the interface. I'm not sure of the best way to go about this. Is there something in SQL Server I can use to easily duplicate the data and still keep it linked to the interface? I want to keep it in the same database, so I don't have to change the connection properties. I thought maybe I could add a "version" field to the top level sections, but I'm not sure.

Thanks for your help, I really appreciate it.

Thanks,
Nick

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-31 : 12:10:08

The data that can vary from catalog to catalog will have to be duplicated so that the customer can change it.
You can have a table for each year and when you want to search all years use a view to combine all the tables, or keep all the info in one table and use a field that distinguishes between each catalog/year.

If you provide some ddl we can give you better advice.

Edited by - ValterBorges on 01/31/2003 12:12:10

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-31 : 13:25:49

This is definatly a stab in the dark, but maybe you could put a nullable "SnaphotID" on each of the items in the catalog. That way, you could have N snapshots (Create a table to hold the snapshot ID's and probably a description) as well as the current version of the catalog all without creating tons of extra tables etc.

I'm thinking that when you go to create a snapshot you can do an INSERT INTO...SELECT to grab all the rows where SnapshotID IS NULL and insert them back into the catalog table with the new SnapShot ID.

Anyone see any issues with this? Only thing I see is that you would probably have a TON of data in those tables, and depending on your indexing, it may slow your system down a bit.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2003-01-31 : 13:41:06

Thanks for your help Valter. I had thought of something along those lines, but the only problem is that I would then need a set of SPROCs for each snapshot, wouldn't I? (On another note, awesome picture in your profile. Thanks for serving.)

Michael, that was my first thought, but I thought there might possibly be a better way. The catalog isn't too large, so it would probably take at least a few snapshots before the database started to get really big. My other hesitation was that I would have to write quite a few INSERT statements, but maybe I'm just lazy.

Thanks again for your help.

Thanks,
Nick

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-31 : 14:43:06

Nick, You'd need to write two insert statements.
One insert statement to create the "SnapshotID record" which will be about two fields, and then one to create the snapshot.

 
CREATE TABLE #Catalog(ItemName VARCHAR(50), SnapshotID INT NULL)

--Note you'd not need to write these insert statements
--because you'd already have the existing catalog in the database
INSERT INTO #Catalog(ItemName, SnapShotID) VALUES('Widget 1', NULL)
INSERT INTO #Catalog(ItemName, SnapShotID) VALUES('Widget 2', NULL)
INSERT INTO #Catalog(ItemName, SnapShotID) VALUES('Widget 3', NULL)

SELECT * FROM #Catalog

--Create Snapshot record and return the ID
--Lets assume that we did that and got ID 1 back
DECLARE @SnapShotID INT
SET @SnapShotID = 1

INSERT INTO #Catalog(ItemName, SnapshotID)
SELECT ItemName, @SnapShotID
FROM #Catalog
where SnapshotID IS NULL


--Now we should haev 6 records.  
--3 for the "current" catalog, 
--and 3 for our "snapshot"
SELECT * FROM #Catalog

DROP TABLE #Catalog

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-01 : 22:45:29

quote:

(On another note, awesome picture in your profile. Thanks for serving.)

It was an honor, I thing everyone that can should do it and give something back. I really worked out well for me comming out of high school I had no clue what I was going to do with my life. It helped me appreciate hard work and commitment and respect the freedom I enjoy.

You could also design it so that you have a date range , 2 columns associated with each catalog item and you maintain the dates for which the item is valid.

If you want all the items in 2003 you would search where startdate >= 1/1/2003 and enddate <=12/31/2003

If there is an update to that item have a revision column and use a
correlated subquery

where (a.revision = (select max() from items b where a.itemid = b.itemid and startdate >=1/1/2003 and enddate <=12/31/2003
group by itemid)
to get only the latest revision

This way you can create snapshots at any point in time with out any extra work.

Edited by - ValterBorges on 02/01/2003 22:54:04

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources