Missing filenames/locations for full backups in the restore dialogue

Question

Missing filenames/locations for full backups in the restore dialogue

kangarolf

Hall of Fame

Points: 3432
More actions
June 22, 2016 at 6:30 am

#327658

Hi all,
I have created maintenance plans and scheduled them to backup the transaction log every hour and make full backups everyday.
When I got to restore a database (testing the restore out) over a blank database all the the transaction logs are there but the full backup files appear but with no filename/location so are not restorable.
If I run the maintenance plans manually its fine and if I execute the scheduled job its also fine.
So firstly how do I get those filenames back in and secondly whats going on =)
Thanks
Rolf

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

kangarolf (6/22/2016)
Hi all,
I have created maintenance plans and scheduled them to backup the transaction log every hour and make full backups everyday.
When I got to restore a database (testing the restore out) over a blank database all the the transaction logs are there but the full backup files appear but with no filename/location so are not restorable.
If I run the maintenance plans manually its fine and if I execute the scheduled job its also fine.
So firstly how do I get those filenames back in and secondly whats going on =)
Thanks
Rolf

Quick suggestion, use Ola Hallengren's maintenance scripts[/url] instead of the maintenance plans, much better option!

😎

kangarolf Hall of Fame Points: 3432 More actions · Answer 2

Thank you I will take a look...I still need to correct these existing backups though.

Anyone have any ideas how I can do that..? The file seems ok its just wherever it is keeping a record of where the backups are located seems to be busted.

Must say on balance I find the management console in 2012 annoying over the old EM. Its great for some things but its just plain weird in other ways...being shoe horned into a .NET app I guess hasnt helped...is it better in 2014..?

kangarolf Hall of Fame Points: 3432 More actions · Answer 3

Can anyone help with retrieving the old backups..?

Any of the full backups done via the maintenance plan and a scheduled job have no name/location so I cannot restore them.

Thanks

Rolf

x SSC-Insane Points: 23660 More actions · Answer 4

do you actually know where the backup files are?

I'm wondering if the server / maintenance plan process had permissions to do the backups, but when you browse them, you are doing so with different permissions and cannot select or browse the source directory? I found out that some operations in SSMS run under the windows user account that's running the desktop that has SSMS (lol does that make any sense at all?)

Specifically in my case, in setting up availability groups, we had to grant the desktop user permissions to the directories in addition to the sql server service accounts. ie., whatever account runs the maintenance jobs has access, but whoever is manually browsing to restore does not.

Just a wild guess on my part 🙂

kangarolf Hall of Fame Points: 3432 More actions · Answer 5

Thanks for the reply.

I can restore them manually from ssms and tsql.

If I run the job or the plan from smss they have a filename in the ssms restore dialogue. When the job runs automatically they don't have a filename in ssms, but the backup file is there and accessible and restorable manually.

When I script out the restore task from ssms, in place of the filename is a long GUID. So it looks like the job when run via sqlaerver agent is not storing the filename. Weird.

Rolf

x SSC-Insane Points: 23660 More actions · Answer 6

ok maybe the long looking string is actually a backup "device?"

https://msdn.microsoft.com/en-us/library/ms178018.aspx

See if that long string is listed in table sys.backup_devices maybe??? Just tossing out ideas!

Michael L John One Orange Chip Points: 27038 More actions · Answer 7

Do you have a third part backup running in addition to the native SQL backups?

There's where the weird filenames are coming from.

Try running this query and see if it shows multiple full backups

SELECT

F.physical_device_name,

S.backup_finish_date,

S.database_name,

S.type

FROM msdb.dbo.backupmediafamily F

INNER JOIN msdb.dbo.backupset S ON F.media_set_id = S.media_set_id

WHERE S.database_name = 'YourDatabase'

ORDER BY S.backup_finish_date DESC

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

kangarolf Hall of Fame Points: 3432 More actions · Answer 8

Nope no other backup happening...unless you count the host machine backing up the Hyper v guest.

All the transaction logs are fine, with filenames, but that is a separate job.

Rolf

kangarolf Hall of Fame Points: 3432 More actions · Answer 9

Can anyone help with this issue...this is what is stored in the backupmediafamily table for a transaction log backup

C:\DBs\Backups\Volunteers\Volunteers_backup_2016_08_08_043001_1718650.trn

and this is what is stored for a full backup

{67896F2D-C306-422C-B95A-2491B8244E04}

No name/file location is stored so I cant restore using the interface I have to manually restore which makes restoring to set points more difficult and time consuming.

Both these tasks are part of the same job and running under the same service user and storing the actual file in the same place and the backup file is actually there...very weird.

I have tried removing and recreating the backup tasks.

Thanks

Rolf

kangarolf Hall of Fame Points: 3432 More actions · Answer 10

Bump..sorry if thats not allowed.

I still cant find any solution to this problem and it worries me =)

I tried using the set of scripts given above from https://ola.hallengren.com/sql-server-backup.html and they backed up find but had the same issue..If I try to restore through SSMS the file names for the full backups are missing, just as when the backup wizard and server agent are used.

I have checked all the permissions in the backup folder and they match the default backup folder created by MSSQL on install.

Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 11

You have other backups going on other than native backups. Add device_type from backupmediafamily in the query you had used earlier - you'll be able to tell by looking but I think it's 2 are the native backups. The others are not.

You should also check the SQL Server logs. Look for backups recorded in the log, look for freeze i/o, i/o resumed from VSS backups.

Sue

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

and this is what is stored for a full backup
{67896F2D-C306-422C-B95A-2491B8244E04}

As Sue says above, this indicates that 3rd party backups are being taken; do your sysadmins use netbackup or something like that?

kangarolf Hall of Fame Points: 3432 More actions · Answer 13

Hi all,

Thanks for the reply.

There is a windows backup copy running on the Hyper V host that takes a VSS backup of the Hyper V virtual machines..including the database server.

Can you explain why this then messes up the SQL backups I run within the database server and how I can resolve it?

Thanks

Rolf

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

If they're using a 3rd party tool (e.g. TSM, NetBackup) to take the backups, you'd need to use the same tool to restore those backups.

It would be a good idea to speak to whoever scheduled those backups to find out exactly what they're taking (are they full/copy-only/transaction log, etc.) and how often because you (plural) need to decide what your recovery strategy is in the event of a disaster. It may be that they have this all figured out (but just haven't told you), or it could be (like in some places I've worked) that they're blindly taking backups of everything without realising that there are knock-on effects to your databases.