[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 957
  • Last Modified:

Missing .bak files in SQL Server Maintenance Plan

It's been a while since I've used SQL Server, so I'm getting back up to speed.

A client has a SQL Server database which has not been backed up regularly for months.  I'm configuring a maintenance plan, setup to do differential backups of the database nightly and full backups weekly.

My full backup procedure is supposed to backup the database to:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup

with Create a sub-directory for each database checked, and then shrink the database.

I executed the maintenance plan, and the log indicates that it executed successfully, but there are not .bak files on that path, and when I searched the entire C:\ drive there were no recent .bak files on the entire drive.

Any ideas what is going on?
0
Dale Fye
Asked:
Dale Fye
1 Solution
 
TempDBACommented:
I would recommend you to manually debug the code. May be it is creating the bak file somewhere else. You can find whether backup was done or not by checking backuphistory in msdb database

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
0
 
Dale FyeAuthor Commented:
TempDBA,

OK, that tells me that the backup did not actually run, because none of the "LastBackupTimes" actually were today.  I created a new maintenance plan with a "Backup Database Task" for all the databases.  Same configuration as above, and it worked.

Thanks for the assistance.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now