Attach & Detach Multiple Databases

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JosephV - 07 Mar 2008 15:43 GMT

SQL Server 2000 Standard

I need to move multiple databases (about 20) from the C partition to the E
partition. I'm familiar with sp_detach_db and sp_attach_db scripts and is OK
for a handful of databases but tedious with 10 or more databases. My manager
suggested "looping" or something like that. In other words he wants a method
to move these databases in an efficient script. I looked into using SSEUTIL
but that gives me a "mssql$sqlexpress is not installed" error message,
looking for SQL Server Express 2005. I'm sure what I'm asking isn't something
new.

Reply to this Message

TheSQLGuru - 07 Mar 2008 19:19 GMT

check out master.dbo.sysaltfiles for file paths. then you can dynamically
create the detach/attach scripts using sysdatabases and sysaltfiles.

think like this:

select 'sp_detach ... ' + name + '
go'
from sysdatabases

I use such constructs all the time to create scripts to execute.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> SQL Server 2000 Standard
>
[quoted text clipped - 11 lines]
> something
> new.

Reply to this Message

JosephV - 07 Mar 2008 19:54 GMT

Thanks. I'll research your suggestion.

> check out master.dbo.sysaltfiles for file paths. then you can dynamically
> create the detach/attach scripts using sysdatabases and sysaltfiles.
[quoted text clipped - 22 lines]
> > something
> > new.

Reply to this Message

Uri Dimant - 09 Mar 2008 13:39 GMT

Hi
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-data
base.html

> Thanks. I'll research your suggestion.
>
[quoted text clipped - 27 lines]
>> > something
>> > new.

Reply to this Message