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);
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