June 22, 2016 at 6:30 am
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
June 22, 2016 at 6:41 am
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!
😎
June 22, 2016 at 6:48 am
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..?
June 23, 2016 at 7:02 am
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
June 23, 2016 at 12:49 pm
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 🙂
June 23, 2016 at 1:04 pm
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
June 23, 2016 at 1:18 pm
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!
June 23, 2016 at 1:19 pm
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/
June 23, 2016 at 1:48 pm
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
August 8, 2016 at 4:41 am
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
September 21, 2016 at 2:08 am
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
September 21, 2016 at 7:01 pm
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
September 22, 2016 at 1:55 am
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?
September 22, 2016 at 2:10 am
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
September 22, 2016 at 2:25 am
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.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply