0

I wanted to run (using Cassini) two copies of my web application from the same computer - not unreasonable (or so I thought!). One using port 80, the other using port 81. So I did the following:

  1. Stopped Cassini and SQL Express
  2. Copy and paste of the site root folder (and renamed it)
  3. Opened Cassini explorer and setup a new site on port 81 and pointed it to the copied location
  4. Changed the web.config of the copied site so that the connection string used "Database=NewAlias" because SQL Express cant attach two databases with the same alias.
  5. Started Cassini and SQL Express again

When I browsed to the NEW site, the first thing that comes up is:

Unable to open the physical file "C:\site1\App_Data\db_log.ldf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)". Cannot create file 'C:\site2 \App_Data\db_log.LDF' because it already exists. Change the file path or the file name, and retry the operation. Cannot open database "NewAlias" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'. File activation failure. The physical file name "C:\site1\App_Data\db_log.ldf" may be incorrect.

Its trying to open the mdf from the OLD location (even if the web.config specifies the exact mdb path to the new location) but trying to create a log in the NEW location. Then to top it all off, drops the hint that it cannot access the ldf from the OLD location, or maybe cant log into it.

Well done Microsoft and your team once again for some truly intuitive errors! Can anyone help?

Jimbo
  • 22,379
  • 42
  • 117
  • 159
  • LOL - something truly brilliant that I must add. When you try to delete the mdf and ldf that SQL Express `couldnt find` in the error above, you cant because they`re locked by SQL Express :P – Jimbo Jan 20 '11 at 07:48
  • 1
    The above works fine if you omit the "Database=xxx" alias from the connection string. Sadly that means you cant run backup commands against that connection (I wish stackoverflow had a `slow clap` emoticon) – Jimbo Jan 20 '11 at 08:33

2 Answers2

0

I don't think you can just copy a live database via the files. If you detach it first, then copy it, you can then reattach (with sp_detach_db) it by mounting the files as a new database.

sp_detach_db OldDb

Then copy the folder, then reattach (with sp_attach_db) the db files as a new database.

sp_attach_db NewDb, "C:\copy of site\App_Data\db_data.mdf", "C:\site1\App_Data\db_log.ldf"
Ben Laan
  • 2,607
  • 3
  • 29
  • 30
0

Another big problem that sometimes occurs when doing this kind of thing (and did in the scenario above which is vaguely eluded to by the error message) is that although the copied MDF file is being used, its still linked to the original LDF (log file). You can run this command to get a list of which files are being used for a connected instance:

sp_helpfile

Which will give you something like this as a response:

name    fileid  filename    filegroup   size    maxsize growth  usage   
=========================================================
db  1   C:\site2\App_Data\db.mdf    PRIMARY 24192 KB    Unlimited   1024 KB data only   
db_log  2   C:\site1\App_Data\db_log.ldf        78080 KB    2147483648 KB   10% log only

You can see from the output that the log file is being shared with the old database which obviously will cause issues, so you can change it to point to the copied log file as follows:

ALTER DATABASE NewAlias MODIFY FILE (NAME = db_log, FILENAME='c:\site2\App_Data\db_log.ldf')
Jimbo
  • 22,379
  • 42
  • 117
  • 159