Move all DB's & logs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Move all DB's & logs

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2003-10-16 : 10:43:31

I have about 40 DB's on a server.

I need to move the MDFs and LDFs from current drive to destination drive.

Using sp_attach & detach

is there a sp_foreach db or something.

maybe loop through all dbs from a sys table.

Anyone have a script or ideas.

Thanks

slow down to move faster...

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-16 : 11:53:20

I'm just writing this - I'll leave you to test

create table #a (name varchar(128))
insert #a
select name
from master..sysdatabases
where name not in ('master','msdb','model','tempdb')

declare @name varchar(128), @sql varchar(1000)
select @name = ''

while @a < (select max(name) from #a)
begin
select @name = min(name) from #a where name > @name
select @sql = 'exec sp_detach_db ' + @name
exec (@sql)
end

You will be better off creating explicit inserts for the databases then you can use that on the target

select
'insert #a select ''' + name + ''''
from master..sysdatabases
where name not in ('master','msdb','model','tempdb')

On the target you will have to generate the attach statements including the path for the files.

I think what you are trying to do is very dangerous as dettach/attach can lose the database. For this number of databases I would do a backup and restore using a similar method. At least make sure you have backups before you try it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-10-16 : 17:00:26

You might want to try some of the techniques in this article [url]http://www.sqlteam.com/item.asp?ItemID=9465[/url]. Many of these techniques build scripts that you can proofread before you run them.

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-16 : 17:31:36

I just did this yesterday except for less databases. If all that you are doing is replacing the drive, then...

Just stop the MSSQLSERVER service. Then go the directory where the files are located, copy them. Paste them over to the new drive. Change the drive letter of the current drive to a different letter. Now change the new drive to what the current drive used to be. Now start the MSSQLSERVER service.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources