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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I think you should take a look at this Ms Article section B
0
Scott PletcherSenior 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
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Scott PletcherSenior 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
Scott PletcherSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.