storage snapshot

[Bernhard Krautschneider]

hello group,

is it possible to do a storage snapshot of a running ms-sql database
without losing transactions?
What tasks must be done before such a snapshot.

thanks in advance,
Bernhard

[Erland Sommarskog]

Re: storage snapshot

Bernhard Krautschneider (bernhard.kraut schneider@acp.a t) writes:[color=blue]
> is it possible to do a storage snapshot of a running ms-sql database
> without losing transactions?
> What tasks must be done before such a snapshot.[/color]

Not sure what you mean. Are you talking about snapshot replication? Or do
you mean taking a backup? In the latter case, the answer is yes. BACKUP
in SQL Server is designed to be run on a live database - anything else
would be unacceptable for a 24/7 shop.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Greg D. Moore \(Strider\)]

Re: storage snapshot


"Bernhard Krautschneider" <bernhard.kraut schneider@acp.a t> wrote in message
news:dec9f4d1.0 405021135.2f4ca 0b7@posting.goo gle.com...[color=blue]
> hello group,
>
> is it possible to do a storage snapshot of a running ms-sql database
> without losing transactions?
> What tasks must be done before such a snapshot.[/color]

Yes.

As Erland stated, you can do a standard backup and capture an accurate
snapshot of the database.

If you mean an "instantane ous" backup, then you need the hardware and
software to do that. For example, Network Appliances will allow snapshots
of that nature to occur. Basically their software issues a command to the
OS, it quiesces the DB for a second or so (i.e. no reads/writes occur), the
Netapp takes a snapshot of the equivalent of the FAT, and then any new
writes occur as deltas. In the meantime, you can then take that backup and
move it to tape, etc.

[color=blue]
>
> thanks in advance,
> Bernhard[/color]

[Bernhard Krautschneider]

Re: storage snapshot

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns94DDF3 D4C94C2Yazorman @127.0.0.1>...[color=blue]
> Bernhard Krautschneider (bernhard.kraut schneider@acp.a t) writes:[color=green]
> > is it possible to do a storage snapshot of a running ms-sql database
> > without losing transactions?
> > What tasks must be done before such a snapshot.[/color]
>
> Not sure what you mean. Are you talking about snapshot replication? Or do
> you mean taking a backup? In the latter case, the answer is yes. BACKUP
> in SQL Server is designed to be run on a live database - anything else
> would be unacceptable for a 24/7 shop.[/color]

The storage I use is able to do a snapshot-copy of all disks to
another storage device. The problem is, that while you do this
snapshot, the database has to be in a consistent state to be usable as
a disaster solution. Within oracle you could do that via the "begin
backup"-clause.

Bernhard

[Erland Sommarskog]

Re: storage snapshot

Bernhard Krautschneider (bernhard.kraut schneider@acp.a t) writes:[color=blue]
> The storage I use is able to do a snapshot-copy of all disks to
> another storage device. The problem is, that while you do this
> snapshot, the database has to be in a consistent state to be usable as
> a disaster solution. Within oracle you could do that via the "begin
> backup"-clause.[/color]

If you use some software outside SQL Server to do this, you need to find
out whether the software supports backup of SQL Server databases that are
online. I don't know really what you could expect, but I would prefer some
sort of certification to be sure.

Until the opposite is proven, assume that it does not work.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Dan Guzman]

Re: storage snapshot

To add to Erland's response, below is the relevant information from the SQL
2000 Books Online. Basically, SQL Server and your storage solution need to
communicate using the SQL Server virtual device interface (VDI) in order to
coordinate the snapshot backup timing. Your storage solution needs to
support this API for reliable snapshot backups of SQL Server databases.

<Excerpt href="adminsql. chm::/ad_bkprst_2upf. htm">
The snapshot backup and restore functionality is accomplished in cooperation
with third party hardware and/or software vendors. These vendors use
features of SQL Server 2000 designed for this purpose. The underlying backup
technology creates an instantaneous copy of the data being backed up. This
is typically accomplished by splitting a mirrored set of disks or creating a
copy of a disk block when it is written, preserving the original. At restore
time, the original is made available immediately and synchronizing the
underlying disks is done in the background, resulting in almost
instantaneous restores.
</Excerpt>

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Bernhard Krautschneider" <bernhard.kraut schneider@acp.a t> wrote in message
news:dec9f4d1.0 405021930.4ee82 ee8@posting.goo gle.com...[color=blue]
> Erland Sommarskog <sommar@algonet .se> wrote in message[/color]
news:<Xns94DDF3 D4C94C2Yazorman @127.0.0.1>...[color=blue][color=green]
> > Bernhard Krautschneider (bernhard.kraut schneider@acp.a t) writes:[color=darkred]
> > > is it possible to do a storage snapshot of a running ms-sql database
> > > without losing transactions?
> > > What tasks must be done before such a snapshot.[/color]
> >
> > Not sure what you mean. Are you talking about snapshot replication? Or[/color][/color]
do[color=blue][color=green]
> > you mean taking a backup? In the latter case, the answer is yes. BACKUP
> > in SQL Server is designed to be run on a live database - anything else
> > would be unacceptable for a 24/7 shop.[/color]
>
> The storage I use is able to do a snapshot-copy of all disks to
> another storage device. The problem is, that while you do this
> snapshot, the database has to be in a consistent state to be usable as
> a disaster solution. Within oracle you could do that via the "begin
> backup"-clause.
>
> Bernhard[/color]