SQL Server Database File - Date Modified

Recently, there was a discussion if the date modified of the database files shown in Windows explorer could be used to determine when the database was last used (or recently used). Often, this date is used to determine when a file (eg. word, excel) is last updated. Could that also be applied to SQL Serve database?

SQL Server - Defunct Filegroup and Defunct Data File

A data file becomes DEFUNCT when its respective filegroup is removed or when the data file or its filegroup is not included during the piecemeal restore in simple recovery model. Usually a filegroup can not be removed if the data file is not empty. However, a filegroup could be removed if one of its data file is not online. This post illustrates how a data file becomes DEFUNCT.

SQL Server - Restore and Rebuild Master Database

Do you know if the master database in SQL Server becomes corrupted or unavailable, you won't be able to bring up the SQL Server? This is how important to know the steps to restore and rebuild master database so that you could address the concern when it is required.

Got CHECKSUM on Backup? Check Again

If your SQL Server is using backup task created via SQL maintenance plans or some customized backup scripts, and you are expecting it to performs backup with CHECKSUM operation, you may want to review the backup task.

DBCC CHECKDB and BACKUP with CHECKSUM

I came across a few forums and there are several questions regarding the BACKUP with CHECKSUM and DBCC CHECKDB. People wonder if the BACKUP with CHECKSUM can replace DBCC CHECKDB. Let's find out more about what these commands actually do.

Restore Master Database In Single User Mode

Restoring database requires exclusive access to the database. If the database already exists in the instance, the database needs to be set to single user mode to prevent other users connects to it. If there are users connected to it and you need to restore the database immediately, you can set database to single mode with immediate rollback option to roll back all incomplete transaction and disconnect all other user connection.

SQL Server Offline and Detach Database

Previously I posted how to take the database OFFLINE, ONLINE and DETACH and ATTACH a database. There are some confusion on when these options should be used? It depends on your goal.

SQL Server Database DETACH and ATTACH

Sometimes databases need to be moved to another instance or server. In this scenario, administrator could use detach and attach options. When you DETACH a database, it is removed from the instance of SQL Server, but keeping the database and log files intact. (There are certain limitations and restrictions preventing a database from being detached, e.g. participation in replication, mirroring, snapshot, or is a system database). The detached database no longer exist in the particular SQL Server instance. request to database returns error. To ATTACH a database to an instance is basically creating a database on the instance with existing database files (with/without log file).

SQL Server Database SET OFFLINE and ONLINE

Sometimes database needed to be taken offline for a few reason, e.g. removing all access to the database, or changing the physical file name. The database could then be set online when it is ready.

When setting the database OFFLINE, the database is shut down, and can not be modified during this state. Since the database is unavailable, request to database returns error. When the database is set ONLINE, the database is open and available to be use.