SQL Maintenance Plans and Atomic Backups of a Set of Databases

15 Jan 2009 3:18 PM

Sophie Bunce

I am new to this tool and have a general question.

Is it possible to set up a SQL Maintenance Plan that guarantees an atomic
backup of a set of several databases? It is imperative that both the full
backups and the transaction logs be backed up atomicially because there are
dependencies between the databases.

If not, what is the best way to handle this requirement?

--
Sophie Bunce

15 Jan 2009 5:34 PM

Andrew J. Kelly

I don't know what you mean by Atomically when it comes to the FULL and Log
backups. They are two different animals with different purposes. A FULL
backup does include a tiny log backup in it for recoverability reasons if
you don't apply any log backups when you restore. The FULL backup is Atomic
in that it is a complete snapshot of the db at the time the backup completes
with data integrity ensured by rolling back or forward any in flight trans
as needed when it is restored. If you need to get a point in time view then
you do need to have log backups of the db as well and you can then specify a
point in time to recover to. If you are referring to multiple dbs that all
need to be backed up at exactly the same time it doesn't work that way. They
finish when they finish. However you can add a marker to the transaction
logs in each of the dbs and then do a restore of the logs using the
STOPATMARK option to get them all to a relatively consistent state but I
don't think it is guaranteed to give exact results due to timing issues of
when it is applied. See RESTORE in BOL for more details.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

Show quoteHide quote

"Sophie Bunce" <SophieBu***@discussions.microsoft.com> wrote in message
news:8E79EF2E-9920-4E16-8112-846080A2971A@microsoft.com...
>I am new to this tool and have a general question.
>
> Is it possible to set up a SQL Maintenance Plan that guarantees an atomic
> backup of a set of several databases? It is imperative that both the full
> backups and the transaction logs be backed up atomicially because there
> are
> dependencies between the databases.
>
> If not, what is the best way to handle this requirement?
>
> --
> Sophie Bunce

15 Jan 2009 6:25 PM

Sophie Bunce

Andrew,

Thank you for your response.

By "atomic" I mean "as a unit". (The term comes from a critical section in
code thas has to all be executed as a unit before giving a timeslice to
another process.) In other words, I need to be able to restore ALL 12 TFS
databases so that the end result is that they are restored to a specific
point in time.

I had already been wondering if marking them and restoring them to the mark
using marking and then STOPMARK. Couldn't you ensure that the result is
perfect IF you use a transaction to mark all 12 databases. (i.e. Begin the
transaction, mark all 12 databases, end the transaction), and then when you
restore you can restore them all to the same mark. At least the
documentation I've read seems to suggest this. However, I'm not a DBA, so I
could be mistaken.

Now that you understand my question a bit better, does a SQL maintenance
plan in which you select a set of several databases guarantee that they will
be restored to the same point in time? It sounds like the answer is no.
Could you confirm?

If then answer is no, then we will abandon SQL maintenance plans for this
particular backup and will script it using these transaction marks.

Sophie Bunce

Show quoteHide quote

"Andrew J. Kelly" wrote:

> I don't know what you mean by Atomically when it comes to the FULL and Log
> backups. They are two different animals with different purposes. A FULL
> backup does include a tiny log backup in it for recoverability reasons if
> you don't apply any log backups when you restore. The FULL backup is Atomic
> in that it is a complete snapshot of the db at the time the backup completes
> with data integrity ensured by rolling back or forward any in flight trans
> as needed when it is restored. If you need to get a point in time view then
> you do need to have log backups of the db as well and you can then specify a
> point in time to recover to. If you are referring to multiple dbs that all
> need to be backed up at exactly the same time it doesn't work that way. They
> finish when they finish. However you can add a marker to the transaction
> logs in each of the dbs and then do a restore of the logs using the
> STOPATMARK option to get them all to a relatively consistent state but I
> don't think it is guaranteed to give exact results due to timing issues of
> when it is applied. See RESTORE in BOL for more details.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Sophie Bunce" <SophieBu***@discussions.microsoft.com> wrote in message
> news:8E79EF2E-9920-4E16-8112-846080A2971A@microsoft.com...
> >I am new to this tool and have a general question.
> >
> > Is it possible to set up a SQL Maintenance Plan that guarantees an atomic
> > backup of a set of several databases? It is imperative that both the full
> > backups and the transaction logs be backed up atomicially because there
> > are
> > dependencies between the databases.
> >
> > If not, what is the best way to handle this requirement?
> >
> > --
> > Sophie Bunce
>
>

15 Jan 2009 9:22 PM

Russell Fields

Sophie,

Actually, the maintenance plans are still a valuable method of running
backups, though you can also create scripts for them. However, multiple
database backups are not (as Andrew already said) coordinated with one
another.

Yes, the documentation indicates that STOPATMARK should work for you,
provided that you update all 12 databases inside the same marked
transaction. As in the BOL example, this could be nothing but a marker
table in each database that gets updated. The update, when committed,
writes the mark to the log.

However, no matter when you decide to set the transaction marks, it is the
restore databases that will make use of them. So make sure that you have a
good backup schedule from which you can restore. (This is really a disaster
recovery scenario we are discussing.) Perhaps weekly full backups, daily
differential backups, and hourly log backups. (Just as an example.)

You could run your marking transaction across the 12 databases just before
each backup. Of course, in the case of a restore of all the databases, any
changes after the STOPATMARK are lost, even it they were changes that were
confined to a single database without any impact on the others.

