Sql-server – How to bring a database offline safely

sql server

Recently I took the database offline with the option Drop all active connections checked.

After that I couldn't bring it back online due to error Could not redo log record for transaction id on page database. I was able to recover the database with CHECKDB but I want to avoid such a scenario in future

As far as I understand an uncommitted transaction was interrupted which corrupted databases's integrity.

I wonder how can I disable new upcoming connections while letting the active transactions to finish?

Best Answer

Recently I took the database offline with the option Drop all active connections checked.

When you take a database OFFLINE, SQL Server is no longer protecting the database files from modification by other programs. SQL Server tries to set file ACLs to prevent other users/programs from modifying the files, but having the database ONLINE provides an important additional protection.

Whenever possible perform actions in a way that doesn't take databases OFFLINE or stop the SQL Server service. EG take the database to SINGLE USER mode instead of OFFLINE, and use Backup/Restore instead of moving database and databse files.

And if you do take the database OFFLINE, ensure that you have a backup first.