February 4, 2011 at 12:15 pm
Ok so I have a developement database that I back up every week by right clicking on the database > Tasks > Backup. Backup type is FULL. Backup component is Database. SO I have a Backup set with a file for each week. (Its set to append to the existing backup set)
Recently I ran an update query that I would rather not try and undue. So I want to restore the database to a specific file in the set. So I right click database > Tasks > Restore > Database. Select To 'mydatabase' to a point in time 'Most Recent possible'. Source for restore is set to 'From device' then I find my backup set and pick the file corresponding to the week I want to go back to.
Problem is when I try to get run the restore I get the following error:
Restore failed for server 'blah'
System.data.sqlclient.sqlerror: The tail of the log for the database 'blah' has not been backed up. Use BACKUP LOG WITH NO RECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
What does this mean?? How will it affect my backup set.
February 4, 2011 at 12:29 pm
Ever done a Transaction Log backup? From your description, I'm assuming you're not aware of the necessity. Your Transaction Log has also been growing and growing with no sign of stopping, right?
In any recovery mode but simple, you need to do transaction log backups separate from the main database. A Tail Log is the final log backup you make before taking a database down.
You want to look into BOL for BACKUP TRANSACTION LOG, and also Recovery Mode. This will help you a lot.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 12:30 pm
It's just telling you that the DB you're restoring over is in full recovery and the tail of the log has not been backed up and hence you will lose any transactions since the last log backup if you continue.
If that is not acceptable, take a tail-log backup before continuing (backup log ... with norecovery)
If it's acceptable and you don't care, RESTORE DATABASE ... WITH REPLACE
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
February 4, 2011 at 2:00 pm
GilaMonster (2/4/2011)
If it's acceptable and you don't care, RESTORE DATABASE ... WITH REPLACE
What do you mean by this?
So say I have been backing up the database for the last month every week. I have made changes to the database in week 4. So I want to go back to week 3. Are you saying because I have not done a transaction log backup I cannot go back to that point at all?
What if I do a transaction log backup and then try to restore to my Week 3 backup? Will that work or will backing up the transaction log now cause today to be as far back as I can restore to?
Thanks for the help so far!
February 4, 2011 at 2:08 pm
loki1049 (2/4/2011)
So say I have been backing up the database for the last month every week. I have made changes to the database in week 4. So I want to go back to week 3. Are you saying because I have not done a transaction log backup I cannot go back to that point at all?
Errr, no. You have a backup from the point you want to restore to? If so, you can restore to it.
What if I do a transaction log backup and then try to restore to my Week 3 backup? Will that work or will backing up the transaction log now cause today to be as far back as I can restore to?
If you don't care about the transactions since the last log backup (and seeing as you want to restore to last week, I'm guessing you don't), then RESTORE ... WITH REPLACE. It's only if you care about those last few transactions between the last log backup and now (ones that are not included in any backup at this point) that you need to take a tail-log backup.
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
February 4, 2011 at 2:18 pm
OK, I think I'm getting this figured out. No I don't care about anything thats been done to the database after the point I want to restore to. This is just a development database that I and only I am using. It was given to be as an .mdf and .LDF file a while back which I just attached to a database name I created using sp_attach / sp_detach. So the data is pretty irrelivant, but I want to get back to using the test data I had in week 3.
So I guess my last concern is how do I exactly do the RESTORE with REPLACE syntax? I am currently using the GUI and don't see an option to select that?
I would be just as happy to run a TSQL statement to due this but I don't know how to access individual files from my backup set as it is just a single file... When I use the GUI and select that file using the 'From Device' browser it populates the backup sets for me in the window below.
February 4, 2011 at 2:22 pm
loki1049,
High level here is what is being said.
Right now you are backing up date database using FULL backups only and you have the database set to FULL recovery.
What is happening is that all your transactions are being writen to the .ldf and never being applied to the mdf. A transaction log backup would help create a checkpoint so that those backups could be applied.
If somone could post a good tutorial on this it would be benifitial. I couldn't find one with a quick search. However, this doesn't seem to be your issue at least from what I understand.
In your cause you have a full backup that you want to restore and you don't care what's currently in the database. In that case you just need to check the Overwrite the existing database (WITH REPLACE) option and it will restore your database back to where it was when you backed the system up.
The following command would work also:
RESTORE DATABASE [<DBNAME>] FROM DISK = N'<BackupFile>' WITH FILE = 1, REPLACE, STATS = 10
GO
Make absolutely sure you don't need anything in your database before you restore it. If there is any doubt back it up first. A restore replaces your mdf and ldf and there is now way to get things back once they are overwritten.
I hope this helps.
February 4, 2011 at 2:33 pm
Make absolutely sure you don't need anything in your database before you restore it.
Is this to say that it will just restore the structure of the database? I want to have all of the data in the database that was in there when I was at week 3.
Pretty much the goal is to pretend anything after the week 3 FULL back up is junk. I only added a column here and there and put in some test data. If this is what your response means I have one more question. Does the WITH FILE = N indicate the Nth file in the backup set to restore to?
February 4, 2011 at 2:36 pm
loki1049 (2/4/2011)
Make absolutely sure you don't need anything in your database before you restore it.
Is this to say that it will just restore the structure of the database?
No. It's to say you will overwrite the database that is there with the database contained inside the backup. entirely, 100%, with no going back.
When you're finished the restore the database will look exactly as it was at the time you took the backup and everything added/changed since will be gone.
Does the WITH FILE = N indicate the Nth file in the backup set to restore to?
Yes
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
February 4, 2011 at 2:37 pm
loki1049 (2/4/2011)
So I guess my last concern is how do I exactly do the RESTORE with REPLACE syntax? I am currently using the GUI and don't see an option to select that?
On the options tab there's a checkbox that says 'overwrite existing database' (or something similar). Check it.
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
February 4, 2011 at 2:38 pm
Carlton B Ramsey (2/4/2011)
If somone could post a good tutorial on this it would be benifitial. I couldn't find one with a quick search.
Managing Transaction Logs[/url]
However a development database should probably be in simple recovery model.
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
February 4, 2011 at 2:50 pm
Great it worked. I'm completely new to database backups and recovery so I will look into whats been said. Thanks for all of the advice.
February 5, 2011 at 6:15 am
loki1049 (2/4/2011)
Ok so I have a developement database that I back up every week by right clicking on the database > Tasks > Backup. Backup type is FULL. Backup component is Database. SO I have a Backup set with a file for each week. (Its set to append to the existing backup set)
Why not schedule it and save yourself the hassle ?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply