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]

Bernhard Krautschneider (be*********************@acp.at) writes:

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.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Greg D. Moore \(Strider\)]

"Bernhard Krautschneider" <be*********************@acp.at> wrote in message
news:de**************************@posting.google.c om...

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.
Yes.

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

If you mean an "instantaneous" 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.


thanks in advance,
Bernhard

[Bernhard Krautschneider]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Bernhard Krautschneider (be*********************@acp.at) writes:

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.

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.

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

[Bernhard Krautschneider]

Hi,
I figured out that all I need to do before initiating the storage
snapshot (eva business copy), is freezing the io of the database for a
certain time. This can be done by the VDI-Interface.
Concerning this matter I have two questions:

Are there any free tools/scripts that provide the functionality to
freeze and resume the io of the database?

Why doesn't Microsoft provide this functionality natively; for example
over T-sql? Or does it?

thanks,
Bernhard

[Erland Sommarskog]

Bernhard Krautschneider (be*********************@acp.at) writes:

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.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dan Guzman]

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" <be*********************@acp.at> wrote in message
news:de**************************@posting.google.c om...

Erland Sommarskog <so****@algonet.se> wrote in message

news:<Xn**********************@127.0.0.1>...

Bernhard Krautschneider (be*********************@acp.at) writes:

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.

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.

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

[Bernhard Krautschneider]

Hi,
I figured out that all I need to do before initiating the storage
snapshot (eva business copy), is freezing the io of the database for a
certain time. This can be done by the VDI-Interface.
Concerning this matter I have two questions:

Are there any free tools/scripts that provide the functionality to
freeze and resume the io of the database?

Why doesn't Microsoft provide this functionality natively; for example
over T-sql? Or does it?

thanks,
Bernhard