Cheat Sheet for SQL Server DBA Daily Operations
By: Eric Blinn | Updated: 2023-04-05 | Comments (1) | Related: More > Database Administration
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.
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
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 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.
- SQL Server Backup Tutorial
- Querying Backup History from MSDB
- Detach and Attach Databases
- Querying SQL Server Agent information from MSDB
- Check DB overview
- Basic SQL Cheat Sheet using SELECT, INSERT, DELETE and UPDATE
- Build a cheat sheet for SQL Server date and time formats
- SQL Cheat Sheet for Basic T-SQL Commands
About the author
View all my tips
Article Last Updated: 2023-04-05