Database Backup

We need to create a back up at a specific time for a client.
Now they have specified that the timings must be exact. The question I
have is this.
Say the back up takes 20 minutes to perform. Will any transactions
that take place in that 20 mins be logged in the database? Or is the
database locked for those transactions and then added to the database
when the backup has finished?
We can't unfortunately take the server off-line while it is in the
back up process.
Many Thanks in advance for your help.
Mark
Mark,
Go ahead. SQL Server backups are good for all committed transactions at
the time the backup completes. Note: open transactions are not included
in the backup file at the time the backup completes.
The database is not locked during the backup, so you can modify the data
in there and it will still be captured, if it is committed by the time
the backup finishes. There are some restrictions like you can't grow or
shrink the data files during a backup.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark wrote:
> We need to create a back up at a specific time for a client.
> Now they have specified that the timings must be exact. The question I
> have is this.
> Say the back up takes 20 minutes to perform. Will any transactions
> that take place in that 20 mins be logged in the database? Or is the
> database locked for those transactions and then added to the database
> when the backup has finished?
> We can't unfortunately take the server off-line while it is in the
> back up process.
> Many Thanks in advance for your help.
> Mark
|||Hi
The database dump is consistent and point in time correct at the end of the
dump as the dump always copies in the un-truncated commited log transactions.
In effect, the dump process takes the pages off the database, writes them to
the dump, without locking, and once the DB is dumped, takes the transaction
log and adds it's contents to the same file.
So, a restore is a restore of the Db and a transaction log replay.
The dump is a copy of the database at 10:20 and not at 10:00 if the dump
takes 20 minutes.
Regards
Mike
"Mark" wrote:

> We need to create a back up at a specific time for a client.
> Now they have specified that the timings must be exact. The question I
> have is this.
> Say the back up takes 20 minutes to perform. Will any transactions
> that take place in that 20 mins be logged in the database? Or is the
> database locked for those transactions and then added to the database
> when the backup has finished?
> We can't unfortunately take the server off-line while it is in the
> back up process.
> Many Thanks in advance for your help.
> Mark
>
|||In addition tot he other posts (in case it isn't obvious already):
If they have a need to have a backup from a certain point in time, say 2 pm:
Do the db backup at a some time (perhaps 2pm). After the db backup, do a log backup. You can now
restore the db backup and then the log backup in which you stop at 2 pm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <mroffey@.hotmail.com> wrote in message
news:1da0fab5.0410210430.6c03d311@.posting.google.c om...
> We need to create a back up at a specific time for a client.
> Now they have specified that the timings must be exact. The question I
> have is this.
> Say the back up takes 20 minutes to perform. Will any transactions
> that take place in that 20 mins be logged in the database? Or is the
> database locked for those transactions and then added to the database
> when the backup has finished?
> We can't unfortunately take the server off-line while it is in the
> back up process.
> Many Thanks in advance for your help.
> Mark
|||Does this mean that for the duration of the backup, committed data is never
flushed to the data file(s)? Thus, the longer the backup, the larger the
trx log file will grow, regardless of the recovery model?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!