Re: SQL Maintenance Plans and Atomic Backups of a Set of Databases

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

"Sophie Bunce" <SophieBunce@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:0CD264C8-1EDF-455C-B6FD-72BF465AF710@xxxxxxxxxxxxxxxx
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" <SophieBunce@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8E79EF2E-9920-4E16-8112-846080A2971A@xxxxxxxxxxxxxxxx
>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



.

Relevant Pages

  • Re: SQL 2000 restore to new server
    ... failed totally then your overnight backups may be the most recent files ... available to restore from. ... > All databases, user and system are backed up ... > I agree in the script solution....hence my included version in the ...
    (microsoft.public.sqlserver.server)
  • Re: SharePoint Transaction Logs - switched to simple, now what?
    ... I will delete the transaction backups and go ... Okay...the trn files are probably just your transaction log ... from when you had the databases in full recovery. ...
    (microsoft.public.sqlserver.connect)
  • Re: Very slow restores on new SQL Server 2000 virtual server
    ... FULL recovery mode do NOT flush committed transactions out of the tlog. ... >> Backups are on the same drive as the data and log files. ... >> rather than the restore itself. ... > Think I've answered my own question - some stupidly large transaction ...
    (microsoft.public.sqlserver.server)
  • Re: how to restore a single document from sharepoint?
    ... Well, if you take db backups, then it is possible to extract a single ... document with a third party tool. ... multiple content databases and that thier size never exceeds 100GB. ... To do a document restore, you determine which content database the site ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Mail not mounting
    ... A hard repair (eseutil /p) is done, in the event when there are no backups ... Check the consistency of the databases by running the following command ... If you do have a good back, it is better that you restore from tape backup ...
    (microsoft.public.exchange2000.admin)