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:
