June 12, 2009 at 3:39 am
As a SQL Server 2005 relative newbie, I'm trying to understand the backup and restore views that we get in in this version. Most of our databases on these servers are development or very small databases so I want to get my head straight before we start on the big databases.
I've created the backups and they appear to be running OK (for both simple and full recovery mode). When I come to do a restore of a simple recovery mode database, I can only see one backup for restore. I can change the date to a specific point in time but still only see one backup for restore. This is because it is in simple mode and is overwritten every night. Have I got that right?
When I come to do a restore of a full recovery mode database, I can only see one backup of the database for restore. However I have a number of transaction log backups. If I pick a specific date in the past, the transaction log backup changes to that date. If I restore that transaction log and the database, the database will roll back to that point in time. Have I got that right?
Sorry if this is a bit stupid, but BOL refers to the last or latest backup. We have the scenario where we may need to revert a database back to a couple of months ago. We don't use differential backups and this was quite straight forward in EM
Madame Artois
June 12, 2009 at 5:10 am
S Hodkinson (6/12/2009)
I've created the backups and they appear to be running OK (for both simple and full recovery mode). When I come to do a restore of a simple recovery mode database, I can only see one backup for restore. I can change the date to a specific point in time but still only see one backup for restore. This is because it is in simple mode and is overwritten every night. Have I got that right?
No, if you take a backup of a database in simple recovery mode you can only take and restore full and differential backups. If you want to do a point in time restore you need to take transaction-log backups as well, but that's not posible in simple recovery mode.
When I come to do a restore of a full recovery mode database, I can only see one backup of the database for restore. However I have a number of transaction log backups. If I pick a specific date in the past, the transaction log backup changes to that date. If I restore that transaction log and the database, the database will roll back to that point in time. Have I got that right?
No exactly. If you want to do a point in time restore you have to restore first the last full backup before that point in time and then apply all the necessary tlog backups which have been taken since that full backup. So instead of a rollback it's more like a roll forward.
Sorry if this is a bit stupid, but BOL refers to the last or latest backup. We have the scenario where we may need to revert a database back to a couple of months ago. We don't use differential backups and this was quite straight forward in EM
Basically backup and restore in SQL 2005 work pretty much the same as in 2000. If you want to go to a point months ago, you need to find first the last full backup before that moment. In the GUI it will select the necessary backups for you from the table backupsets in the msdb database, but if you go back a long time, chances are that the backup files are no longer on your disk.
Hope this helps
[font="Verdana"]Markus Bohse[/font]
June 12, 2009 at 6:07 am
Yes. I'm beginning to get a grip of this now. I'll do some tests and verify my processes.
Thanks for your help (but I may be back!!)
Madame Artois
June 12, 2009 at 2:55 pm
I've created the backups and they appear to be running OK (for both simple and full recovery mode). When I come to do a restore of a simple recovery mode database, I can only see one backup for restore. I can change the date to a specific point in time but still only see one backup for restore. This is because it is in simple mode and is overwritten every night. Have I got that right?
--
The sql command you are probably includes the parameter INIT. This will cause the next backup to overwrite the previous backup in the file. If you don't want to overwrite the file, use NOINIT. You can then do a restore headeronly to show you the different backups.
--
June 12, 2009 at 6:21 pm
If you do a Full+Tlog(s) > restore this way > Full+Subsequents Tlog(s) starting from oldest to newest.
If you do Full+Diff+Tlog(s) > restore this way > Full+most Recent Diff+Tlogs AFTER most recent Diff, starting from oldest Tlog to most recent
The advantage of Differential backups is that you avoid the use of Tlog backups for restoring since last Full Backup. Everytime you run a Diff it resets a bit at database level and there is no need to restore any Tlog backup file previous the most recent Differential backup. For instance, if you ran a Full Sunday 12am, Diff backups Mondays and Fridays at 12am and Tlog daily every 4 hours and your database crash Friday 6am, you will have to use the
Sunday's 12am Full+Friday's 12am Differential+Friday's 4am Tlog backup
If database is in Simple recovery model you can not restore in point of time or run Tlog backups. You can restore to a specific point of time if you're using Full recovery model and when doing that, it is possible to run Tlog backups.
If your database is almost read only of you does not change a lot during time, or it is not mission critical, you can safely change to Simple recovery model.
June 30, 2009 at 3:51 am
After doing more tests, I'm still getting confused. I'm doing a full database and a transaction log backup every night. In EM, I can see a database and transaction log and pick a date. In SSMS I see the full database and required transaction logs. So far so good.
I've been doing some testing with a colleague on a true test database. I take a backup then he alters data through his application. I then try to restore the database back to where it was before he altered the data. But it doesn't work!! I still have the data as my colleague amended. Obviously, I'm doing something wrong.
I've looked at backup sets and appending rather than overwriting the media. I don't know if I have something selected incorrectly or I'm not following the correct sequence. Is there where transaction tail backup come in?
Phew!
Madame Artois
June 30, 2009 at 4:34 am
S Hodkinson (6/30/2009)
I've been doing some testing with a colleague on a true test database. I take a backup then he alters data through his application. I then try to restore the database back to where it was before he altered the data. But it doesn't work!! I still have the data as my colleague amended. Obviously, I'm doing something wrong.
Are you specifying STOP AT, or just restoring all the logs?
What are the exact command you're using? If you're using the Management Studio wizards, I would suggest you switch to code and get the feel of exactly what you're doing.
I've looked at backup sets and appending rather than overwriting the media. I don't know if I have something selected incorrectly or I'm not following the correct sequence. Is there where transaction tail backup come in?
No. Transaction log tail backup is the backing up of the last portion of the transction log (the tail of the log) and is done before a restore to get the last transactions.
Personally, my preference is that each backup goes to a separate file that contains the date and time as part of the name. That way I don't have to worry at all about backups getting overwritten/appended, it's easier to get those file to tape/off site backup, and it's easy to see what ones need restoring, because you can go by date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 5:06 am
GilaMonster (6/30/2009)
Personally, my preference is that each backup goes to a separate file that contains the date and time as part of the name. That way I don't have to worry at all about backups getting overwritten/appended, it's easier to get those file to tape/off site backup, and it's easy to see what ones need restoring, because you can go by date.
My problem is that the backup to tape is done by Infrastructure. They keep only three days of flat files (i.e SQL Server backups) which was how we discovered our initial fault. I can't keep a load of backups on the server which is why I was trying to replicate the options and ability that we have on EM
Madame Artois
June 30, 2009 at 5:16 am
S Hodkinson (6/30/2009)
My problem is that the backup to tape is done by Infrastructure. They keep only three days of flat files (i.e SQL Server backups) which was how we discovered our initial fault.
That's not a technical issue. I would speak with your manager or their manager (or both) and explain why 3 days of backups is not sufficient, and see if there's any way they can store more.
If you can't keep backups on the server and they won't keep backups, there's not many options other than buying an external drive or burning to DVD yourself.
We have the scenario where we may need to revert a database back to a couple of months ago. We don't use differential backups and this was quite straight forward in EM
If you need to restore from a couple months back, you need to find the backup file that was taken around that time. The restore's trivial, in the restore dialog browse to that file, select the backup, change the paths as necessary and start the restore.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 5:53 am
You said
Are you specifying STOP AT, or just restoring all the logs?
What are the exact command you're using? If you're using the Management Studio wizards, I would suggest you switch to code and get the feel of exactly what you're doing.
I was using the wizards rather than code. I thought that STOPAT rendered all subsequent transaction logs as invalid. I thought that this meant that, if I've not got the correct time/date (my users can be a bit flaky on when they actually did something!)I've lost the subsequent transaction logs and snookered myself.
Infrastructure tend to be 'total disaster' oriented rather than 'data disaster' oriented
Madame Artois
June 30, 2009 at 6:08 am
S Hodkinson (6/30/2009)
I thought that STOPAT rendered all subsequent transaction logs as invalid. I thought that this meant that, if I've not got the correct time/date (my users can be a bit flaky on when they actually did something!)I've lost the subsequent transaction logs and snookered myself.
If you don't use StopAt then the entire of the log that you're restoring will be restored. If you need to restore part of a log backup, you have to use Stop at.
Eg:
tran log backup at 10am
tran log backup at 11am
At 10:45 a user deletes some critical data.
You can restore just the log backup that ran at 10am, but that'll result in the loss of 45 min of data.
You can restore the entire of the log backup at 11am, but that means that the delete will happen
You can restore both and Specify STOPAT = 10:45 and get back to just before the deletion.
It should be possible (thought I haven't tested) to restore logs with both STOP AT and Standby. If that is valid, you can query the data (standby mode) and see if you've gone too far. If you have though, it's start again from scratch.
Infrastructure tend to be 'total disaster' oriented rather than 'data disaster' oriented
Then speak with your manager. After all it'll be (indirectly) on his head if there's a major problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 7:51 am
I've just run a full database backup and transaction log backup. I've asked my data admin colleague to delete something from his database via the application this afternoon. The routine full database and transaction log backup should happen in the early hours of the morning.
So in the morning the script should run as
Use master
RESTORE DATABASE OIS_TEST
FROM OIS_TEST backup
WITH NO RECOVERY
Use master
RESTORE LOG OIS_TEST
FROM OIS_TEST backup
WITH STOPAT = 'June 30, 2009 14:35 PM', RECOVERY
However when I parsed it I got the following errors
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
I feel I'm nudging forward slowly!
Madame Artois
June 30, 2009 at 8:11 am
Please check Books Online for syntax and keywords.
No recovery should be one word - WITH NORECOVERY. I assume you're restoring from a backup device. If som the name must conform to SQL identifier requirements, ie no spaces or, if there are, the name must be wrapped in []
Example:
RESTORE DATABASE Test
FROM [MyBackupDevice]
WITH NORECOVERY
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 8:15 am
S Hodkinson (6/30/2009)
The routine full database and transaction log backup should happen in the early hours of the morning....
WITH STOPAT = 'June 30, 2009 14:35 PM', RECOVERY
If the tran log backup ran in the early hours of the morning (containing log records up until that time), how are you going to restore to early afternoon with it?
To restore to a point in time, you need a log backup that contains transaction logs over that time period.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 8:25 am
I've got a database and log backup that took place before the deletion and will have a log backup after the deletion. This will run in the early hours of tomorrow morning. Sorry if the reference was a bit confusing; its a 24x7x365 organisation and we have certain understandings
Madame Artois
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply