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:
- RECOVERY PENDING
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.
That’s the easy part. When you create a new database, it will automatically become ONLINE
CREATE DATABASE ONLINEDB
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.
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
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…
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
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.
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.