Need script to detach and attach mdf and ldf SQL Server files

Running SQL SERVER 2000.

 There is one mdf and ldf file I want to detach and move and attach .
I have some experience with SQL server 2000 .

Let's call the mdf file A.mdf and the log file is Alog.ldf   The name for the sql server instance is 'A200'.

Could you please write  a simple script showing:
How to set A.mdf into single user mode.
How to detach A.mdf
How to attach A.mdf which is now in folder c:\A200Storage

Once the database A.mdf is attached, what commands will remove single user mode from A.mdf so it can be used by many users?

Thank you.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
MSSQL 2000 doesn't have a single user mode. If it had, you would need to switch the instance, not the database.
The database release so old that it is difficult to find proper documentation; I cannot remember if sp_detach_db and sp_attach_db exist already ( is noted to be available starting with 2008). Id they work, it is really easy to write the two lines necessary.

Main question: why not just using the Management Studio?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I cannot remember if sp_detach_db and sp_attach_db exist already

Qlemo, yes, as far as I remember SQL Server 2000 has sp_detach_db and sp_attach_db commands available

>> why not just using the Management Studio?

In SQL Server 2000, we have only Query analyzer and Enterprise Manager and Management Studio is from SQL Server 2005 onwards(I just want to add more details to your comments..)

>> There is one mdf and ldf file I want to detach and move and attach .
Please follow the below commands as guided in the below link

Kindly let me know if you face any issues while following the link to help accordingly..
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Yep, I was able to research that  ^  myself meanwhile ;-). And the linked article describes exactly what I had in mind.
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Scott PletcherSenior DBACommented:
First, make sure you keep a separate copy of these files!  If the attach fails, the primary file, at least, will be modified and won't be able to be attached again, you'll need to copy the original files again.

EXEC sp_attach_db 'your_db_name', 'd:\path\to\file\A.mdf', 'l:\path\to\file\Alog.ldf'

You can't make a file single user, only a db.  If the db was in single-user mode when it was detached, or when SQL was shut down, and you want to return it to multi users, isse this command:

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
So to put everything together:
alter database A set SINGLE_USER;
exec sp_detach_db 'A';
-- now move or copy the db files to c:\A200Storage
exec sp_attach_db 'A', 'c:\A200Storage\A.mdf', 'c:\A200Storage\A.ldf'  -- and more, if needed;
alter database A set MULTI_USER;

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
It can be tricky to do that cleanly.  Before you set it into single user mode, you need to make sure YOU are that single user.  But before you detach it, you have to get out of that db, since you can't detach a db that's in use.  So this:

--original db location
USE master
EXEC sp_detach_db 'A'
donpickAuthor Commented:
Thank you all for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.