How do I perform restore of DB which does not exist?

Question

Post reply

How do I perform restore of DB which does not exist?

artisticcheese

Right there with Babe

Points: 751
More actions
March 9, 2017 at 6:45 pm

#328188

Hello,
I have full backup of DB and transcation log backups. I select BAK file and transcation log backup in SMSS, put a new database name. As you can see there are 3 files in backup, one full and 2 transaction logs, but in restore plan only transaction logs are present for some reason and attempt to restore fails with error below.
What am I doing wrong?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

matak SSCrazy Points: 2674 More actions · Answer 1

You could always just create the database with the same name and restore onto that.

artisticcheese Right there with Babe Points: 751 More actions · Answer 2

This does not help, it just somehow can not see full backup when trying to restore log backups.

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 3

Hi,
I just can see T-log backups in your screenshot. You can name your backupf-file like you want. A file named *.bak can contanin only T-log backups.

View the content of your backup files with this script:

RESTORE HEADERONLY FROM DISK = 'Path_to_backup_file\backup_file.bak' GO

And show us the result.
Best regards,
Andreas

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Find the full backup, restore just that with the NORECOVERY option, then restore your log backups. You're getting an error because all you're trying to restore at the moment are log backups.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

And please, please - take half an hour to learn the T-SQL syntax for BACKUP and RESTORE. I guess that with the GUI, it fails and then you have to start again. With a RESTORE statement, you know exactly what you're getting, and if it fails, all you have to do is tweak it and press Execute.

John

artisticcheese Right there with Babe Points: 751 More actions · Answer 6

GilaMonster - Friday, March 10, 2017 1:30 AM
Find the full backup, restore just that with the NORECOVERY option, then restore your log backups. You're getting an error because all you're trying to restore at the moment are log backups.

I have about 200 transaction log backups to restore and UI does not allow to restore more then one at a time. I did select BAK file to restore, just transaction log backups are being shown in UI for some reason. Check file list of the files which contain in backup set, you see both bak and trn files there. It's just after I select all of them and add to UI only transcation log backups are shown and not full backup.

Also BAK file does in fact contain full backup inside it

BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BackupStartDate BackupFinishDate SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags BindingID RecoveryForkID Collation FamilyGUID HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID ForkPointLSN RecoveryModel DifferentialBaseLSN DifferentialBaseGUID BackupTypeDescription BackupSetGUID CompressedBackupSize Containment
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------------------- ---------- -------- ---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------- ----------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------------------- ----------------------- --------- -------- --------------- ---------------------- ------------------ ---------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------ ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ ----------------- ---------- ---------- ------------ ------------------ --------- -------------- --------------------- -------------- ---------- ------------------------------------ --------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ -------------------- -----------
siena_backup_2017_03_07_030002_5542988 NULL 1 NULL 1 1 2 NT SERVICE\SQLSERVERAGENT DB-PROD2 siena 782 2016-08-03 01:23:50.000 53319479296 183277000008153800194 183277000008162900001 183277000008153800194 183277000008023000045 2017-03-07 04:48:31.000 2017-03-07 05:03:37.000 0 0 1033 196609 100 4608 12 0 4213 DB-PROD2 512 BFE6CCEC-B661-4FD1-B1E7-28C76BA26DBF 39725676-206F-4DC9-83E5-B7B86524366C Latin1_General_CI_AS 96EBD2AD-32A9-4ED6-BF60-C03509A47A1B 0 0 0 0 0 0 0 0 0 0 39725676-206F-4DC9-83E5-B7B86524366C NULL FULL NULL NULL Database 2C9129FD-3842-4B80-AFC8-DBCC6D27B88A 27099401620 0
(1 row(s) affected)

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 7

I did select BAK file to restore, just transaction log backups are being shown in UI for some reason

What happens when you remove the .trn files and try restoring just the .bak file with norecovery?

artisticcheese Right there with Babe Points: 751 More actions · Answer 8

Beatrix Kiddo - Friday, March 10, 2017 7:01 AM
I did select BAK file to restore, just transaction log backups are being shown in UI for some reason
What happens when you remove the .trn files and try restoring just the .bak file with norecovery?

Restoring individual BAK file works fine, also restoring individual TRN files on RECOVERING DB works fine too. Issue is that I have one BAK file and about 100 TRN files and restoring this way through UI will take forever. I understand I can script it etc but I want to use UI. So in UI as soon as I add any TRN files BAK file being removed from recovery plan list. I read through research that it might happen if log backup was take during Full backup but did not confirm if it's the case.

