Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
1  
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

2 Answers 2

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 (http://msdn.microsoft.com/en-us/library/bb522682(v=sql.120).aspx):

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

 
discard

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.