• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

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.
6 Solutions
QlemoBatchelor, 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 (https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-db-transact-sql 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..
QlemoBatchelor, 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.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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:

QlemoBatchelor, 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

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now