| Author |
Topic |
|
raylward102
Starting Member
4 Posts |
 Posted - 2010-05-05 : 12:13:43
|
| I'm new to using SQL and was wondering if I preformed my databse restore correctly. I first kicked everyone out of the database. From the SQL management studio for sql server 2005 standard; I right clicked on the databse and selected backup. When the backup completed; our staff embarked on a training exercise on learning how to delete orders from history with $0 balances from an accpac specialist, who turned out; not to be so special after all. Apparently the DB was all messed up and needed restoring which leads me to the restoring part of my story. Anyways; not having ever performed a db restore; I got on the sql server manage studio; right clicked on the DB and selected restore. I selected device (path to backup created earlier in the day). When I hit apply; an error messagae came up about the transaction log. I wasn't sure about these logs; but decided to choose the overwrite transaction log option and was then able to run the restore. After doing so; I was informed that all was fixed and looking good. The next day however; some problems cropped up that may or may not be related. Which brings me to the whole "overwriting of the transaction log option". Does it sound like I made the right moves? |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-05 : 14:13:12
|
| You should be Ok. The reason SQL Server asks you to backup the transaction log is so you can roll the log forward after the restore. It's not necessary if you want to restore the database back to the point it was when the backup was taken.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-05 : 14:15:42
|
| "to the point it was when the backup was taken"Pedantic point, but I think that should be "to the point it was when the backup finished being taken" |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-05 : 14:40:13
|
quote:
Originally posted by Kristen
"to the point it was when the backup was taken"Pedantic point, but I think that should be "to the point it was when the backup finished being taken"
From the OP:I first kicked everyone out of the database.Although, I suppose it's possible that someone has reconnected to the database and made an update during the backup. So you are technically correct (which is the best kind of correct).------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-05 : 14:47:55
|
quote:
Originally posted by raylward102
When I hit apply; an error messagae came up about the transaction log. I wasn't sure about these logs; but decided to choose the overwrite transaction log option and was then able to run the restore. After doing so; I was informed that all was fixed and looking good.
I am not clear on what error you got. There is no option to overwrite the transaction log. Do instead mean that you selected the option to overwrite the database? If that's the case, then you did the right thing. This issue should not be relevant to rolling the log forward.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-05 : 15:26:00
|
" which is the best kind of correct"I think so  ... but like I said - its a "pedantic point" only, but I did mention it because I think it may be unexpected - in particular because SQL7 (or was it SQL200?) Backups were as-of the START of the backup and that then changed to being as-of the END of the backup.At the time I was gutted because I thought the old way was oh-so-cool and the new way was brilliantly-conceived, but utterly-utterly boring and UN-cool by comparison  I'll explain the difference if you insist ... |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-05 : 15:52:34
|
quote:
Originally posted by KristenI'll explain the difference if you insist ...
Go on, you know you want to. :)------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
raylward102
Starting Member
4 Posts |
Posted - 2010-05-05 : 19:20:56
|
| Looks like all is well. The gal's just didn't understand what they were talking about. Anyways; it seemed as though the restore went perfectly. The error message for the transaction log overwrie was the only thing that made me nervous. Thanks for all of your inputs! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-06 : 04:30:34
|
quote:
Originally posted by KristenBackups were as-of the START of the backup and that then changed to being as-of the END of the backup.
Well, um, mostly.  --Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-06 : 15:18:53
|
| OK, I'm stumped on that one. And I'd appreciate an example - if it doesn't involve you having to write a whole Thesis!" Go on, you know you want to"At the risk of Gail shooting my ageing-memory-recollection full of holes! I seem to remember it went something like this:SQL 2000: SQL locks all pages in the database, and starts writing to Backup File starting at Page 1, releasing the page-locks as it goes. Meantime it checks to see if anyone is waiting on a lock for other pages, and if they are it backs up that page next, out of order, and releases the lock on that page. I thought that was a well-cool solution (a bit like how excited I got when learning about the elegance of the Boyer-Moore search algorithm). Backup is as-per the start time of the backup.SQL 2005: Backup the database pages without locks, and then backup the relevant portion of the TLog file - i.e. which includes all pages changed during the period of the backup, thus meaning that the backup can run without any page locks, but the restore-point of the backup is the finish-point, rather than the start-point. For some reason I find this less "elegant" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-06 : 17:34:53
|
| Backup did not take locks in SQL 2000, or apparently 7 or 6.5. Maybe in a much earlier version (6 or 4.2). Backups have been end of backup since at least 2000, probably SQL 7. If they ever were start of, we're talking much, much earlier version (6, 4.2 era)http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx (you'll have to copy-paste link. Not linking properly)As for the 'state as of the end of the backup', kinda, but you're ignoring what happens to transactions still executing when the backup completes.To give an eg (timeline)Time 1: Transaction A startsTime 2: Backup startsTime 3: Transaction B starts.. fair bit of time passes ..Time 4: Transaction C startsTime 5: Transaction C commitsTime 6: Backup completesTime 7: Transaction A commitsTime 8: Transaction B commitsWhat's the state of those 3 transactions when the backup is restored?Transaction log does not contain pages changed during the period of the backup (transaction log never contains database pages), it contains records of changes so that they can be rolled forward or backward as necessary to return the database to a consistent state.--Gail ShawSQL Server MVP |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2010-05-06 : 17:48:35
|
| Well Gail, that's not quite true (you piqued my interest by asking me all those questions on Twitter a minute ago) - the log sometimes does contain entire images of pages if the operation and log record call for it, but not for stuff you're going to be doing day-to-day.Backups do not take locks period.The restore point is transactionally-consistent at the time the data-reading portion of the backup finished, not the time the backup overall finished. The log-reading portion of the backup could take a very long time depending on how far much transction-log must be read.I'll have to check how far back the page-locking backup algorithm was removed (if it ever did what you say).ThanksPaul S. Randal,Managing Director, SQLskills.com Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandalSQL MVP, Microsoft RD, Contributing Editor of TechNet MagazineAuthor of SQL 2005 DBCC CHECKDB/repair code |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-06 : 17:54:09
|
quote:
Originally posted by paulrandal
Well Gail, that's not quite true (you piqued my interest by asking me all those questions on Twitter a minute ago) - the log sometimes does contain entire images of pages if the operation and log record call for it, but not for stuff you're going to be doing day-to-day.
You would catch something I edited in later. I was thinking distinction between pages in log and pages in log backup in bulk-logged recovery when I added that.Care to elaborate on what does require entire pages (I assume complete with header) to go into the log?--Gail ShawSQL Server MVP |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2010-05-07 : 00:05:22
|
| Nope - I wasn't thinking about bulk-logged operations causing log backups to pull in entire extents - I was thinking about full-page formats of allocation bitmaps under various circumstances.ThanksPaul S. Randal,CEO, SQLskills.com Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandalSQL MVP, Microsoft RD, Contributing Editor of TechNet and SQL Server MagazinesAuthor of SQL 2005 DBCC CHECKDB/repair code |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-07 : 06:29:37
|
| "If they ever were start of, we're talking much, much earlier version (6, 4.2 era)"I was around way-back then, so might have been then, but otherwise Darn It! ... either way I think its a cool idea (but would scale horribly ...)Yeah, I accept that "end of backup" can't be taken at face-value as "the moment the backup finished".Some interesting I-never-knew-that stuff on Paul's Myth-Buster page, thanks for the link. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-07 : 06:57:09
|
quote:
Originally posted by paulrandal
Nope - I wasn't thinking about bulk-logged operations causing log backups to pull in entire extents - I was thinking about full-page formats of allocation bitmaps under various circumstances.
Oh, so GAM, IAM, etc, not data pages? Any hints as to any of the 'various circumstances'?--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-07 : 06:59:39
|
quote:
Originally posted by Kristen
"[i]Some interesting I-never-knew-that stuff on Paul's Myth-Buster page, thanks for the link.
There are 29 other mythbusters articles on his blog (one for each day in April) All cool and interesting.--Gail ShawSQL Server MVP |
|
|
|
|
databse restore