By: Eric Blinn | Updated: 2023-04-05 | Comments (1) | Related: More > Database Administration
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 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
- 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
Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-04-05