Test this out in your test environment and make sure you are getting what
you want.

RLF

Show quoteHide quote

"Sophie Bunce" <SophieBu***@discussions.microsoft.com> wrote in message
news:0CD264C8-1EDF-455C-B6FD-72BF465AF710@microsoft.com...
> Andrew,
>
> Thank you for your response.
>
> By "atomic" I mean "as a unit". (The term comes from a critical section in
> code thas has to all be executed as a unit before giving a timeslice to
> another process.) In other words, I need to be able to restore ALL 12
> TFS
> databases so that the end result is that they are restored to a specific
> point in time.
>
> I had already been wondering if marking them and restoring them to the
> mark
> using marking and then STOPMARK. Couldn't you ensure that the result is
> perfect IF you use a transaction to mark all 12 databases. (i.e. Begin
> the
> transaction, mark all 12 databases, end the transaction), and then when
> you
> restore you can restore them all to the same mark. At least the
> documentation I've read seems to suggest this. However, I'm not a DBA, so
> I
> could be mistaken.
>
> Now that you understand my question a bit better, does a SQL maintenance
> plan in which you select a set of several databases guarantee that they
> will
> be restored to the same point in time? It sounds like the answer is no.
> Could you confirm?
>
> If then answer is no, then we will abandon SQL maintenance plans for this
> particular backup and will script it using these transaction marks.
>
>
> Sophie Bunce
>
>
> "Andrew J. Kelly" wrote:
>
>> I don't know what you mean by Atomically when it comes to the FULL and
>> Log
>> backups. They are two different animals with different purposes. A FULL
>> backup does include a tiny log backup in it for recoverability reasons if
>> you don't apply any log backups when you restore. The FULL backup is
>> Atomic
>> in that it is a complete snapshot of the db at the time the backup
>> completes
>> with data integrity ensured by rolling back or forward any in flight
>> trans
>> as needed when it is restored. If you need to get a point in time view
>> then
>> you do need to have log backups of the db as well and you can then
>> specify a
>> point in time to recover to. If you are referring to multiple dbs that
>> all
>> need to be backed up at exactly the same time it doesn't work that way.
>> They
>> finish when they finish. However you can add a marker to the transaction
>> logs in each of the dbs and then do a restore of the logs using the
>> STOPATMARK option to get them all to a relatively consistent state but I
>> don't think it is guaranteed to give exact results due to timing issues
>> of
>> when it is applied. See RESTORE in BOL for more details.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>>
>> "Sophie Bunce" <SophieBu***@discussions.microsoft.com> wrote in message
>> news:8E79EF2E-9920-4E16-8112-846080A2971A@microsoft.com...
>> >I am new to this tool and have a general question.
>> >
>> > Is it possible to set up a SQL Maintenance Plan that guarantees an
>> > atomic
>> > backup of a set of several databases? It is imperative that both the
>> > full
>> > backups and the transaction logs be backed up atomicially because there
>> > are
>> > dependencies between the databases.
>> >
>> > If not, what is the best way to handle this requirement?
>> >
>> > --
>> > Sophie Bunce
>>
>>

15 Jan 2009 11:15 PM

Erland Sommarskog

Sophie Bunce (SophieBu***@discussions.microsoft.com) writes:

> By "atomic" I mean "as a unit". (The term comes from a critical section
> in code thas has to all be executed as a unit before giving a timeslice
> to another process.) In other words, I need to be able to restore ALL
> 12 TFS databases so that the end result is that they are restored to a
> specific point in time.

I think the only really good answer to the question is that if you want a
consistent backup of the data, it has to be one database. As soon as you
you start to deal with multiple databases, you are taking chances.

STOPMARK maybe works, but wouldn't it be possible for someone to perform
an operation in one TFS database before you mark the next?

The best option may be that in case of a disaster that you restore all
databases to the same point in time. And if you aim at that *restore*
plan, it does not really matter when the backups were taken. Then you
can backup the databases one after an other if you like.

Whatever you arrive at, it is imperative that you test your restore
strategy so that you know that it works.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

21 Jan 2009 9:29 PM

Itamar, Nobhill Software

On Jan 15, 5:18 pm, Sophie Bunce
<SophieBu***@discussions.microsoft.com> wrote:

> I am new to this tool and have a general question.
>
> Is it possible to set up a SQL Maintenance Plan that guarantees an atomic
> backup of a set of several databases? It is imperative that both the full
> backups and the transaction logs be backed up atomicially because there are
> dependencies between the databases.
>
> If not, what is the best way to handle this requirement?
>
> --
> Sophie Bunce

On Jan 15, 5:18 pm, Sophie Bunce
<SophieBu***@discussions.microsoft.com> wrote:

> I am new to this tool and have a general question.
>
> Is it possible to set up a SQL Maintenance Plan that guarantees an atomic
> backup of a set of several databases? It is imperative that both the full
> backups and the transaction logs be backed up atomicially because there are
> dependencies between the databases.
>
> If not, what is the best way to handle this requirement?
>
> --
> Sophie Bunce

Sophie,
If you are looking for full save of the database's entities, you can
check one of our Tools, called RANDOLPH,
( I'll be happy to know what you think of it.)

http://nobhillsoft.com/Randolph_detail1.aspx

Nobhill software.