Moving several databases at once

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 Administration (2000)

Moving several databases at once

Author

Topic

tvengel
Starting Member

1 Post

Posted - 2007-11-06 : 17:28:56

Hello,

We installed a larger disk in our server and need to move the SQL databases to the new disk.
But the problem is that there are more then 400 SQL databases configured in the server. Our accounting program created a new database per customer file. Is there any way to move the at once to the new disk, without having them de-attached and re-attached?

Regards,
Tim Van Engeland

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-06 : 19:50:25

Not really. database per customer seems pretty ineffective. I see an opportunity to re-develop and be a hero.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-06 : 23:00:02

What you can try is modify db file path with 'alter database', stop sql, copy db files to new disk then restart sql.

jcumberland
Starting Member

8 Posts

Posted - 2007-11-07 : 00:18:57

You can detach the databases, copy the files to the new location, then then run an attach operation with the new file paths. You can use the system tables to help generate the commands to save some time. See the example below (undocumented procedure in use), you would need to save the output of the script and run that (minus the line spacers)


SET QUOTED_IDENTIFIER OFF

-- GENERATES ATTACH COMMAND, CHANGE DRIVE PATHS AS NEEDED
exec sp_msforeachdb "
USE ?

IF ( DB_NAME() NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) )
BEGIN
	SELECT
		'sp_attach_db @dbname = ' + '?'

	SELECT
		',@filename1 = N''' + filename + ''''
	FROM
		sysfiles
	WHERE
		name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )

	SELECT
		'GO'
END
"
GO

USE master
GO

-- GENERATES DETACH COMMAND, NEED TO KILL CONNECTIONS FIRST
SELECT
	'sp_detach_db ' + name
FROM
	sys.databases
WHERE
	name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
GO

Jason Cumberland
:wq

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources