|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server state for Point-in-time copyI wish to use a storage array snapshot capability to take point in time
copies of a SQL Server 2000 database. The database and logs etc, need to be placed in a consistent state to make the snapshot valid (i.e. flushed and no writes being performed). How do I put (from a script), the SQL DB into a quiesced (as Oracle would put it) state? and then resume it again a few seconds later? Thanks. There is no nedd to put in in such a state, just do a hot backup of the
database. The transaction that took place during the backup of the database will be applied to the backup at the end, so it is always consistent. HTH, Jens Süßmeyer. --- http://www.sqlserver2005.de --- Show quote "Mark R" wrote: > I wish to use a storage array snapshot capability to take point in time > copies of a SQL Server 2000 database. > > The database and logs etc, need to be placed in a consistent state to make > the snapshot valid (i.e. flushed and no writes being performed). How do I put > (from a script), the SQL DB into a quiesced (as Oracle would put it) state? > and then resume it again a few seconds later? > > Thanks. Use ALTER DATABASE. Put the database in SINGLE_USER state, do your stuff and
then put it back in MULTI_USER state. AMB Show quote "Mark R" wrote: > I wish to use a storage array snapshot capability to take point in time > copies of a SQL Server 2000 database. > > The database and logs etc, need to be placed in a consistent state to make > the snapshot valid (i.e. flushed and no writes being performed). How do I put > (from a script), the SQL DB into a quiesced (as Oracle would put it) state? > and then resume it again a few seconds later? > > Thanks. Hi
You could OFFLINE the user DB's, resulting in something similar as then there is no I/O pending. Single User mode could still have IO pending. SQL Server is not intended to be backed up in this way. -- Show quote-------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ "Alejandro Mesa" wrote: > Use ALTER DATABASE. Put the database in SINGLE_USER state, do your stuff and > then put it back in MULTI_USER state. > > > AMB > > "Mark R" wrote: > > > I wish to use a storage array snapshot capability to take point in time > > copies of a SQL Server 2000 database. > > > > The database and logs etc, need to be placed in a consistent state to make > > the snapshot valid (i.e. flushed and no writes being performed). How do I put > > (from a script), the SQL DB into a quiesced (as Oracle would put it) state? > > and then resume it again a few seconds later? > > > > Thanks. Thank for the replies, but I should point out that the DB will be live at the
time. I understand placing it in single user mode would terminate existing connections. The "Snapshot" is part of a DR solution. The aim is to "freeze" flushing cahces/logs, HW Snap the disk volume (takes a few tenths of a second), "thaw" the DB. Is there a standard SQL API / SP call I can use which is equivalent to Oracle's "alter system quiesce / unquiesce" ?? Show quote "Mark R" wrote: > I wish to use a storage array snapshot capability to take point in time > copies of a SQL Server 2000 database. > > The database and logs etc, need to be placed in a consistent state to make > the snapshot valid (i.e. flushed and no writes being performed). How do I put > (from a script), the SQL DB into a quiesced (as Oracle would put it) state? > and then resume it again a few seconds later? > > Thanks. > Is there a standard SQL API / SP call I can use which is equivalent to I believe there is. Search Books Online etc for "Virtual Device Interface". This is an API used by > Oracle's "alter system quiesce / unquiesce" ?? 3:rd party SQL Server backup vendors and it includes support for "freezing" SQL Server in order to perform "snapshot" backup. AFAIK. No, I haven't played with it myself, but it should be a starting point for you. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Mark R" <Ma***@discussions.microsoft.com> wrote in message news:99C2CFE5-E5DC-4EA0-8191-447D708CDB30@microsoft.com... > Thank for the replies, but I should point out that the DB will be live at the > time. I understand placing it in single user mode would terminate existing > connections. > > The "Snapshot" is part of a DR solution. The aim is to "freeze" flushing > cahces/logs, HW Snap the disk volume (takes a few tenths of a second), "thaw" > the DB. > > Is there a standard SQL API / SP call I can use which is equivalent to > Oracle's "alter system quiesce / unquiesce" ?? > > "Mark R" wrote: > >> I wish to use a storage array snapshot capability to take point in time >> copies of a SQL Server 2000 database. >> >> The database and logs etc, need to be placed in a consistent state to make >> the snapshot valid (i.e. flushed and no writes being performed). How do I put >> (from a script), the SQL DB into a quiesced (as Oracle would put it) state? >> and then resume it again a few seconds later? >> >> Thanks.  |
|||||||||||||||||||||||
Other interesting topics