473,321 Members | 1,667 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

storage snapshot

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
Jul 20 '05 #1
7 3542
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
Jul 20 '05 #2

"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

Jul 20 '05 #3
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
Jul 20 '05 #4
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
Jul 20 '05 #5
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
Jul 20 '05 #6
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

Jul 20 '05 #7
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
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Bernhard Krautschneider | last post by:
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
8
by: Jean-Marc Blaise | last post by:
Dear all, It seems there is a problem with some snapshot table functions, if you try to divide by some element that is unset or equal to 0. This generates a trap file with the db2fmp.exe...
4
by: Civilian_Target | last post by:
Is there any way in DB2 to automate the taking of a snapshot, for example, a function that will cause a db2 snapshot to be taken every 5 minutes and written to a uniquely named file? Am I better...
5
by: tom | last post by:
Hey All- I've exported a report to Access's "snapshot" format and want to put it on a website for users to view. However, when I click on the link to the snapshot file, I notice that my browser...
9
by: bluedolphin | last post by:
Hello All: I have been brought onboard to help on a project that had some performance problems last year. I have taken some steps to address the issues in question, but a huge question mark...
3
by: Randall Skelton | last post by:
What is the storage cost of a null entry in a column? i.e. does a null entry of type integer, float8 or text consume the same amount of storage as one that is filled? I ask because I have...
3
by: Laurence | last post by:
Hi folks, DB2 UDB supports automatic storage management in v8.2.2 and v9. The question is how do I know the databases and/or tablespaces are enable "automatic storage management" or not after...
4
by: beena | last post by:
All, I'm new to the concept of automatic storage... I'm looking at the database setup by a vendor. I see few tablespaces showing up with automatic storage - Yes. Tablespace ID ...
8
by: grant | last post by:
Hi I've copied Stephens code into my db, and can get it to work, but only on "plain Jane" reports with no images. Most of my reports has an unbound image obluect in them that I set to an...
6
by: cristizaharioiu | last post by:
Hello, I am beginner with db2 ( DB2 v9.1.0.1 running on RHEL 4); this is my first post. I have this error " SQL0968C The file system is full. SQLSTATE=57011"" in my instance configured with...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.