Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In my development/test environment on my laptop, I can't back up the SQL server database files because the file handles are kept permanently open by SQL server (VSS doesn't work because the drive is truecrypted)

I was hoping there may be some setting in SQL server that can make it unlock the data files after a certain period of inactivity and automatically open them again on demand, but I can't find anything.

I don't really want to be dumping the database out every night because it's only a development environment. apart from stopping sql server before I do the backup is there any other solution?

share|improve this question
Doesn't the built-in backup capability of SQL Server handle this for you? Why not just script a SQL Server backup of the databases? – joeqwerty Oct 19 '13 at 15:42
up vote 3 down vote accepted

Another option is the auto close property "ALTER DATABASE [dbname] SET AUTO_CLOSE ON". According to the SQL Server Books online (

The AUTO_CLOSE option is useful for desktop databases because it allows for database files to be managed as regular files. They can be moved, copied to make backups, or even e-mailed to other users. The AUTO_CLOSE process is asynchronous; repeatedly opening and closing the database does not reduce performance.

share|improve this answer
Thanks - this worked great and does just what I want. – Andy Oct 21 '13 at 11:44

Nothing that will automatically do it for you, but you can detach or offline the database from the management studio or the command line, e.g. by issuing the ALTER DATABASE <database> SET OFFLINE via sqlcmd in your backup script.

You may want to reconsider a daily database backup using the BACKUP DATABASE <database> TO FILE='<filename>' syntax that actually backs up the DB itself, not the file.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.