Restore SQL database and (many) transaction logs

Hi all,

I have a new customer who need some help restoring a SQL database - and I am not good at that.
It seems like the backup plan has not functioned as planned - it looks like the full backup has not run.

So what I have is 1 large file (see picture 1) and 7.997 other .bak files (see picture 2).
Picture 1Picture 2

I have managed to restore the large file with a "RESTORE WITH NORECOVERY" so I can put in transaction logs afterwards. I am using the Microsoft SQL Server Management Studio GUI.
But I can only select 1 file at a time when trying to restore these transactionslogs. I think there must be a smarter way to restore to the point I want (let's say March the 11th. 2018).

I hope I have explained myself good enough - or else - please ask.

Can anybody help? Please keep in mind that I'm not use to SQL Server at all :-)

Best regards
Henrik
LVL 4
Henrik Rosager PedersenIT ManagerAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The file name to restore is coming from the table, not hard-coded into the command:

SELECT
    'RESTORE LOG DATABASE_SCM FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY ' +
    '/* Backup_Finish_Date = ' + CONVERT(varchar(30), bs.backup_finish_date, 120) + ' */'
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = 'DATABASE_SCM' AND bs.type = 'L'
    AND bs.backup_finish_date > '20171216 09:30'

I also added on critical WHERE condition.

You will get a command for every log file that needs restored.  Yes, that's 7.997+/- commands.  
(That's why you shouldn't take only log backups to cover that long a period of time :-) :-) [as I'm sure you realize now].)

Again. the only way to use only the log files to recover is to apply every log file, in order, from the full backup through the time you want to recover to.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
I think you should take a look at this Ms Article section B
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
No matter how many files, the restore can still theoretically work if you have all the lob backups taken since the full backup and they can all be applied successfully.  They must be applied in order.  It doesn't look like the log history has been purged from msdb, so you should have a chance.

First rule: Do not use the GUI to restore (esp. from log files)!
It's flaky, quirky and can be error prone.

If you need to stop at a specific time before the end of the last log, let me know BEFORE you run the restores, because you'll want to add a "STOPAT" to the command.

Since you're already restored the full backup, we just need to restore the logs.  The command below should generate all the restore commands you need, in order.  Try the first one.  If it works, you can run the rest.  

SELECT
    'RESTORE LOG DATABASE_SCM FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY ' +
    '/* Backup_Finish_Date = ' + CONVERT(varchar(30), bs.backup_finish_date, 120) + ' */'
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = 'DATABASE_SCM' AND bs.type = 'L'

After all the log restores have completed successfully, you just need to do one final recovery of the db itself:

RESTORE DATABASE DATABASE_SCM WITH RECOVERY;

If all this somehow works correctly, naturally be sure to immediately take a full back up of the recovered db!
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Henrik Rosager PedersenIT ManagerAuthor Commented:
Thanks so far!

I have tried to follow the steps in John's link - but I can't get it to work.

@Scott - from where do I run this command? (yes - I really do not know anything about SQL). I need to stop the restore at March the 11th. at 14.00.

Thanks again so far.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
I'm assuming you're using SSMS to get to the restore panel you're seeing.

From within SSMS, in "Object Explorer" on the left side of the screen, expand the instance of SQL that this db is in.

Click on "Databases" to show all the dbs.

Click on the database name.  It should highlight.

Click on the "New Query" button.  That should open a query window, and the db name should appear in the drop-down box.

Copy the query I wrote into that window and run it.  ONE MINOR CHANGE: BEFORE running the command, it would be a good idea to add a semi-colon after the "WITH NORECOVERY", like this "WITH NORECOVERY;".  That will help SQL parse the statements.

The result should be a list of RESTORE LOG commands.

Click "New Query" again, and copy the FIRST of the RESTORE LOG commands into that window and try to run it.  If it runs OK, you can copy the rest of the commands (but not the first one again, just to be clear) and try to run them all.  It will take a while.  If it works, one final recovery command and the db is good.
0
 
Henrik Rosager PedersenIT ManagerAuthor Commented:
@Scott

Can I do this on another SQL server (I would like to do it in a test environment)?

So far I needed to change the code a bit - or else I got some errors. Now it looks like this:

SELECT 
    'RESTORE LOG DATABASE_SCM FROM DISK = "C:\Admin\restore\SCM\DATABASE_SCM.bak" WITH NORECOVERY;'
    '/* Backup_Finish_Date = CONVERT(varchar(30), bs.backup_finish_date, 120) */'
    FROM msdb.dbo.backupset bs
        INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
    WHERE bs.database_name = 'DATABASE_SCM' AND bs.type = 'L'

Open in new window


This is the result I get:

Result
If I  cut the 2 last lines of your code - so it looks like this:

SELECT 
    'RESTORE LOG DATABASE_SCM FROM DISK = "C:\Admin\restore\SCM\DATABASE_SCM.bak" WITH NORECOVERY;'
    '/* Backup_Finish_Date = CONVERT(varchar(30), bs.backup_finish_date, 120) */'
    FROM msdb.dbo.backupset bs

Open in new window


I get a result with MANY lines looking like this:

New result
It might not be right since I', doing this on another server than the original?
0
 
Henrik Rosager PedersenIT ManagerAuthor Commented:
I did not get it to work as I wanted - but I am sure Scotts guides are the correct solution :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.