Okay I had two versions of a database. One was the detached database file (.mdf), the other was a backup file (.bak). For Both were done at the same time, but the Bak file seems to not have the latest updates. Is there anything I can do to figure out what transactions have occured on the one one since the other one? Why would they be different, could it be commit issues? I'm totally confused.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Backup Missing data?
- Thread starter kramers
- Start date
NoCalAdmin
IS-IT--Management
have you checked your transaction logs?
Tom
Tom
NoCalAdmin
IS-IT--Management
how are you backing the databases up? Flat filing?
Tom
Tom
- Thread starter
- #4
Is the DB highly active? If there are several transactions say per second then of course there will be differences in the time it takes you to 1) detach a DB and 2) back it up to disk.
when you say there is no trans log do you mean the DB is in simple recovery mode?
[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]
when you say there is no trans log do you mean the DB is in simple recovery mode?
[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]
- Thread starter
- #6
I was unaware you can have a sql server database without at least one transaction log.
[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]
[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]
mrdenny
Programmer
- May 27, 2002
- 11,595
- 0
- 36
You can't have a SQL Server database with out a transaction log. Simple recovery mode is the closest thing.
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)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
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)
If I'm not mistaken, transactions can still occur against a DB that someone is backing up. They simply wait until the backup is done to fully commit if the backup is accessing the page in question. This could be the reason for your difference in data.
The best scenario, to get an exact copy, is to kill all connections (but talk to the users first so no data is lost), set the DB to Single_User, back it up, then restore the backup to another DB. At this point, all the data should be identical.
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
The best scenario, to get an exact copy, is to kill all connections (but talk to the users first so no data is lost), set the DB to Single_User, back it up, then restore the backup to another DB. At this point, all the data should be identical.
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
mrdenny
Programmer
- May 27, 2002
- 11,595
- 0
- 36
Transactions can most definetly occure while the database is being backed up.
When the backup starts all the data in the database at that second is what is backed up. Everything else is commited as it is written. The only difference is that it's not checkpointed from the log to the database until the backup is complete. This gives you a complete relational set of data.
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)
When the backup starts all the data in the database at that second is what is backed up. Everything else is commited as it is written. The only difference is that it's not checkpointed from the log to the database until the backup is complete. This gives you a complete relational set of data.
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)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
