Test your scripts against different database states

Probably you all have created some scripts that were just working fine on your instance but they didn’t work on production. I’ve seen a lot scripts that were running just fine as long as all the databases have a status ONLINE. But as soon as a database has another state, the scripts start to fail… The possible database states are:

    • ONLINE
    • OFFLINE
    • RESTORING
    • RECOVERING
    • RECOVERY PENDING
    • SUSPECT
    • EMERGENCY

More information on the database states can be found on http://technet.microsoft.com/en-us/library/ms190442.aspx. You should handle the different database states in your scripts and test them against all different database states. Now… the challenge is to force your database in a particular state. In this blog post, I’m going to explain how you can create test databases with a different state.

ONLINE

That’s the easy part. When you create a new database, it will automatically become ONLINE

CREATE DATABASE ONLINEDB

OFFLINE

When you want to create an OFFLINE database you first have to create the database and run the ALTER DATABASE statement to bring it OFFLINE.

CREATE DATABASE OFFLINEDB
GO
ALTER DATABASE OFFLINEDB SET OFFLINE
GO

You can also bring a database offline with the GUI of the SQL Server Management Studio.

RESTORING

To create a database in RESTORING state, create a new database, take a FULL BACKUP of the database and RESTORE the database with the option NORECOVERY. Your database will not come ONLINE but it will stay in RESTORING mode.

CREATE DATABASE RESTORINGDB
GO
BACKUP DATABASE RESTORINGDB TO DISK=<YourBackupPath>RESTORINGDB.bak' WITH COPY_ONLY
GO
RESTORE DATABASE RESTORINGDB
FROM DISK='<YourBackupPath>RESTORINGDB.bak'
WITH REPLACE,NORECOVERY
GO

RECOVERING

This is a temporary state. Normally, a database is only “In Recovery” mode during startup. So, when SQL Server starts up the database or when you bring it back ONLINE from an OFFLINE state. I haven’t found a possible method to simulate a database that stays in RECOVERING mode…

RECOVERY PENDING

Follow these steps to create a database with a RECOVERY PENDING state

  • Create an empty database
  • Stop the SQL Server Service
  • Rename or remove the database transaction log file of the database
  • Restart the SQL Server Service

You will notice that the database will now have a Recovery Pending state

SUSPECT

I followed this blog post of Paul Randal (blog | twitter) to create a SUSPECT database. However, my database always turned in Recovery Pending state and not SUSPECT… In the blog post, Paul is performing and update in an uncommitted transaction. I changed it into an insert of an extra colleague (myself ;)) and stopped the instance. I opened the log file with the same XVI32 tool and searched for my name and changed that section. When I restarted the SQL Server instance, the engine was unable to perform a rollback of my transaction and the database finally got into a SUSPECT state. Paul describes in this article the differences between SUSPECT and RECOVERY PENDING.

EMERGENCY

When you want to create a database that needs to go into EMERGENCY mode, you first have to create the database and run the ALTER DATABASE statement.

CREATE DATABASE EMERGENCYDB
GO
ALTER DATABASE [EMERGENCYDB] SET EMERGENCY
GO

OTHER DATABASE OPTIONS

Apart from the above mentioned states, I would also recommend to create the following test databases.

  • Read Only –  ALTER DATABASE … SET READ_ONLY
  • Restricted User – ALTER DATABASE … SET RESTRICTED_USER
  • Single user – ALTER DATABASE … SET SINGLE_USER
  • A database with name DB_EX@T!ç-$&ù!&.,.;=+#

Well, start testing your scripts and see how many of them could be improved.

Leave a Reply

Your email address will not be published. Required fields are marked *