How to check if the SQL Server Database is Online

To check if the SQL Server Database is online, run the following code:

IF EXISTS (SELECT name FROM master.sys.databases
WHERE name = N'Database_Name' AND state_desc = 'ONLINE')
PRINT 'Database is online'
ELSE
PRINT 'Database is not online'

check_if_database_is_online

To backup a mirrored database, create a SQL Server Agent job on both principal and mirror server that will check if the database is online, and backup the database if it is online. Database is in Online state on the principal server and it can be backed up. On the mirror server the database is in Restoring state, and database backup cannot be performed.

To make a full backup of the database if it is online, use this code:

IF EXISTS (SELECT name FROM master.sys.databases
WHERE name = N'Database_Name' AND state_desc = 'ONLINE')
BACKUP DATABASE [Database_Name] TO DISK = N'D:\Backup\Database_Name.bak'
WITH NOFORMAT, NOINIT, NAME = N'Database_Name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

backup_if_database_is_online

Create one job for full backups and one for transaction log backups on both principal and mirror server.

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">