Back on September 4th I posted an article
about using DMO to attach a LOT of databases very quickly and easily. If you
haven't read it yet, take a couple minutes to do so before continuing. I also
challenged our readers to find a way to implement the solution using T-SQL.
Whether you use DMO or T-SQL to solve a problem usually depends on your
background. I use VB quite often, so being able to set a reference to the DMO
library and keep working in the same environment is a productive way for me to
work. So my thought was - how would someone really comfortable with T-SQL solve
the problem?
I'm pleased to announce Wynn Muse as the winner of the contest. I'm including
the entire script as he forwarded to me, you can download the file here,
and it will also be added to our script
library as well. Wynn will be receiving a copy of JMS Messaging, published
by Wrox (yes, an off topic book, but is any technology book really off topic?).
It's an excellent piece of code - even if you don't need to accomplish this
particular task, I think you'll benefit from working through the solution. Never
know what you'll find when you read code -- I make the point because I was
pleasantly surprised to find an answer to a question I've been asked several
times -- how do you get a list of file names from a folder into a table?
Solutions I've seen or thought of include using the file system object (hard
from T-SQL, easy from DTS) to using xp_cmdshell to execute the dir command and
redirect it's output to a file, then import the file (using DTS). Stop and think
for a minute - how would you do it?
Long pause while you think.
Now take a look at this excerpt from the script:
| CREATE TABLE #dir_result (filename varchar (255), dbname varchar(255)) EXEC master..xp_sprintf @strExec OUTPUT, 'dir /b %s*.mdf', @MDFpathFromServer INSERT #dir_result (filename) EXEC master..xp_cmdshell @strExec |
The real workhorse is still xp_cmdshell - but the trick is to remember it
returns a result set just like any other stored procedure - and in this case
it's the list of files we need! Wynn uses the xp_sprintf procedure to do the
work of building the final string to get the exact directory string he needs - I
think a cleaner solution than using Replace.
Thanks and congratulations again to Wynn - a great job! As always, post your
comments or ideas in the attached discussion forum, I look forward to hearing
from you.
| IF EXISTS (SELECT name FROM sysobjects WHERE name = N'sp_attach_db_from_dir' AND type = 'P') DROP PROCEDURE sp_attach_db_from_dir GO CREATE PROCEDURE sp_attach_db_from_dir @MDFpath varchar (255), @serverMDFpath varchar (255) = NULL, @sExclude varchar (8000) = 'msdbdata, northwnd' /* Procedure sp_attach_db_from_dir Copies mdf files located in a directory on a client and attaches them to the connected server Usage sp_attach_db_from_dir @MDFpath=directory which contains the mdf files for attaching [,@serverMDFpath=path to the server's main db folder as seen from the server (default, NULL)] [,@sExclude=mask specifying which existing MDFpath files to exclude (default, 'msdbdata, northwnd'] Notes * Most of the time the @MDFpath will not be the path that the server can use to access the MDF files These three things can happen - 1) The @MDFpath is used and modifed to UNC using the admin share) (ex. '\\machinename\C$\Temp\db') 2) If the sp is run locally, the @MDFpath is used without modifications. 3) If the @MDFpath is entered in UNC notation to begin with, then the @MDFpath is used without modifications. * Since the admin share is used, your MSSQLSERVER service must be started by an account with admin rights (the LocalSystem won't work) You can, if you are using the LocalSystem account, just run the sp locally on the server (the MDFpath must also be accessible to LocalSystem) Or you can use UNC to access a share that has "everyone" access (accessible to LocalSystem). * When @serverMDFpath is NULL the DATA path entered during the SQL Server installation is used(ex. 'C:\Program Files\Microsoft SQL Server\MSSQL\Data') * Enter @sExclude values as a comma separated string (ex. 'mydb1, mydb2, mydb3') * There is no need to exclude existing dbs, these are automagically removed from the attach process - except for the defaults ('msdbdata, northwnd') * This procedure exploits the fact that SQL 2K can attach a db without the existence of the LDF - No LDF are used in the copy and attach process * This procedure assumes that all user dbs are named with the "databasename_data.mdf" convention. Contact wynn.muse@routematch.com */ AS -- For the populating the temptable & the cursor DECLARE @strExec varchar (8000) -- For the cursor DECLARE @filename varchar(255) DECLARE @logfilename varchar(255) DECLARE @dbname varchar(255) DECLARE @res int -- For the Exclusions DECLARE @value varchar (8000) DECLARE @iSnag smallint -- For resolving the MDFpath for the server DECLARE @MDFpathFromServer varchar (255) SET NOCOUNT ON IF @MDFpath IS NULL OR @MDFpath = '' BEGIN RAISERROR ('The MDFpath entered does not exist.',16,1) RETURN END -- ============================================= -- Resolve the MDFpathFromServer -- ============================================= IF HOST_NAME() <> SERVERPROPERTY('MachineName') BEGIN IF LEFT(@MDFpath, 2) = '\\' SET @MDFpathFromServer = @MDFpath ELSE SET @MDFpathFromServer ='\\'+(SELECT HOST_NAME())+'\'+STUFF(@MDFpath,2,1,'$') END ELSE SET @MDFpathFromServer = @MDFpath IF RIGHT(@MDFpathFromServer,1) <> '\' SET @MDFpathFromServer =@MDFpathFromServer+'\' EXEC master..sp_MSget_file_existence @MDFpathFromServer, @res out IF (@res = 0) BEGIN RAISERROR ('Either the MDFPath <%s> does not exist or the MSSQLSERVER Service Account can''t access it.',16,1, @MDFpathFromServer) RETURN END -- ============================================= -- Resolve the serverpath -- ============================================= IF @serverMDFpath IS NULL OR @serverMDFpath = '' BEGIN EXEC sp_MSget_setup_paths '', @serverMDFpath OUT SET @serverMDFpath =@serverMDFpath+'\Data' END IF RIGHT(@serverMDFpath,1) <> '\' SET @serverMDFpath =@serverMDFpath+'\' -- ============================================= -- Manage the exclusions -- Take the array and throw it into a table -- ============================================= IF CHARINDEX('msdbdata',@sExclude)=0 SET @sExclude = @sExclude +', msdbdata' IF CHARINDEX('northwnd',@sExclude)=0 SET @sExclude = @sExclude +', Northwnd' IF OBJECT_ID('tempdb..#tDbName') IS NOT NULL DROP TABLE tempdb.#tDbName CREATE TABLE #tDbName (dbname varchar(8000)) WHILE CHARINDEX(',', @sExclude)>0 BEGIN SET @value = SUBSTRING(@sExclude,1, CHARINDEX(',',@sExclude)-1) INSERT #tDbName VALUES(@value) SET @iSnag = DATALENGTH(@value) + 1 SET @sExclude = LTrim(Right(@sExclude,DATALENGTH(@sExclude) - @iSnag)) END INSERT #tDbName VALUES (@sExclude) -- ================================================= -- Create and populate a temp table with the MDFpath filenames -- ================================================= IF OBJECT_ID('tempdb..#dir_result') IS NOT NULL DROP TABLE tempdb.#dir_result CREATE TABLE #dir_result (filename varchar (255), dbname varchar(255)) EXEC master..xp_sprintf @strExec OUTPUT, 'dir /b %s*.mdf', @MDFpathFromServer INSERT #dir_result (filename) EXEC master..xp_cmdshell @strExec IF NOT EXISTS(SELECT * FROM #dir_result) BEGIN RAISERROR ('No MDFs to attach: Check your directory.',16,1) RETURN END IF EXISTS(SELECT * FROM #dir_result WHERE filename LIKE '%d.') BEGIN RAISERROR ('No MDFs to attach: Check your directory.',16,1) RETURN END -- ============================================= -- Clean-up the temp table: -- Remove the server's existing dbnames -- Remove the null field carried over from the dir -- Remove any exclusions -- ============================================= UPDATE #dir_result SET dbname = LEFT(filename,CHARINDEX('.MDF',filename)-1) WHERE CHARINDEX('_', filename)=0 UPDATE #dir_result SET dbname = LEFT(filename,CHARINDEX('_',filename,LEN(filename)-9)-1) -- We have some dbnames that include the underscore, hence the third argument in the CHARINDEX WHERE CHARINDEX('_', filename)>0 DELETE #dir_result WHERE dbname IN (SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA) OR filename IS NULL OR dbname IN (SELECT dbname from #tDbName) IF NOT EXISTS(SELECT * FROM #dir_result) BEGIN RAISERROR ('No MDFs to attach: Either your exclusions are filtering them all out or these dbs have already been attached.',16,1) RETURN END -- ============================================= -- Copy the database(s) form client to server -- Attach the database(s) -- ============================================= IF OBJECT_ID('tempdb..#copy_result') IS NOT NULL DROP TABLE tempdb.#copy_result CREATE TABLE #copy_result (filename varchar (255), dbname varchar(255)) DECLARE cAttach CURSOR READ_ONLY FOR SELECT filename, dbname FROM #dir_result OPEN cAttach FETCH NEXT FROM cAttach INTO @filename, @dbname WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN -- xcopy the files to the server and ensure that it was successful EXEC master..xp_sprintf @strExec OUTPUT, 'xcopy "%s%s" "%s"', @MDFpathFromServer, @filename, @serverMDFpath DELETE #copy_result INSERT #copy_result (filename) EXEC master..xp_cmdshell @strExec IF NOT EXISTS (SELECT * FROM #copy_result WHERE filename LIKE '%file(s) copied%') RAISERROR('xcopy not successful, file may be in use, check permissions, etc.',16,1) SET @filename = @serverMDFpath+@filename --insurance against any lingering log files SET @logfilename = stuff(@filename,datalength(@filename)-7,10,'log.ldf') EXEC master..xp_sprintf @strExec OUTPUT, 'del "%s"', @logfilename EXEC master..xp_cmdshell @strExec , NO_OUTPUT EXEC @res=sp_attach_db @dbname, @filename IF (@res <> 0) RAISERROR ('Error attaching %s database.',16,1,@dbname) ELSE PRINT Char(13) + 'Attach operation of ' + @dbname + ' successful' END FETCH NEXT FROM cAttach INTO @filename, @dbname END CLOSE cAttach DEALLOCATE cAttach GO |