How to attach an existing SQL Server 2000 Database to SQL Server 2000 Desktop Engine?

This article describes how you can attach an existing SQL Server 2000 database to SQL Server 2000 desktop engine. Situation arises when you need to copy an existing database from MS SQL Server 2000 for placing it on another computer that is running Microsoft SQL Server 2000 Desktop Engine.

What you have to do is temporarily detach the database from its SQL Server, copy the database file .MDF and log file .LDF to the target computer, and finally attach the files to the SQL Server 2000 Desktop Engine server there. For attaching an existing SQL Server 2000 database to SQL Server 2000 desktop engine, follow the below mentioned steps. These steps demonstrate how to copy the sample PUBS named database from a computer that is running SQL Server 2000 and how to place it on a computer that is running SQL Server 2000 Desktop Engine:

1. On the server that is running SQL Server 2000, open SQL Server Query Analyzer, and type the following line:

sp_detach_db pubs

1. On the Query menu, click Execute (or press F5) to run the query. It is to be noted that temporarily detaching the database from the server allows you to copy the data and log files.

2. In Windows Explorer, copy Pubs.mdf and Pubs_log.ldf from the Microsoft SQL Server\MSSQL\Data folder on the server to the Microsoft SQL Server\MSSQL\Data folder on the SQL Server 2000 Desktop Engine computer.

3. To attach the files to the SQL Server computer again, run the following statements in the Query Analyzer.

EXEC sp_attach_db @dbname = ‘pubs’,

@filename1 = ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf’,

@filename2 = ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf’

1. Use your copy of SQL Server 2000 Desktop Engine to open any working Access project.

2. Click Queries, and then click New.

3. In the New Query dialog box, click Create Text Stored Procedure, and then click OK.

4. Type the following statements in the new procedure.

Create Procedure AttachDB

As

EXEC sp_attach_db @dbname = ‘pubs’,

@filename1 = ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf’,

@filename2 = ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf’

return

1. Save the stored procedure, and then close it.

2. Double-click AttachDB to run it. You will receive a message indicating that no records were returned. Click OK.

Now, the PUBS sample database is attached to your SQL Server 2000 Desktop Engine. Well, you can see that it’s so easy to attach an existing SQL Server 2000 database to SQL Server 2000 desktop engine.

Comments are closed.