search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

Single-User Mode Check Combined With Database Accessibility Check Lead to Backup Failures #755

Open EdwardPollack opened 1 year ago

EdwardPollack commented 1 year ago

When a database is in single-user mode, the backup script will still back it up, so long as it is accessible - even if no additional connections are available to it. The result is that the backup fails, as does the stored procedure and the job that calls it.

In dbo.DatabaseBackup, there is a check prior to backing up a database to determine if a database is in a state where it cannot/should not be backed up. One of those checks is this one on line 3191: AND NOT (@CurrentUserAccess = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)

This check will skip backing up a database if it is both in single-user mode and it is not accessible. Generally speaking, few (if any) organizations want to back up databases in single-user mode and this will often fail as another process has set the database to single-user and is likely using the single connection for another task.

I would request that either:

  1. These checks are separated. This way, single-user mode databases are automatically skipped and inaccessible databases are skipped, but these 2 checks are not tied together.
  2. Whether or not single-user mode databases are backed up is added as a parameter to the stored procedure.

Personally, I lean towards #1 as it makes more sense to me, but either of these options would resolve the problem. Thank you! -Ed

SQL Server version and edition Microsoft SQL Server 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64) Jan 23 2023 17:02:42 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 22621: ) **Also Web, Standard, and Enterprise

Version of the script SQL Server Maintenance Solution - SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, and SQL Server 2022

What command are you executing? dbo.DatabaseBackup (parameters vary per server/environment)

What output are you getting? An error, whenever a database on a server is in single-user mode.

olahallengren commented 2 weeks ago

I don't understand why you are getting an error in this scenario. I would expect it to be skipped, if it is not accessible.