Will the backup file pick up transactions that occur...

[SteveMe]

after the backup kicked off? Please advise. Thanks

 

[mrdenny]

No. The backup only picks up transactions that have been commited before the full backup. To get transactions that happen after the full backup starts would require a diff or t/log backup.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SteveMe]

Denny thanks... so while the backup takes place. All new transactions go into the transaction log file until the backup completes. Once the back up completes the transaction in the log file are then committed to the db. Am I right, wrong, partially or way off base? haha Please advise

 

[mrdenny]

That's basically the way it works. I don't know the inside details but for the database tables to all be in sync when you restore SQL stops writting to the file while the backups runs. I would assume that everything is kept in the transaction log until the backup is complete. At which point the transaction log is checkpointed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SteveMe]

Denny I really appreciate the input. If after the backup completes and the log file is 30gb in simple recovery mode. Transactions more or less have not been committed to the DB. They are in a waiting mode. If staff where to do a dbcc shrinkfile. Would those transactions be lost or would they be committed and then shrunk or else

 

[mrdenny]

The log file won't be shrunk beyond the last file block until all transactions have been checkpointed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com