Search This Blog

Saturday, February 9, 2008

It is possible to delete the data/log files in SQL Express Edition even if the Instance is UP

Yes , that is true but thankfully only in SQL Express Edition :)

Research :This behavior is by design in SQL Express. Until the user database is used explicitly by user, sql will not recover/open that database so at the time of SQL startup anyone can delete/move the datafiles. Once we connect to database, following entry gets logged into the SQL errorlog: 2007-06-07 19:16:09.59 spid51 Starting up database 'Test2'. This behavior using FILEHANDLE.EXE.So the risk of file delete will be there till the application connects to SQL (or actually until the filehandle is acquired by the SQL server service)
Resolution / Workaround : The easiest workaround of this problem would be creating a startup stored procedure which will query any table from the user database. i.e.

1) Suppose we have a user database named 'TEST' in Express instance, create a stored procedure in master database which will query one row from this database:
use master
go
create procedure start
as
select top 1 id from TEST.dbo.testTable

2) Set this SP as startup stored procedure for this instance:
sp_procoption 'dbo.start','startup' ,'true'

Now on every startup, SQL will query this database table which will in turn open this database automatically.

Enjoi :)

No comments: