How To Query A Database That's Being Used By Asp.Net

Question

I have a Sql Server 2008 Express database file that's currently being used by an ASP.NET application, and I'm not sure how to query the database without taking the website down.

I'm unable to copy the database files (.mdf and .ldf files) to another directory, since they're in use by the web server. Also, if I attach the databases to an instance of the sql server (using the 'Create Database [DB name] on (filename = '[DB filename.mdf]') for attach;' command at the sqlcmd prompt), then the application pool user becomes unable to access the database (i.e. the webpages start producing http 500 errors. I think this might have to do with the username for the application pool becoming somehow divorced from the login credentials in the sql server database).

Any suggestions? I realize this is probably a newbie question, since it seems like a rather fundamental task. However, due to my inexperience, I really don't know what the answer is, and I'm pretty stumped at this point, since I've tried a couple of different things.

Thanks!

Andrew

Answer 1

if I attach the databases to an instance of the sql server (using the 'Create Database [DB name] on (filename = '[DB filename.mdf]') for attach;' command at the sqlcmd prompt),

Don't do this to a live database - it's attempting to be setup an MDF to be written to by two different databases...

Use Backup/Restore

As you've found, Attach/ReAttach requires the database to be offline - use the Backup/Restore functionality:

• MSDN: Using SSMS to Backup the Database
• MSDN: Using SSMs to Restore the Backup

Be aware that the backup/restore doesn't maintain logins (& jobs if you have any associated with the database) - you'll have to recreate & sync if using an account other than those with uber access.

Maybe Linked Server would work?

Another alternative would be to setup another SQL Server Express/etc instance on a different box, and use the Linked Server functionality to create a connection to the live/prod data. Use a different account than the one used for the ASP application...

Answer 2

Have you tried this?

How to: Attach a Database (SQL Server Management Studio)