 |
|
|
|
Next: gfj
|
| Author |
Message |
External
Since: Mar 12, 2006
Posts: 25
|
(Msg. 1) Posted: Sat Nov 24, 2007 3:52 pm
Post subject: Database Attach Scripts
Archived from groups: microsoft>public>sqlserver>clustering (more info?)
|
|
|
|
|
| Back to top |
|
 | |
External
Since: Mar 03, 2006
Posts: 234
|
(Msg. 2) Posted: Sat Nov 24, 2007 9:46 pm
Post subject: RE: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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? |
|
| Back to top |
|
| |
External
Since: Aug 24, 2003
Posts: 739
|
(Msg. 3) Posted: Sun Nov 25, 2007 2:57 am
Post subject: Re: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Mar 12, 2006
Posts: 25
|
(Msg. 4) Posted: Sun Nov 25, 2007 3:23 pm
Post subject: RE: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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? >> Stay informed about: Database Attach Scripts |
|
| Back to top |
|
| |
External
Since: Mar 03, 2006
Posts: 234
|
(Msg. 5) Posted: Sun Nov 25, 2007 7:10 pm
Post subject: RE: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
> 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? >> Stay informed about: Database Attach Scripts |
|
| Back to top |
|
| |
External
Since: Mar 12, 2006
Posts: 25
|
(Msg. 6) Posted: Mon Nov 26, 2007 5:39 am
Post subject: RE: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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? >> Stay informed about: Database Attach Scripts |
|
| Back to top |
|
| |
External
Since: Mar 03, 2006
Posts: 234
|
(Msg. 7) Posted: Mon Nov 26, 2007 11:18 am
Post subject: RE: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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? >> Stay informed about: Database Attach Scripts |
|
| Back to top |
|
| |
External
Since: Mar 12, 2006
Posts: 25
|
(Msg. 8) Posted: Mon Nov 26, 2007 12:01 pm
Post subject: RE: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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? >> Stay informed about: Database Attach Scripts |
|
| Back to top |
|
| |
External
Since: Mar 12, 2006
Posts: 25
|
(Msg. 9) Posted: Mon Nov 26, 2007 12:17 pm
Post subject: RE: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Nevermind.
Sorry!
Thanks for the help!
"Charlie" 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:
>
> > 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? >> Stay informed about: Database Attach Scripts |
|
| Back to top |
|
| |
External
Since: Mar 12, 2006
Posts: 25
|
(Msg. 10) Posted: Mon Nov 26, 2007 12:46 pm
Post subject: RE: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
It truncates after 32 db's. Is there any way to avoid this?
"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? >> Stay informed about: Database Attach Scripts |
|
| Back to top |
|
| |
External
Since: Feb 21, 2007
Posts: 457
|
(Msg. 11) Posted: Mon Nov 26, 2007 5:09 pm
Post subject: Re: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Charlie,
You are probably using up the 4000 character space available in the
variable. Here is a minor change to Linchi's script to PRINT each line as
it is created, instead of printing them all at the end. This will give you
pretty much the same output.
RLF
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;
-- PRINT OUT every piece separately.
PRINT @sql;
SELECT @sql = ''
-- AND CLEAR the variable
end
close db_cr;
deallocate db_cr;
"Charlie" wrote in message
> It truncates after 32 db's. Is there any way to avoid this?
>
> "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? >> Stay informed about: Database Attach Scripts |
|
| Back to top |
|
| |
External
Since: Mar 12, 2006
Posts: 25
|
(Msg. 12) Posted: Tue Nov 27, 2007 4:51 am
Post subject: Re: Database Attach Scripts [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
That did it!
Thank you both.
"Russell Fields" wrote:
> Charlie,
>
> You are probably using up the 4000 character space available in the
> variable. Here is a minor change to Linchi's script to PRINT each line as
> it is created, instead of printing them all at the end. This will give you
> pretty much the same output.
>
> RLF
>
>
> 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;
> -- PRINT OUT every piece separately.
> PRINT @sql;
> SELECT @sql = ''
> -- AND CLEAR the variable
> end
> close db_cr;
> deallocate db_cr;
>
>
>
>
>
> "Charlie" wrote in message
>
> > It truncates after 32 db's. Is there any way to avoid this?
> >
> > "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?
>
>
> >> Stay informed about: Database Attach Scripts |
|
| Back to top |
|
| |
| Related Topics: | Database mirroring - Hi Guys, I'm trying to configure mirroring among 3 servers, principal,mirror,witness, i was able to configure it successfully but when i start mirroring it gives this error the server network address "TCP://xx.yy.zz.com:5022" can not be rea...
Single Database Failover - Is it possible to set up SQL Server clustering so that only one database would failover or is it an all or nothing type thing.
Database Mirror in a SQL cluster - I read that you can implement DB mirror in a cluster. However, someone else said that you cannot. Does anyone have any ideas which is correct?
Restoring the master database - As part of testing a cluster-to-cluster migration process, I am attempting to restore the master database from an old test cluster into a new one. I've read quite a bit about this online, and it looks like a relatively easy procedure. These are the..
Recover Model Database - SQL 2000. I was trying to move the system files, I managed to move the master, msdb, tempdb but then it died on the model. and I need to recover database... the sql service keeps failing. thank you!!! |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
