SQL Cheat Sheet for SQL Server DBA Daily Operations

Problem

Every database environment has a list of operations that should be performed whether it is daily, weekly, yearly, etc. In this article, we look at various scripts that can help you manage your SQL Server environment.

Solution

This SQL cheat sheet contains a series of scripts to help address common situations that might be brought to a Microsoft SQL Server DBA. Each has a proper explanation as to what it does and how it works. These scripts can be downloaded and saved for a rainy day. Each script is being offered as-is without any warranty.

Consider linking this tip to your SQL files to access them quickly.

Note: These scripts are not specific to any one SQL Server version. However, some may not work on legacy versions, such as those that are already end-of-life when this tip was published. Others may not work in cloud installations such as Azure DB or Azure Managed Instance.

Backups

Below are some useful scripts for creating a backup or finding available backups.

Create a SQL Server Backup

Backing up databases is among the most common actions taken by a DBA. Most backup operations are handled automatically, but sometimes a one-off backup needs to happen manually when a user requests it. This is the anatomy of a full backup followed by a log backup. The WITH COMPRESSION part is optional. Here is the syntax:

BACKUP DATABASE [DBName] TO  DISK = N'\\ShareServer\ShareName\FileName.bak' WITH COMPRESSION;
BACKUP LOG [DBName] TO  DISK = N'\\ShareServer\ShareName\FileName.trn' WITH COMPRESSION;

Sometimes a DBA needs to confirm that backups are being taken successfully. This next query can be used to determine the most recent backups of a database. Seeing rows here is a good way to prove that backups were taken.

Script to Find Available Backups

It can also be useful to answer a question from a user, such as, “I deleted something that I need back. What dates and times do you have available to restore from?”

It is very important to remember that having a row in this table does not mean the file is still on the file system where it was created. SQL Server has no way of knowing that a file was deleted or moved. Before telling a user that a file is available for restore, I usually check the file system to confirm that what SQL Server thinks is there is actually there.

This SQL query assumes there is only one backup in each file.

 SELECT
     database_name
    ,type
    ,backup_start_date
    ,backup_finish_date
    ,CAST(backup_size/1048576 AS INT) BackupSizeMB
    ,CAST(compressed_backup_size/1048576 AS INT) CompressedBackupSizeMB
    ,[physical_device_name]
FROM
    msdb.dbo.backupmediafamily fam
    inner join msdb.dbo.backupset bset on bset.media_set_id = fam.media_set_id
WHERE
    type IN ('D', 'L', 'I') --D for Full, L for Transaction Log and, I for dIfferential
     and
    database_name = 'One Specific DB'
     and 
    server_name = @@servername
     and
    backup_finish_date >= DATEADD(DAY, -7, SYSDATETIME())
ORDER BY
     backup_finish_date desc
    ,type;

Retiring or Unretiring a Database

Sometimes when a database is being moved to a new server, the old database is kept around for a short while in case a rollback is needed. When this happens, several options are available and are presented below from least impactful to most impactful.

Set a Database to Read-Only

Setting a database to read-only means that users can connect to the database and read from it but not write to it.

ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT;

To roll back a change from read-only, set it back to read/write.

ALTER DATABASE [DBName] SET READ_WRITE WITH NO_WAIT;

Set a Database Offline

Setting a database to offline means that SQL Server will not allow users to connect to it, read data, or write data. The database will still appear in sys.databases and the SSMS Object Explorer.

ALTER DATABASE [DBName] SET OFFLINE WITH NO_WAIT;

To bring an offline database back, simply set it to online.

ALTER DATABASE [DBName] SET ONLINE;

Detach a Database

Detaching a database tells SQL Server to drop its locks on the data and log files. The database will no longer be listed in Object Explorer or sys.databases. The database files will still exist on the operating system. Before doing this, it may be worth noting the location of the files, as you’ll need them to recover the database should you want it back.

SELECT filename FROM DBName.sys.sysfiles;
EXEC master.dbo.sp_detach_db @dbname = N'DBName';

To recover a detached database, run the procedure below. To run this, you’ll need to know the location of the files. Include one FILENAME parameter for each file. This example has 2 filenames, but a database could have more depending on how it was set up.

CREATE DATABASE [DBName] ON 
( FILENAME = N'C:\MSSQL15.MSSQLSERVER\DBName.mdf' ),
( FILENAME = N'C:\MSSQL15.MSSQLSERVER\DBName_log.ldf' )
 FOR ATTACH;

You can read a whole tip about detaching and reattaching databases and another about fixing a common permission error when doing detach/attach operations.

Deleting a Database

Deleting the database, also known as dropping the database, removes the database from sys.databases and the Object Explorer, just like detaching. However, unlike detaching, the underlying data and log files are removed from the operating system.

DROP DATABASE DBName;

To recover a dropped database, a restore from backup will be required.

Checking on Server and Database Maintenance

Failed Jobs

The most common way to alert a DBA to failed SQL Server Agent jobs is to use email. Sometimes I want a concise look at all the failed jobs for one server on one screen. You can query this information from the msdb database using the query below. The very last line tells it to go back 3 days. That date range can be easily modified as needed.

USE msdb
 
SELECT
     sj.name
    ,sjs.step_id
    ,sjs.step_name
    ,CASE sjh.run_status
     WHEN 0 THEN 'Failed'
     WHEN 2 THEN 'Retried'
     WHEN 3 THEN 'Cancelled'
     ELSE 'Running Now'
     END RunStatus
    ,dbo.agent_datetime(sjh.run_date, sjh.run_time) StepStartTime
    ,CONVERT(VARCHAR, dbo.agent_datetime('19000101', sjh.run_duration), 8) Duration
    ,sjh.message
FROM dbo.sysjobs sj
    INNER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
    INNER JOIN dbo.sysjobhistory sjh ON sjs.job_id = sjh.job_id AND sjs.step_id = sjh.step_id
WHERE
    sjh.run_status <> 1
      AND
    dbo.agent_datetime(sjh.run_date, sjh.run_time) > DATEADD(DAY, -3, SYSDATETIME());

Learn even more about querying SQL Server Agent information out of MSDB.

Get Stats Updates Information

It is important to keep the table statistics for the database up to date. You can check the age of statistics on a table using the following query. NOTE: If you run this for an entire database, it can run for a very long time. Try to focus on a table or small group of tables.

SELECT
     s.name SchemaName
    ,t.name TableName
    ,st.name StatName
    ,STATS_DATE(st.object_id, st.stats_id) AS StatsDate
FROM
    sys.stats st
    INNER JOIN sys.tables t on st.object_id = t.object_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.name = N'Invoices'
     AND
    s.name = N'Sales';

Find Last Good CheckDB

It is important for the DBCC CheckDB job to be run regularly for every database on the server. If there is any question about the last time a database was checked, run this DBCC command. The WITH TABLERESULTS part is important.

DBCC DBINFO(N'DBName') WITH TABLERESULTS;

The output will have a row with a FieldID value of dbi_dbccLastKnownGood. The value on this row is the last successful DBCC CheckDB for the database.

This screenshot shows the last check db for the test database being over 2 years before the publish date of the tip. Yikes!

This database on my test server has not been checked for quite some time! Learn about the importance of corruption detection from this tip.

There are several other useful items in this output, one being dbi_LastLogBackupTime which checks to make sure the log files are backing up as expected.

Next Steps

Resources

Community

Subscribe