November 17, 2016 at 2:35 am
Good day all,
I am trying to work a disaster recovery plan. I want to restore a database to a point in time. I had the scripts to do that in 2012/2013 but I lost it. Now I want to get everything up and running again and save my plan somewhere where I cannot lose it again.
I did a lot of Google-ing and came on this script which I change to suit my needs (e.g. Database name, backup locations etc.) but something is amiss and I cannot get it right.
The original code:
/* Example of restoring a to the point of failure */
-- Step 1: Create a tail-log backup by using WITH NORECOVERY.
BACKUP LOG AdventureWorks2012
TO DISK = 'C:\AdventureWorks2012_Log.bck'
WITH NORECOVERY;
GO
-- Step 2: Restore the full database backup.
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'C:\AdventureWorks2012_Data.bck'
WITH NORECOVERY;
GO
-- Step 3: Restore the first transaction log backup.
RESTORE LOG AdventureWorks2012
FROM DISK = 'C:\AdventureWorks2012_Log.bck'
WITH NORECOVERY;
GO
-- Step 4: Restore the tail-log backup.
RESTORE LOG AdventureWorks2012
FROM DISK = 'C:\AdventureWorks2012_Log.bck'
WITH NORECOVERY;
GO
-- Step 5: Recover the database.
RESTORE DATABASE AdventureWorks2012
WITH RECOVERY;
GO
Now, these steps I first followed in SSMS using the gui but my database was not restored to the time of the last log backup. My question is do I have to restore each and every log backup since my last full backup? I do not remember doing that in 2012/2013 when I read that article. But then, I do not remember much of that.
If my database completely failed and I now have to create a new database with my restore, I know how to do that with a full backup but to restore it to a point in time I don't know. Please help?!
:ermm::ermm::ermm::ermm::ermm:
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
November 17, 2016 at 2:46 am
Manie
Yes, you do. Or every log since the last differential, if you have one. Maybe last time you were dealing with differential backups, not log backups? A differential backup is a backup of all the changes since the last full backup, so you only need to restore the last one of those.
John
November 17, 2016 at 3:01 am
I've noticed as well that your log files seems to all have the same name. If you are using log back up, each one will need to have a different name, as otherwise you'll overwrite the previous log file, and thus render them useless.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 17, 2016 at 3:01 am
John Mitchell-245523 (11/17/2016)
ManieYes, you do. Or every log since the last differential, if you have one. Maybe last time you were dealing with differential backups, not log backups? A differential backup is a backup of all the changes since the last full backup, so you only need to restore the last one of those.
John
Thank you John for the quick answer and no, I have never made a differential backup before. I need to add that to my repertoire of things to learn.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
November 17, 2016 at 3:04 am
Thom A (11/17/2016)
I've noticed as well that your log files seems to all have the same name. If you are using log back up, each one will need to have a different name, as otherwise you'll overwrite the previous log file, and thus render them useless.
That was just a script I copied from the internet. I have changed that script for my needs. Yes, I also saw that but it is just an example.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
November 17, 2016 at 3:07 am
John Mitchell-245523 (11/17/2016)
ManieYes, you do. Or every log since the last differential, if you have one. Maybe last time you were dealing with differential backups, not log backups? A differential backup is a backup of all the changes since the last full backup, so you only need to restore the last one of those.
John
Yes, good spot. Although if the backups were made with NOINIT, the previous backups wouldn't be overwritten, and the RESTORE statement would need to include an extra parameter to specify which backup set to restore from the file. I don't recommend that approach (backing up with NOINIT) - it's much cleaner to use a separate file for each backup.
John
November 17, 2016 at 3:20 am
manie (11/17/2016)
Thom A (11/17/2016)
I've noticed as well that your log files seems to all have the same name. If you are using log back up, each one will need to have a different name, as otherwise you'll overwrite the previous log file, and thus render them useless.That was just a script I copied from the internet. I have changed that script for my needs. Yes, I also saw that but it is just an example.
Good to hear. It can be scary how many people take a script from the internet and read it as "gospel". Then suddenly rant and rave when it all falls over >_<
I don't know what other people's solution are, but if you're looking to automate the script to restore the database in a disaster recovery scenario, I personally use Powershell to create the SQL statement. Using SSMS isn't "bad", but if you are in a situation where the disaster has occurred, I personally feel using that GUI is an awful solution when you have the upper levels of Management leaning over your shoulder asking you to "WORK FASTER!!!" 😛
There are quite a few options open to you, but I suggest looking into the best way to automate your restore process as much as possible now, so that you have damage limit should the worst occur and have your business up and running again as soon as possible. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply