Database Attach Scripts

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?
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?
|||Charlie
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:220D9152-7C48-41BF-8CD8-8C3DD32C2C35@.microsoft.com...
>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?
|||What does this script do exactly?
"Linchi Shea" wrote:
[vbcol=seagreen]
> 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:
|||> What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.
Linchi
"Charlie" wrote:
[vbcol=seagreen]
> What does this script do exactly?
> "Linchi Shea" 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:
[vbcol=seagreen]
> Generate a T-SQL script to sp_attach_db every user database.
> Linchi
> "Charlie" 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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||Nevermind.
Sorry!
Thanks for the help!
"Charlie" wrote:
[vbcol=seagreen]
> 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:
|||It truncates after 32 db's. Is there any way to avoid this?
"Linchi Shea" wrote:
[vbcol=seagreen]
> 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:

Database Admin Tool Prerequisites

The prerequisites for the Database Admin Tool says:
a.. Microsoft .NET Framework 2.0
a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
2005 SP2 (32-bit only)
Does that mean the Database Admin Tool will work with SQL Server 2005
Workgroup Edition?
Hi
"Carel" wrote:

> The prerequisites for the Database Admin Tool says:
> a.. Microsoft .NET Framework 2.0
> a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
> 2005 SP2 (32-bit only)
> Does that mean the Database Admin Tool will work with SQL Server 2005
> Workgroup Edition?
>
The prerequisites say you have installed service pack 2 and .NET Framework
2.0. Workgroup Edition is only available as 32bit
John
|||Thanks John,
But I am still not clear. The prerequisities only mention SQL Express & SQL
Server.
Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
database?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4B6538F3-6F44-4F37-B945-BBACA7EEEA00@.microsoft.com...
> Hi
> "Carel" wrote:
> The prerequisites say you have installed service pack 2 and .NET Framework
> 2.0. Workgroup Edition is only available as 32bit
> John
|||Hi Carel
"Carel" wrote:

> Thanks John,
> But I am still not clear. The prerequisities only mention SQL Express & SQL
> Server.
> Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
> database?
>
Yes, Microsoft SQL Server 2005 (32 bit) will refer to all editions
(Standard, Enterprise, Workgroup and Developer) including Workgroup.
John

Database Admin Tool Prerequisites

The prerequisites for the Database Admin Tool says:
a.. Microsoft .NET Framework 2.0
a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
2005 SP2 (32-bit only)
Does that mean the Database Admin Tool will work with SQL Server 2005
Workgroup Edition?Hi
"Carel" wrote:

> The prerequisites for the Database Admin Tool says:
> a.. Microsoft .NET Framework 2.0
> a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
> 2005 SP2 (32-bit only)
> Does that mean the Database Admin Tool will work with SQL Server 2005
> Workgroup Edition?
>
The prerequisites say you have installed service pack 2 and .NET Framework
2.0. Workgroup Edition is only available as 32bit
John|||Thanks John,
But I am still not clear. The prerequisities only mention SQL Express & SQL
Server.
Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
database?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4B6538F3-6F44-4F37-B945-BBACA7EEEA00@.microsoft.com...
> Hi
> "Carel" wrote:
>
> The prerequisites say you have installed service pack 2 and .NET Framework
> 2.0. Workgroup Edition is only available as 32bit
> John|||Hi Carel
"Carel" wrote:

> Thanks John,
> But I am still not clear. The prerequisities only mention SQL Express & SQ
L
> Server.
> Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
> database?
>
Yes, Microsoft SQL Server 2005 (32 bit) will refer to all editions
(Standard, Enterprise, Workgroup and Developer) including Workgroup.
John