T-SQL script to attach over 1500 db's

Transact-SQL
1

I have a table in a SQL database that has 3 columns. A - dbname(name of db to attach), B - dbfile(path and .mdf), C - logfile(path and _log.ldf)

There are over 1500 db's that need to be attached. I want to use a while loop to iterate through the table and get each column value passed into a variable then run a script to attach the db using the variables. Here's what I have in script so far:

CODE

DECLARE Master_Cursor CURSOR FOR

SELECT
[dbname], [fpath], [flog]
FROM tempAttach.dbo.dbNames
where [dbname] NOT IN(SELECT name from sysdatabases);

OPEN Master_Cursor;

DECLARE @ID varchar(55),
@fp varchar(255),
@fplog varchar(150);

FETCH NEXT FROM Master_Cursor
INTO @ID, @fp, @fplog

WHILE @@FETCH_STATUS=0
BEGIN

      //Attach code goes here

FETCH NEXT FROM Master_Cursor
INTO @ID, @fp, @fplog;

END;

CLOSE Master_Cursor;
DEALLOCATE Master_Cursor;

Can anyone help with the syntax on the attach portion?

thanks for your help in advance!!!

Mike

2

Personally I think you would be better off generating a script, and then running the script.

The trouble, to my mind anyway!, of doing this one-off type of thing using dynamic SQL is that it is hard to debug, and if it part-works its even harder to figure out which ones did, and which ones did not, run properly.

Also, you could (if you needed to) generate the script on one server and actually run it on a different one.

So I would do something like:

SELECT	'PRINT ''----> ' + REPLACE(dbname, '''', '''''') + ''''
	+ CHAR(13)+CHAR(10)+'GO'
	+ CHAR(13)+CHAR(10)+'CREATE DATABASE ' + QuoteName(dbname)
	+ CHAR(13)+CHAR(10)+'	ON (FILENAME = ''' + REPLACE([fpath], '''', '''''') + '''),'
	+ CHAR(13)+CHAR(10)+'	(FILENAME = ''' + REPLACE([flog], '''', '''''') + ''')'
	+ CHAR(13)+CHAR(10)+'	FOR ATTACH'
	+ CHAR(13)+CHAR(10)+'GO'
FROM	tempAttach.dbo.dbNames
WHERE	[dbname] NOT IN
	(
		SELECT	name
		FROM	sys.databases
	)

Sample output:

PRINT '----> MyDBName'
GO
CREATE DATABASE [MyDBName]
	ON (FILENAME = 'C:\MyDataPath\MyDBName_Data.mdf'),
	(FILENAME = 'C:\MyLogPath\MyDBName_Log.ldf')
	FOR ATTACH
GO
1 Like