Perry Whittle SSC Guru Points: 233854 More actions · Answer 9

artisticcheese - Friday, March 10, 2017 7:53 AM
Beatrix Kiddo - Friday, March 10, 2017 7:01 AM
I did select BAK file to restore, just transaction log backups are being shown in UI for some reason
What happens when you remove the .trn files and try restoring just the .bak file with norecovery?
Restoring individual BAK file works fine, also restoring individual TRN files on RECOVERING DB works fine too. Issue is that I have one BAK file and about 100 TRN files and restoring this way through UI will take forever. I understand I can script it etc but I want to use UI. So in UI as soon as I add any TRN files BAK file being removed from recovery plan list. I read through research that it might happen if log backup was take during Full backup but did not confirm if it's the case.

Restore the .BAK through UI with no recovery.
Then once restored and in restoring state select restore tlog and select all log backups in the UI.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

artisticcheese Right there with Babe Points: 751 More actions · Answer 10

Well this work but the problem is that UI does not allow you to restore more then one TRN file at a time and I make full backup every 4 days and hourly log backups so to restore for last 3 days it will take forever.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

If you're right, that's another good reason to use scripts instead of the GUI.

John

SQLBill SSC Guru Points: 51440 More actions · Answer 12

artisticcheese - Friday, March 10, 2017 9:31 AM
Well this work but the problem is that UI does not allow you to restore more then one TRN file at a time and I make full backup every 4 days and hourly log backups so to restore for last 3 days it will take forever.

While this won't help you now, I would suggest you change your backup methods. You take a full backup every four days and tlog backups hourly. Change that to a Full backup every four days, a differential every day, and tlogs every hour. If you had done that, you would only have to restore the full back, 3 differential backups, and then just those tlog backups made after the last differential.

Example: Full backup made Sunday evening. Need to restore Thursday morning. You would restore the full backup, the differentials for Monday, Tuesday, and Wednesday nights, and lastly, the tlog backups that were made following Wednesday's differential backup. Much easier.

-SQLBill

artisticcheese Right there with Babe Points: 751 More actions · Answer 13

SQLBill - Friday, March 10, 2017 12:09 PM
artisticcheese - Friday, March 10, 2017 9:31 AM
Well this work but the problem is that UI does not allow you to restore more then one TRN file at a time and I make full backup every 4 days and hourly log backups so to restore for last 3 days it will take forever.
While this won't help you now, I would suggest you change your backup methods. You take a full backup every four days and tlog backups hourly. Change that to a Full backup every four days, a differential every day, and tlogs every hour. If you had done that, you would only have to restore the full back, 3 differential backups, and then just those tlog backups made after the last differential.
Example: Full backup made Sunday evening. Need to restore Thursday morning. You would restore the full backup, the differentials for Monday, Tuesday, and Wednesday nights, and lastly, the tlog backups that were made following Wednesday's differential backup. Much easier.
-SQLBill

Yes I would usually do something like that but problem is that volume backup is being done by third party service which breaks restore chain and diff backups stop working

SQLBill SSC Guru Points: 51440 More actions · Answer 14

artisticcheese - Friday, March 10, 2017 12:13 PM
SQLBill - Friday, March 10, 2017 12:09 PM
artisticcheese - Friday, March 10, 2017 9:31 AM
Well this work but the problem is that UI does not allow you to restore more then one TRN file at a time and I make full backup every 4 days and hourly log backups so to restore for last 3 days it will take forever.
While this won't help you now, I would suggest you change your backup methods. You take a full backup every four days and tlog backups hourly. Change that to a Full backup every four days, a differential every day, and tlogs every hour. If you had done that, you would only have to restore the full back, 3 differential backups, and then just those tlog backups made after the last differential.
Example: Full backup made Sunday evening. Need to restore Thursday morning. You would restore the full backup, the differentials for Monday, Tuesday, and Wednesday nights, and lastly, the tlog backups that were made following Wednesday's differential backup. Much easier.
-SQLBill
Yes I would usually do something like that but problem is that volume backup is being done by third party service which breaks restore chain and diff backups stop working

Then if the Diff chain would be broken, so would the tlog chain.

-SQLBill