how can we create a table snapshot?

Question

Post reply

how can we create a table snapshot?

rshm35

SSC-Addicted

Points: 496
More actions
September 22, 2009 at 4:39 pm

#98004

Table Snapshot based on th esize of the table

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

I'm not quite sure what you mean... do you mean you want to make a copy of the table AND the data?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rshm35 SSC-Addicted Points: 496 More actions · Answer 2

rshm35

SSC-Addicted

Points: 496

September 22, 2009 at 5:23 pm

#1057202

yes,snap shot..a read only copy for the table

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

If it's only one table, you could create a VIEW. If it's a whole bunch of tables for something like a reporting server and you have a SAN, you could use the SAN software to make nightly snapshots. On the 1TB system my previous company had, it only took a minute or two for the whole shootin' match.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 4

Note that snapshots on a SAN will work in the same way as snapshots in SQL Server - so there is an on-going, but slight, performance degradation, basically as the SAN marks up a list of 'modified blocks'.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

another option is to move your table to its own database and then create database snapthots at your desire.

Please refer to SQL Server 2005 Books Online topic "Database Snapshots" for prerequisites and impact.

Off course it is also described at SSC :w00t: http://www.sqlservercentral.com/articles/Administration/2733/

It always comes at a cost !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

gaji750 Valued Member Points: 59 More actions · Answer 6

Learn how to alter a master table in a read only or read write snapshot replication setup without dropping and recreating the snapshot or doing a full refresh, both of which can be extremely time and resource consuming. This leads to a time and effort savings of more than 98 percent.

One of the biggest challenges in administration of a snapshot replication environment (also called materialized view replication) is the usual maintenance of the snapshot after a modification of the master table. An example of this is adding columns or modifying the data type of a column. After a column is added to the master table, the only way the newly added column could be replicated to the replication site is by dropping the snapshot and recreating it. If the master table is large, the recreation process may take several hours as it brings all the data over the network. This also requires a large rollback segment both on the master and the replication sites and it may lead to the ORA-1555, "Snapshot Too Old" problem if the table access and rate of change is high. At a certain point, it may be impossible to even build the snapshot by recreating in this manner. At that point, the only option would be to do a full export and import of the table and recreate the snapshot by using the PREBUILT table option.

This problem, however, is not present in a multi-master setup. While the snapshot replication site presents numerous advantages in setting up and administration, this lack of ability to alter a master table easily poses real challenges to the DBAs maintaining the environment when they try to perform relatively trivial tasks like altering a table to add columns or change data types. This article presents a way to achieve these objectives and not having to recreate the snapshot or do a full refresh.

Rumah Dijual [/url]

mengembalikan jati diri bangsa [/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

The thing I don't like about any form of SQL Server replication (it's been fixed in 2k8) is when you need to alter a table which it won't let you do if the table is being replicated. That's why I like the SAN replication methods... set it and forget it...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 8

Jeff Moden (9/26/2009)
That's why I like the SAN replication methods... set it and forget it...

Have you ever had any issues with that? Because we had an issue once where it was in the middle of replicating blocks when a failure occurred, and boom - dead. Because the target was in a 'half block written' state, SQL Server was not a happy bunny when the node came up...

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

We never experienced such a thing... not even when the source or target when down for one reason or another. Maybe we just got lucky but it ran with no problems for about 4 years.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 10

Jeff Moden (9/26/2009)
We never experienced such a thing... not even when the source or target when down for one reason or another. Maybe we just got lucky but it ran with no problems for about 4 years.

I don't believe you and luck go together sir. Magic fairy dust maybe!! 🙂

No but seriously - was it over a WAN? And was the SAN replication tuned for SQL Server or out of the box? Maybe we just tried it way too early before they'd go SAN replication working properly! 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

I wasn't the one that set it up (our OPs guys set it up) so I don't know many of the details. The servers and the SAN were all in the same room and I believe there was some sort of direct connection from the servers to the SAN rather than going over a WAN. I don't believe anything was optimized just for SQL Server since they also did the same thing for the main Oracle server (also in the same room). It's been a couple of years since I've worked for that company but, IIRC, it was a Clarion SAN and the software to do the snapshots was optional and extra (but also made by Clarion) and certainly worth it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 12

Interesting - thanks for the info - I think I'll have to add it to my 'topics to read about' list 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone