Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2010-12-31 : 03:14:44
|
| Hi,I have done backup and restores on SQL server in the past and set up maintainance tasks. I never was in a situation where there was a crash and we had to go back to a backup. Now, I am learning SQL server in detail, I am getting these question. I I would appreciate any answers to my questions.1. I took a full backup of a Database (which has Full Recordy model) which has a simple (customers) table. Size of the .bak file - 162,040 KB; Then I added one more record to the table (one more customer); Then I took a transactinal backup. .trn file's size is - 230,939 KB; I was under the impression that the transactional log backup with backup the additional transactions happend since the last full backup? is this wrong? Is the reason of the size is, transactional log backup has the full backup data + all the transaction happened since the creation of the database?2. After this first trn back, i did another transactional log back up. Size of the .trn is 162,040 KB (exact same size as the full backup). Does this mean the following?Since I did a .trn backup, it wiped off all the transactional logs in the DB. Since there were not more transactions before the second .trn backup, the backup is same in size as the full backup?Am i right on this one?3. If the transactional logs are going to include the last Full backup, why do we do full backups at all?(I see that a lot of DBAs do something like this - 1 Full backup at 12.00 am, and in a .trn backup every 4 hours or so?)4. Now I have 3 backups in the excercise I did above. I was able to restore the full backup. How can I restore the first transactional backup and see if I can get the additional row i inserted?5. Imagine I have the follwing maintainance tasks - 1 Full backup at 12.00 am, and in a .trn backup every 4 hours.Database crashes at 6.00 am. How much data can I save from what I got?I am thinking I can restore from -the full backup is taken at 12.00 am.and - the transactional backup at 4.00 am.also teh databse should keep the transactional logs until 6.00 am. correct?So, the only data loss would be 1 or 2 transactions that could not be completed because of the crash.. right?How do I restore in this scenario?Would I just not worry about teh backups and just attach a new database using the .mdf and ldf files i got?Thank you in advance for your time...Regards,Shiyam |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-31 : 04:52:01
|
| What was the exact command you ran to take the log backups?Got any long-running open transactions?--Gail ShawSQL Server MVP |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-31 : 05:50:46
|
| A log backup holds all the transactions since the previous log backup ish (not affected by full backups)log backups don't contain the full backup but a full backup will contain some of the log backup for recovery purposes.You might be able to backup the log after a crash and restore some of that too.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-01-04 : 12:05:13
|
| http://connectsql.blogspot.com/2011/01/sql-server-point-in-time-database.html |
|
|
|
|
|
|
|
|
Backup Question - Mainly transactional Log