Data file backups and ArcServe

We are attempting to backup our SQL Server data files (MDF
and LDF) onto tape using Brightstor ARCserve. (I have my
normal daily backups - this is for offsite redundancy) The
Arcserve backups of the data files are failing
consistantly. I suspect that arcserve is not able to get
an image of the data files because they are open and
active - transactions run on this server 24/7. Does anyone
have experience with this? Is there a workaround besides
stopping the SQL service? I'm satisfied with taping the
daily backups, but my se wants the data itself.
RLR
You would need to purchase the Open File Agent in order to
backup the files while the SQL Server is running.
-Sue
On Thu, 22 Apr 2004 07:18:17 -0700, "rlruss"
<rlrsysdev@.mindspring.com> wrote:

>We are attempting to backup our SQL Server data files (MDF
>and LDF) onto tape using Brightstor ARCserve. (I have my
>normal daily backups - this is for offsite redundancy) The
>Arcserve backups of the data files are failing
>consistantly. I suspect that arcserve is not able to get
>an image of the data files because they are open and
>active - transactions run on this server 24/7. Does anyone
>have experience with this? Is there a workaround besides
>stopping the SQL service? I'm satisfied with taping the
>daily backups, but my se wants the data itself.
>RLR
|||According to my se, we have Open agent installed - it works for everything except the db files. I created a non-sql file with the mdf extension, and it copied it fine. We're going with taping the dailies until this gets figured out - se isn't thrilled, bu
t I can live with it.
so it goes..
sql

Data change during backup.

I have found this article:
Dynamic Backups
SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
Let's look at how these two processes differ. When SQL Server begins a
backup, it notes the Log Sequence Number (LSN) of the oldest active
transaction and performs a checkpoint, which synchronizes the pages on disk
with the pages in the cache memory. Then, SQL Server starts the backup,
reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
needs to update a record, SQL Server allows the update if the backup process
has already backed up the record. Otherwise, SQL Server holds up the request
for a moment-long enough for the backup process to jump ahead and back up the
extent containing that record. SQL Server 6.5 then lets the update request
proceed and resumes the backup process at the point it was when it was
interrupted. When SQL Server 6.5 reaches this extent again, the backup
process skips it, because the process has already backed up this extent.
SQL Server 7.0, in contrast, doesn't worry about whether users are reading
or changing pages. SQL Server 7.0 just backs up the extents sequentially,
which is faster than jumping around as SQL Server 6.5 does. Because SQL
Server 7.0 doesn't jump ahead to back up extents before users change data,
you could end up with inconsistent data. However, SQL Server 7.0 also
introduced the ability to capture data changes that users make while the
backup is in progress. When SQL Server 7.0 reaches the end of the data, the
backup process backs up the transaction log, capturing the changes users made
during the backup process. Although dynamic backup comes with a performance
penalty, Microsoft promises no more than about a 6 percent to 7 percent
performance reduction, which most users would never notice. Scheduling
backups during low database activity is still a good idea, but if you have to
back up the transaction log several times a day, you won't be able to avoid
having some users connected.
Because a backup can take considerable time, SQL Server 7.0's process is a
welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
when SQL Server finishes the backup.
If you're backing up just the transaction log, at the end of the backup
process, SQL Server 7.0 truncates the log, removing all transactions before
the LSN it recorded for the oldest ongoing transaction. Truncating the log
frees up space in the log and keeps it from filling up. (The log could still
fill up, however, if you have a long-running transaction that isn't
completing.) Remember that SQL Server doesn't truncate any log entry that has
an LSN greater than that of the oldest active transaction.
The question is:
What is the behavior of Sql Server 2000, when a long-running transaction
work during backup?
SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
the backup ?Same as 7.0. Data pages are backuped as they are, and the transaction log records generated during
the backup process are also included. If the transaction isn't finished at end-time of the backup,
the COMMIT log records isn't included in the backup and when you restore the backup, the transaction
will be rolled back.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:89E94015-4FD0-4EE6-97E0-FAE4E591919B@.microsoft.com...
>I have found this article:
> Dynamic Backups
> SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
> Let's look at how these two processes differ. When SQL Server begins a
> backup, it notes the Log Sequence Number (LSN) of the oldest active
> transaction and performs a checkpoint, which synchronizes the pages on disk
> with the pages in the cache memory. Then, SQL Server starts the backup,
> reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
> needs to update a record, SQL Server allows the update if the backup process
> has already backed up the record. Otherwise, SQL Server holds up the request
> for a moment-long enough for the backup process to jump ahead and back up the
> extent containing that record. SQL Server 6.5 then lets the update request
> proceed and resumes the backup process at the point it was when it was
> interrupted. When SQL Server 6.5 reaches this extent again, the backup
> process skips it, because the process has already backed up this extent.
> SQL Server 7.0, in contrast, doesn't worry about whether users are reading
> or changing pages. SQL Server 7.0 just backs up the extents sequentially,
> which is faster than jumping around as SQL Server 6.5 does. Because SQL
> Server 7.0 doesn't jump ahead to back up extents before users change data,
> you could end up with inconsistent data. However, SQL Server 7.0 also
> introduced the ability to capture data changes that users make while the
> backup is in progress. When SQL Server 7.0 reaches the end of the data, the
> backup process backs up the transaction log, capturing the changes users made
> during the backup process. Although dynamic backup comes with a performance
> penalty, Microsoft promises no more than about a 6 percent to 7 percent
> performance reduction, which most users would never notice. Scheduling
> backups during low database activity is still a good idea, but if you have to
> back up the transaction log several times a day, you won't be able to avoid
> having some users connected.
> Because a backup can take considerable time, SQL Server 7.0's process is a
> welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
> when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
> when SQL Server finishes the backup.
> If you're backing up just the transaction log, at the end of the backup
> process, SQL Server 7.0 truncates the log, removing all transactions before
> the LSN it recorded for the oldest ongoing transaction. Truncating the log
> frees up space in the log and keeps it from filling up. (The log could still
> fill up, however, if you have a long-running transaction that isn't
> completing.) Remember that SQL Server doesn't truncate any log entry that has
> an LSN greater than that of the oldest active transaction.
> The question is:
> What is the behavior of Sql Server 2000, when a long-running transaction
> work during backup?
> SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
> the backup ?

