Using Xcopy to get the .mdf file of pubs database to the App_Data folder of a website of VWD Express project

Scott Chang

Hi all,

I have a stand-alone Windows XP Pro PC that has SQL Server Express (SSE) and Visual Web Developer Express (VWDE) programs. The Microsoft "pubs" Database is installed in the SQL Server Management Studio Express (SSMSE). I created a website in my VWDE program. I need the .mdf file of the pubs Database in the App_Data folder of website of my VWDE project. I think that User Instance in my SSE is established. I have studied Xcopy Deployment (SQL Server Express) and User Instance for a quite a while and I still do not know where and how to use Xcopy to get the mdf file of the pubs database into the App_Data folder of the website of my VWDE project. Please help and give me the detailed key steps/instructions about where and how to get the .mdf file of the pubs database into the App_Data folder of the website of my VWDE project via Xcopy.

Thanks in advance,

Scott Chang

Arnie Rowland

As long as the Pubs database is attached to an operating SQL Server, you will not be able to copy the mdf file.

Using SSMSE, right-click on the database and select [Tasks], then [Detach]. You can now copy or XCopy the mdf file (and the ldf file) to whereever you desire.

Again, using SSMSE, right-click on [Databases] and select [Attach]. Follow the prompts to locate the mdf file in its new location.

Scott Chang

Hi Arnie, Thanks for your valuable response.

(1) I tried to do the "Using SSMSE, right-click on the database and select [Tasks], then [Detach]. " as you instructed. First I saw

the following:

Detach Database

Select a Page Script Help

General Database to detach:

Database Name Drop Connection Update Statistics Keep Full Text Catalog Status

pubs | | | | |V| Failure

|OK| |Cancel|

After I clicked the |OK| button, I saw the following message:

Microsoft SQL Server Management Studio Express

X Detach database failed for Server 'myComputer\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

Additional Information:

-> An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.Express.Connection)

-> EXECUTE permission denied on object 'sp_detach_db', database 'mssqlsystemresource', schma

'sys'. (Microsoft SQL Server, Error:29)

I do not know how to handle this problem. Please help and tell me what I should do to get the "Detach" thing works.

(2) I have 2 more questions to ask you about the instructions you gave me in your last response:

(i) If I detach the pubs in SSMSE suscessfully, I can do ".... You can now copy or XCopy the mdf file (and the ldf file) to whereever you desire.

Again, using SSMSE, right-click on [Databases] and select [Attach]. Follow the prompts to locate the mdf file in its new location."

Do I understand your above instructions correctly

(ii) I think that "....You can now copy or XCopy the mdf file (and the ldf file) to whereever you desire...." in the (2) (i) step is "XCopy" not the regular copy. Am I right on this matter

Sorry to ask you the above damn questions. Please kindly respond again.

Many Thanks,

Scott Chang

Arnie Rowland

It appears that your login does not have permissions to detach/attach databases. Please check your permissions and login account.

Copy and XCopy do about the same thing. XCopy is more versitile and often faster.

Markus Sch.

I think when Auto Close (Database Properties / Options / Automatic) is on, then a database must not be detached for copy. It is enough when the last user exits:

From Help:
Auto Close: Specify whether the database shuts down cleanly and frees resources after the last user exits. Possible values are True and False. When True, the database is shut down cleanly and its resources are freed after the last user logs off.