Introduction
As a SQL Server Database Administrator (DBA), daily operations can be time-consuming and require a vast amount of knowledge and experience. From managing backups to troubleshooting performance issues, a DBA’s daily tasks can be daunting without a proper reference guide. That’s where a cheat sheet or quick reference guide can come in handy. It provides a concise summary of essential SQL Server operations, making it easier for a DBA to perform daily tasks efficiently. In this article, we’ll explore a cheat sheet for SQL Server DBA daily operations that can help simplify the complex daily tasks that a SQL Server DBA is responsible for.
SQL Server Backup Creation
Backing up databases is a common task for a DBA. While most backup operations are automated, occasionally a manual backup may be necessary when requested by a user. The following is the syntax for a full backup followed by a log backup, with an optional compression feature:
BACKUP DATABASE [DBName] TO DISK = N'\ShareServer\ShareName\FileName.bak' WITH COMPRESSION;
BACKUP LOG [DBName] TO DISK = N'\ShareServer\ShareName\FileName.trn' WITH COMPRESSION;
Set a SQL Server Database to Read-Only Mode
To prevent users from writing to a database, it can be set to read-only mode. In this mode, users can still connect to the database and read from it, but they cannot write to it. Here is the syntax to set a database to read-only mode:
ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT;
To undo the read-only mode and restore the ability to write to the database, use the following command:
ALTER DATABASE [DBName] SET READ_WRITE WITH NO_WAIT;
Set a SQL Server Database to Offline Mode
To prevent users from connecting to a SQL Server database, both reading and writing data, it can be set to an offline mode. In this mode, the database will not be accessible to users, but it will still appear in sys.databases and the SSMS Object Explorer. Here is the syntax to set a database to an offline mode:
ALTER DATABASE [DBName] SET OFFLINE WITH NO_WAIT;
To bring the database back online and restore access to it, use the following command:
ALTER DATABASE [DBName] SET ONLINE;
Detach and Recover a SQL Server Database
Detaching a SQL Server database releases its locks on the data and log files. The database will no longer be visible in the SSMS Object Explorer or sys.databases. However, the database files will still exist on the operating system. Before detaching a database, it is recommended to note the file locations, as they are needed to recover the database if required.
To find the file locations of a database, use the following query:
SELECT filename FROM DBName.sys.sysfiles;
To detach a database, execute the following stored procedure, specifying the database name:
EXEC master.dbo.sp_detach_db @dbname = N'DBName';
To recover a detached database, execute the following procedure, specifying the file locations using one FILENAME parameter for each file:
CREATE DATABASE [DBName]
ON (FILENAME = 'C:\DBPath\DBName.mdf'),
(FILENAME = 'C:\DBPath\DBName_log.ldf')
FOR ATTACH;
Drop a SQL Server Database
Dropping a database removes it from the SQL Server instance, including sys.databases and the SSMS Object Explorer. However, unlike detaching, dropping a database also removes the underlying data and log files from the operating system.
To drop a SQL Server database, execute the following statement, specifying the name of the database to be dropped:
DROP DATABASE DBName;
Querying table statistics for a database in SQL Server
To ensure the database’s table statistics remain up to date, it’s essential to periodically check them. The following query can be used to obtain information about the age of statistics on a specific table. Please note that running this query on an entire database can be time-consuming. It’s recommended to target individual tables or small groups of tables.
SELECT
s.name AS SchemaName
,t.name AS TableName
,st.name AS 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'TableName'
AND
s.name = N'SchemaName';
DBCC CheckDB execution for a database in SQL Server
Regularly running the DBCC CheckDB job for every database on a server is crucial. If you’re unsure when a database was last checked, execute the following DBCC command, making sure to include the “WITH TABLE RESULTS” parameter.
DBCC DBINFO(N'DBName') WITH TABLERESULTS;
Conclusion
In conclusion, as a SQL Server DBA, staying on top of daily tasks is essential for ensuring the database system is running smoothly. This quick reference guide has provided a comprehensive overview of the critical tasks that a SQL Server DBA should perform regularly. From checking database backups and monitoring server health to managing security and maintaining table statistics, these tasks are essential for the optimal performance of the SQL Server. By incorporating these best practices into your daily routine, you can ensure that your database system is secure, reliable, and operating at peak efficiency. Remember to regularly review and update your daily tasks as the needs of your organization evolve to ensure your SQL Server remains optimized and healthy.
Learn With Sundar 
Leave a Reply