Daily Backups SQL 2005 Standard Edition

With SQL 2000 I was backing up to a NAS server by mapping a drive share to
Z. Is there a reason that I cannot do this in SQL 2005 now or should I be
focused on a networking issue?
Thanks!
ChrisBackups are always done with the privileges of the account SQL Server is
running under. Does this account have access to that share? You never want
to use mapped drives. Use UNC pathing instead.
Andrew J. Kelly SQL MVP
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:eRk0J22LGHA.420@.tk2msftngp13.phx.gbl...
> With SQL 2000 I was backing up to a NAS server by mapping a drive share to
> Z. Is there a reason that I cannot do this in SQL 2005 now or should I be
> focused on a networking issue?
> Thanks!
> Chris
>

Daily Backups SQL 2005 Standard Edition

With SQL 2000 I was backing up to a NAS server by mapping a drive share to
Z. Is there a reason that I cannot do this in SQL 2005 now or should I be
focused on a networking issue?
Thanks!
Chris
Backups are always done with the privileges of the account SQL Server is
running under. Does this account have access to that share? You never want
to use mapped drives. Use UNC pathing instead.
Andrew J. Kelly SQL MVP
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:eRk0J22LGHA.420@.tk2msftngp13.phx.gbl...
> With SQL 2000 I was backing up to a NAS server by mapping a drive share to
> Z. Is there a reason that I cannot do this in SQL 2005 now or should I be
> focused on a networking issue?
> Thanks!
> Chris
>

Daily Backups SQL 2005 Standard Edition

With SQL 2000 I was backing up to a NAS server by mapping a drive share to
Z. Is there a reason that I cannot do this in SQL 2005 now or should I be
focused on a networking issue?
Thanks!
ChrisBackups are always done with the privileges of the account SQL Server is
running under. Does this account have access to that share? You never want
to use mapped drives. Use UNC pathing instead.
--
Andrew J. Kelly SQL MVP
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:eRk0J22LGHA.420@.tk2msftngp13.phx.gbl...
> With SQL 2000 I was backing up to a NAS server by mapping a drive share to
> Z. Is there a reason that I cannot do this in SQL 2005 now or should I be
> focused on a networking issue?
> Thanks!
> Chris
>