Nightly Update / Fallback protection

I have become involved with an assessment of a client's nightly update
procedures, and I keep running into the following practice.
The client consistently copies databases off to separate locations and
performs updates upon the new copy. After successful update, the
updated database is then copied back to the origin location and
becomes the new version. All of this, obviously, to protect
themselves in case of error. I can't help but suspect that SQL Server
provides an easier way to do it.
In the mainframe universe, this used to be accomplished via
generational datasets which would be promoted as the baseline when the
update completes. I guess what I'm asking is if SQL Server provides
any kind of "checkpoint-fallback" capability that the client could use
instead of this monstrous time-consuming practice. When talking
thousands of updates in a batch, I'm not sure transactioning would be
the best way to do it. That would be one big honking rollback.
Thanks in advance to any of you architects out there!SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
(CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
the Copy databases' database files. This way, the copy database always has the data as it was when
the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
the Orig database' files and you are back where you began. Concept is called "Copy on write".
Same-same (concept) but different (implementation). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||It is called a backup before you do any changes. Then if there is an issue
you simply restore.
--
Andrew J. Kelly SQL MVP
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||On Apr 23, 1:23 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
> modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
> the Copy databases' database files. This way, the copy database always has the data as it was when
> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
> the Orig database' files and you are back where you began. Concept is called "Copy on write".
> Same-same (concept) but different (implementation). :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "BizEd" <EdMathia...@.gmail.com> wrote in message
> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>
> >I have become involved with an assessment of a client's nightly update
> > procedures, and I keep running into the following practice.
> > The client consistently copies databases off to separate locations and
> > performs updates upon the new copy. After successful update, the
> > updated database is then copied back to the origin location and
> > becomes the new version. All of this, obviously, to protect
> > themselves in case of error. I can't help but suspect that SQL Server
> > provides an easier way to do it.
> > In the mainframe universe, this used to be accomplished via
> > generational datasets which would be promoted as the baseline when the
> > update completes. I guess what I'm asking is if SQL Server provides
> > any kind of "checkpoint-fallback" capability that the client could use
> > instead of this monstrous time-consuming practice. When talking
> > thousands of updates in a batch, I'm not sure transactioning would be
> > the best way to do it. That would be one big honking rollback.
> > Thanks in advance to any of you architects out there!- Hide quoted text -
> - Show quoted text -
Tibor,
Thanks. That's the type of functionality I was looking for. A simple
backup is not a sufficient solution when you are talking about a
multiple stage update process. If you wish to restart a step in the
process, the backup would necessitate going all the way back to the
beginning. Your solution is in essence "a delta file" which provides
functionality much more in line with the concept of mainframe
generational data sets!
Now to get the client on to SQL 2005....harrumph!|||Note that you can have several "delta-files" (several database snapshots). But if you want to
restore from any of them, you need for first DROP DATABASE for all those snapshots except the one to
restore from.
Another important thing is that when you restore from a database snapshot, you break the log backup
chain sequence for your database. So, if you are doing log backups for that database, you want to do
a BACKUP DATABASE after restoring from a database snapshot.
And, as always, make sure you read all about this in Books Online first. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177365104.403457.180780@.b58g2000hsg.googlegroups.com...
> On Apr 23, 1:23 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
>> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are
>> initially
>> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take.
>> Then
>> modify your Orig database. As you modify Orig database, the pages to be modified are first copied
>> to
>> the Copy databases' database files. This way, the copy database always has the data as it was
>> when
>> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
>> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back
>> to
>> the Orig database' files and you are back where you began. Concept is called "Copy on write".
>> Same-same (concept) but different (implementation). :-)
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "BizEd" <EdMathia...@.gmail.com> wrote in message
>> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>>
>> >I have become involved with an assessment of a client's nightly update
>> > procedures, and I keep running into the following practice.
>> > The client consistently copies databases off to separate locations and
>> > performs updates upon the new copy. After successful update, the
>> > updated database is then copied back to the origin location and
>> > becomes the new version. All of this, obviously, to protect
>> > themselves in case of error. I can't help but suspect that SQL Server
>> > provides an easier way to do it.
>> > In the mainframe universe, this used to be accomplished via
>> > generational datasets which would be promoted as the baseline when the
>> > update completes. I guess what I'm asking is if SQL Server provides
>> > any kind of "checkpoint-fallback" capability that the client could use
>> > instead of this monstrous time-consuming practice. When talking
>> > thousands of updates in a batch, I'm not sure transactioning would be
>> > the best way to do it. That would be one big honking rollback.
>> > Thanks in advance to any of you architects out there!- Hide quoted text -
>> - Show quoted text -
> Tibor,
> Thanks. That's the type of functionality I was looking for. A simple
> backup is not a sufficient solution when you are talking about a
> multiple stage update process. If you wish to restart a step in the
> process, the backup would necessitate going all the way back to the
> beginning. Your solution is in essence "a delta file" which provides
> functionality much more in line with the concept of mainframe
> generational data sets!
> Now to get the client on to SQL 2005....harrumph!
>|||As a standard process, this seems excessive.
You are aware that within the processing, SQLServer (like most major
databases) allows you to define a transaction that holds off
commitment until all the steps have completed successfully, and can be
rolled back if any errors are detected?
Josh
On 23 Apr 2007 11:10:16 -0700, BizEd <EdMathias18@.gmail.com> wrote:
>I have become involved with an assessment of a client's nightly update
>procedures, and I keep running into the following practice.
>The client consistently copies databases off to separate locations and
>performs updates upon the new copy. After successful update, the
>updated database is then copied back to the origin location and
>becomes the new version. All of this, obviously, to protect
>themselves in case of error. I can't help but suspect that SQL Server
>provides an easier way to do it.
>In the mainframe universe, this used to be accomplished via
>generational datasets which would be promoted as the baseline when the
>update completes. I guess what I'm asking is if SQL Server provides
>any kind of "checkpoint-fallback" capability that the client could use
>instead of this monstrous time-consuming practice. When talking
>thousands of updates in a batch, I'm not sure transactioning would be
>the best way to do it. That would be one big honking rollback.
>Thanks in advance to any of you architects out there!