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?

  • 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

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.

| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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