Skip to main content

Posts

Showing posts from May, 2016

SQL Database Backup Restore History

To get SQL Database Backup Restore History you can execute below code using msdb database. This table resides in msdb database. SELECT [rs].[destination_db], [rs].[restore_date], [bs].[backup_start_date], [bs].[backup_finish_date], [bs].[database_name] as [source_db], [bmf].[physical_device_name] as [backup_file_used_for_restore] FROM msdb.restorehistory rs INNER JOIN msdb.backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id] INNER JOIN msdb.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ORDER BY [rs].[restore_date] DESC

Access denied Error on SQL Database Backup failure

Problem : I have faced one issue which was access denied type error . I am able to take Backup manually but when I am trying to set it through maintenance job then its show below Error. Executing the query "EXECUTE testDB.dbo.xp_create_subdir N'D:\DBBackup..." failed with the following error: "xp_create_subdir() returned error 5, 'Access is denied.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Solution :.I resolved above error by providing full right for user everyone to folder DBBackup.  To give rights to folder you can follow steps as below. 1.Select folder Properties and select the Tab Security 2.Now click Add buton then click on Advanced button. 3.Then click Find Now and select Everyone then click ok.