| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2005-01-07 : 10:40:08
|
I'm trying to restore a SQL server 7 database backup from a year ago as some data was entered then and it isn't clear what that was. When I try it all seems to go well and then right at the end I get a message saying Microsoft SQL-DMO (ODBC SQL State 42000)Cannot apply the differential backup on device <blah, blah> to database 'MyRestore'. Backup or restore operation terminating abnormally The database itself is created but is in a 'restore' status and cannot be accessed. Is this an access rights issue?thankssteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-07 : 12:07:04
|
| Sounds like you are trying to restore a differential rather than a full backup==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-08 : 07:02:14
|
| You can have a look at the type of backup with:RESTORE HEADERONLY FROM DISK = 'x:\MSSQL\BACKUP\MyBcakupfile.BAK'BackupType is one of:1 = Database2 = Transaction Log4 = File5 = Differential Database6 = Differential Fileand possibly some others that my Restore SQL Snippet doesn't know about, but I expect BOL will!Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-08 : 11:47:37
|
| Thanks Guys I'll take a look first thing on Monday and report backsteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-10 : 12:12:03
|
| OK,When I do that I get MyDB backup NULL 1MyDB backup NULL 5MyDB Trans Log NULL 2MyDB Trans Log NULL 2MyDB Trans Log NULL 2MyDB Trans Log NULL 2MyDB Trans Log NULL 2and lots of other stuff. Obviously the first one is a full backup, the second is an incremental and the rest are Transaction log backups but I'm not really sure what it means?If it makes any difference at all there are a number of factors (some of which have just come to light).1) I'm trying to do a restore to a particular point in time (a year ago give or take a few days but ideally just after 15th Jan 2004 (some spurious data has come to light that has happened in the last year but there is no way to tell when)2) All the stuff above is dated from this week which is sort of what I expected (I thought we had a system that took a full backup every sunday night, incremental backups every other night, and transaction log backups every 2 hours during the working day)3) I was trying (I think) to restore from the full backup that was just after the point in time I'm interested in when this error occured. I've tried various combinations but it still won't work as expected.4) It seems that not long ago in my absence someone here decided to 'sort out the backups' and 'forgot to tell me', which means that I'm now not sure entirely what's going on, but I think that the one they have created is seperate from the ones I know about, but if it's not they may have overwritten mine.Could part of the backup be corrupt or perhaps part of the Transaction Log missing? Does anyone understand what might be going on here or have any clues?Many, many thankssteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-10 : 14:19:13
|
| It's important that no one has backed up the tr log (or truncated it) separetly from the files you have.If they have then you are stuffed.What happens when you just restore the full backup?Restore to standby so that you can see what's in the database.Then do the same for the diff and logs one by one.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 17:08:56
|
| I think you need to specify the WITH FILE option in the RESTORE command since it looks like you've got multiple backups inside one file.Tara |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-11 : 04:09:39
|
| Thanks I'll take a look at them and post backsteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-11 : 11:30:21
|
| I'm now getting a little more confused. When I run the same again as Kristen gave I now getMyDB backup NULL 1 MyDB Trans log backup NULL 2But as it's now Tuesday I would expect a full backup (from Sunday), a differential backup from last night and a few transaction log backups from during the day.Am I missing something here?SteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-11 : 12:01:15
|
| I've just discovered two more factors that may be pertinent.1) Our network admins have been 'taking a look' at the SQL backups and its not clear if they have done anything2) Out network admins recommended a full backup be made every night which was put in place in addition to the existing routines. I presume that this will clear the transaction logs which may partly explain my last post.I think I will change my sig to something like 'never look under a rock as you don't know what will crawl out'thanks yet again steveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-11 : 12:42:45
|
| I haven't seen people append backup files to the same file since SQL Server 6.5. Of course it's still available, but it's so much easier to have one backup per file and have the backup datetime in the file name. Anyway, I digress.How are you going to restore last year's data if your backups are for the current year? How old is the oldest backup that you have?Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-11 : 12:56:23
|
| Perhaps your Full backup is set to overwrite the file, but the Diffs and TLogs to Append to it?When you are showing:"MyDB backup NULL 1"I presume you are actually getting lots of extra columns of data? In particular the LSN columns should enable you to decide if you have a complete set of Backup + Diff + TLogs in order to restore to point in time. A FagPacket and Pencil is probably required for this one!Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-11 : 17:57:56
|
| TaraI have backups from around Nov 2000 on (arguably they will save my neck one day - our data is potentially that critical). At that point I knew even less about SQL server than I know now. I think I just set a maintenance plan in action and hoped for the best. I've done occasional restores since then (some using EM) with no problems. It would have been set to append as I was probably being extremely paranoid and knew that the system had no audit trail. I can definitely see the value in what you say but there is a possibility that I will have to go back to any particular point in time which is probably why I set it up as I did.Kristen - yes there is lots of other data but some of it was meaningless to me. What is the LSN and what does it tell me? Is it explained in BOL (he said writing this from home at 11 pm after a glass or two of wine! You could be right about the backups as that has a sort of logic to it (doesn't it?)Many thanks to all who have helped with thissteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-11 : 18:01:04
|
| So you need to run RESTORE HEADERONLY on a file that is around the time that contains this data. Take a look at the lists of backups inside of that result set. Run RESTORE DATABASE on the full backup inside the result set using the WITH FILE option.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-12 : 10:21:33
|
| My understanding is as follows, and quite probably not right:The LSN numbers are kind-of transaction batch-numbers. A Full Backup contains a start LSN, and then just before it finishes it will top-up with any LSNs that have happened whilst the backup was being made.The TLog backups are for a Start/End range of LSNs.So you need to restore a Full backup (and possibly a subsequent, but related, Diff)Then you need to restore a TLog that starts at an EARLIER LSN and finishes at a LATER LSN - i.e. the full backup [or Diff backup] was somewhere in the middle of the range that that TLog backup covered. (You might get lucky and the Full Backup exactly coincides with the LSNs of the TLog backups - dunno if that is possible (maybe the Full Backup makes a transaction as it finishes?!)And then you need to restore TLog backups, thereafter, in strict sequence of LSNs.Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-12 : 10:22:21
|
| My understanding is as follows, and quite probably not right:The LSN numbers are kind-of transaction batch-numbers. A Full Backup contains a start LSN, and then just before it finishes it will top-up with any LSNs that have happened whilst the backup was being made.The TLog backups are for a Start/End range of LSNs.So you need to restore a Full backup (and possibly a subsequent, but related, Diff)Then you need to restore a TLog that starts at an EARLIER LSN and finishes at a LATER LSN - i.e. the full backup [or Diff backup] was somewhere in the middle of the range that that TLog backup covered. (You might get lucky and the Full Backup exactly coincides with the LSNs of the TLog backups - dunno if that is possible (maybe the Full Backup makes a transaction as it finishes?!)And then you need to restore TLog backups, thereafter, in strict sequence of LSNs.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-12 : 12:26:44
|
| So in essence, LSNs are SQL Servers way of keeping track of the order of the backups. So if you try to restore a tlog out of sequence, it knows immediately you can't do it due to the LSNs.Tara |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-12 : 18:28:17
|
| Many thanks Kristen/Tara, that makes a lot of sense. Hopefully I will have some luck with it nowcheerssteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-18 : 10:39:07
|
| Ok, I've been able to get what I wanted from this database - Thanks to all for their assistance. So now I would appreciate suggestions on backup strategy. At the moment we take a full backup every Sunday, an incremental every weekday and a transaction log every hour during the working day. I'm moderately happy that this is sufficient, what I'm not really clear on is how to create these in such a way that the data can be easily restored. Tara suggests (if I've understood) that the full backup could have the date as part of its file name. Presumably I could then append the incrementals to the full one for that week and perhaps similarly append the transaction logs. OR might I be better performing incrementals throughout the day and full backups each night and just ignoring transaction logs (what would be the benefits/down side of this). The final factor to take into account is that of storing all these backups - as suggested I probably need to keep ALL backups!! e.g. in a few years time I may need to look at something that was entered today.Presumably I could physically copy the files elsewhere. I don't suppose there is a way that I can tell if my network admins friends have succesfully backed up a particular file?Any suggestions/comments would be greatly appreciatedMany thankssteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-18 : 12:12:06
|
| Have a look athttp://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.htmlIt will take full diff and log backups and timestanmp the filenames.There's also stuf for restoring with logs and log shipping there too==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-18 : 12:45:38
|
| I don't ever append backups in one file. Each backup regardless of its type gets its own file. And each file gets a timestamp. No, there isn't a way to see if the network admins are backing up your files to tape. Maybe you should request periodic restores to disk and then you should do the restores to SQL Server to make sure the files on tape are completely restorable.Tara |
|
|
|
Next Page
|
Bad Backup?