Database Attach Scripts

#1

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?

#2

Something like this:

declare @dbid int,
@sql varchar(4000),
@name sysname,
@old_name sysname,
@filename nchar(260);

declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;

open db_cr;

set @sql = '';
set @old_name = '';

fetch next from db_cr into @dbid, @filename;
while @@fetch_status = 0
begin
If @old_name <> db_name(@dbid)
select @sql = @sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@dbid)) + ''',
N''' + rtrim(@filename) + '''';
else
select @sql = @sql + ', N''' + rtrim(@filename) + '''';

select @old_name = db_name(@dbid);
fetch next from db_cr into @dbid, @filename;
end
close db_cr;
deallocate db_cr;
print @sql;

Linchi

"Charlie" wrote:

Quote:

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?

#3

Charlie
http://dimantdatabasesolutions.blogs...-database.html

"Charlie" <Charlie (AT) discussions (DOT) microsoft.com> wrote

Quote:

I have a few existing 2000 instances that we are migrating to 2005 SP2.
The
instances have a lot of databases on them. Is there an easy way to
generate
the attach scripts for migrating?

#4

What does this script do exactly?

"Linchi Shea" wrote:

Quote:

Something like this:

declare @dbid int,
@sql varchar(4000),
@name sysname,
@old_name sysname,
@filename nchar(260);

declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;

open db_cr;

set @sql = '';
set @old_name = '';

fetch next from db_cr into @dbid, @filename;
while @@fetch_status = 0
begin
If @old_name <> db_name(@dbid)
select @sql = @sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@dbid)) + ''',
N''' + rtrim(@filename) + '''';
else
select @sql = @sql + ', N''' + rtrim(@filename) + '''';

select @old_name = db_name(@dbid);
fetch next from db_cr into @dbid, @filename;
end
close db_cr;
deallocate db_cr;
print @sql;

Linchi

"Charlie" wrote:

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?

#5

Quote:

What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.

Linchi

"Charlie" wrote:

Quote:

What does this script do exactly?

"Linchi Shea" wrote:

Something like this:

declare @dbid int,
@sql varchar(4000),
@name sysname,
@old_name sysname,
@filename nchar(260);

declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;

open db_cr;

set @sql = '';
set @old_name = '';

fetch next from db_cr into @dbid, @filename;
while @@fetch_status = 0
begin
If @old_name <> db_name(@dbid)
select @sql = @sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@dbid)) + ''',
N''' + rtrim(@filename) + '''';
else
select @sql = @sql + ', N''' + rtrim(@filename) + '''';

select @old_name = db_name(@dbid);
fetch next from db_cr into @dbid, @filename;
end
close db_cr;
deallocate db_cr;
print @sql;

Linchi

"Charlie" wrote:

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?

#6

Where does it get a list? We are moving the user db's to a new environment.
I need to attach the raw files, sepcifying the .mdf and .ldf file locations
and db name....

Make sense?

Maybe I am not understanding what you are telling me....

"Linchi Shea" wrote:

Quote:

What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.

Linchi

"Charlie" wrote:

What does this script do exactly?

"Linchi Shea" wrote:

Something like this:

declare @dbid int,
@sql varchar(4000),
@name sysname,
@old_name sysname,
@filename nchar(260);

declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;

open db_cr;

set @sql = '';
set @old_name = '';

fetch next from db_cr into @dbid, @filename;
while @@fetch_status = 0
begin
If @old_name <> db_name(@dbid)
select @sql = @sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@dbid)) + ''',
N''' + rtrim(@filename) + '''';
else
select @sql = @sql + ', N''' + rtrim(@filename) + '''';

select @old_name = db_name(@dbid);
fetch next from db_cr into @dbid, @filename;
end
close db_cr;
deallocate db_cr;
print @sql;

Linchi

"Charlie" wrote:

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?

#7

The script reads the info from the sysaltfiles table on the current server.
If you are moving the files to a new server, you may need to edit the script
to point to the files in different locations.

Unless you have some overriding reasons to use a different directory
structure or a different drive, it's often simpler to keep the file locations
identical.

Linchi

"Charlie" wrote:

Quote:

Where does it get a list? We are moving the user db's to a new environment.
I need to attach the raw files, sepcifying the .mdf and .ldf file locations
and db name....

Make sense?

Maybe I am not understanding what you are telling me....

"Linchi Shea" wrote:

What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.

Linchi

"Charlie" wrote:

What does this script do exactly?

"Linchi Shea" wrote:

Something like this:

declare @dbid int,
@sql varchar(4000),
@name sysname,
@old_name sysname,
@filename nchar(260);

declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;

open db_cr;

set @sql = '';
set @old_name = '';

fetch next from db_cr into @dbid, @filename;
while @@fetch_status = 0
begin
If @old_name <> db_name(@dbid)
select @sql = @sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@dbid)) + ''',
N''' + rtrim(@filename) + '''';
else
select @sql = @sql + ', N''' + rtrim(@filename) + '''';

select @old_name = db_name(@dbid);
fetch next from db_cr into @dbid, @filename;
end
close db_cr;
deallocate db_cr;
print @sql;

Linchi

"Charlie" wrote:

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?

#8

That is really what I need!

This script does not print anything. A assume you want me to run it in
query analyzer?

"Linchi Shea" wrote:

Quote:

The script reads the info from the sysaltfiles table on the current server.
If you are moving the files to a new server, you may need to edit the script
to point to the files in different locations.

Unless you have some overriding reasons to use a different directory
structure or a different drive, it's often simpler to keep the file locations
identical.

Linchi

"Charlie" wrote:

Where does it get a list? We are moving the user db's to a new environment.
I need to attach the raw files, sepcifying the .mdf and .ldf file locations
and db name....

Make sense?

Maybe I am not understanding what you are telling me....

"Linchi Shea" wrote:

What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.

Linchi

"Charlie" wrote:

What does this script do exactly?

"Linchi Shea" wrote:

Something like this:

declare @dbid int,
@sql varchar(4000),
@name sysname,
@old_name sysname,
@filename nchar(260);

declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;

open db_cr;

set @sql = '';
set @old_name = '';

fetch next from db_cr into @dbid, @filename;
while @@fetch_status = 0
begin
If @old_name <> db_name(@dbid)
select @sql = @sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@dbid)) + ''',
N''' + rtrim(@filename) + '''';
else
select @sql = @sql + ', N''' + rtrim(@filename) + '''';

select @old_name = db_name(@dbid);
fetch next from db_cr into @dbid, @filename;
end
close db_cr;
deallocate db_cr;
print @sql;

Linchi

"Charlie" wrote:

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?

#9

Nevermind.

Sorry!

Thanks for the help!

"Charlie" wrote:

Quote:

That is really what I need!

This script does not print anything. A assume you want me to run it in
query analyzer?

"Linchi Shea" wrote:

The script reads the info from the sysaltfiles table on the current server.
If you are moving the files to a new server, you may need to edit the script
to point to the files in different locations.

Unless you have some overriding reasons to use a different directory
structure or a different drive, it's often simpler to keep the file locations
identical.

Linchi

"Charlie" wrote:

Where does it get a list? We are moving the user db's to a new environment.
I need to attach the raw files, sepcifying the .mdf and .ldf file locations
and db name....

Make sense?

Maybe I am not understanding what you are telling me....

"Linchi Shea" wrote:

What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.

Linchi

"Charlie" wrote:

What does this script do exactly?

"Linchi Shea" wrote:

Something like this:

declare @dbid int,
@sql varchar(4000),
@name sysname,
@old_name sysname,
@filename nchar(260);

declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;

open db_cr;

set @sql = '';
set @old_name = '';

fetch next from db_cr into @dbid, @filename;
while @@fetch_status = 0
begin
If @old_name <> db_name(@dbid)
select @sql = @sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@dbid)) + ''',
N''' + rtrim(@filename) + '''';
else
select @sql = @sql + ', N''' + rtrim(@filename) + '''';

select @old_name = db_name(@dbid);
fetch next from db_cr into @dbid, @filename;
end
close db_cr;
deallocate db_cr;
print @sql;

Linchi

"Charlie" wrote:

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?

#10

It truncates after 32 db's. Is there any way to avoid this?

"Linchi Shea" wrote:

Quote:

The script reads the info from the sysaltfiles table on the current server.
If you are moving the files to a new server, you may need to edit the script
to point to the files in different locations.

Unless you have some overriding reasons to use a different directory
structure or a different drive, it's often simpler to keep the file locations
identical.

Linchi

"Charlie" wrote:

Where does it get a list? We are moving the user db's to a new environment.
I need to attach the raw files, sepcifying the .mdf and .ldf file locations
and db name....

Make sense?

Maybe I am not understanding what you are telling me....

"Linchi Shea" wrote:

What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.

Linchi

"Charlie" wrote:

What does this script do exactly?

"Linchi Shea" wrote:

Something like this:

declare @dbid int,
@sql varchar(4000),
@name sysname,
@old_name sysname,
@filename nchar(260);

declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;

open db_cr;

set @sql = '';
set @old_name = '';

fetch next from db_cr into @dbid, @filename;
while @@fetch_status = 0
begin
If @old_name <> db_name(@dbid)
select @sql = @sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@dbid)) + ''',
N''' + rtrim(@filename) + '''';
else
select @sql = @sql + ', N''' + rtrim(@filename) + '''';

select @old_name = db_name(@dbid);
fetch next from db_cr into @dbid, @filename;
end
close db_cr;
deallocate db_cr;
print @sql;

Linchi

"Charlie" wrote:

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?