Attach all Databases from Dir

Fügt alle Datenbanken in einem Verzeichnis dem SQL Server hinzu. 
Dies wird oftmals für schnelle Server Umzüge genutzt.

sp_configure 'adv',1
reconfigure
go
sp_configure 'xp_cmd',1
reconfigure
go


BEGIN  
   DECLARE @AttachFromDir       nvarchar(500)   = 'F:\Daten\SQL'    --- <-- Attach Dir angeben
   DECLARE @readDirCmd          nvarchar(1000)
   DECLARE @Aktuell             nvarchar(160)
   DECLARE @dbs                 nvarchar(256)
   DECLARE @phys_name           nvarchar(520)
   DECLARE @dbccstmt            nvarchar(1000)
   DECLARE @dbsVersionAttach    INT
   DECLARE @VersionServer       INT  
   DECLARE @TabFileNames        TABLE (FileName nvarchar(260))
   DECLARE @TabFileAttribiute   TABLE (attrName sql_variant, attrValue sql_variant)
   
   DECLARE cf CURSOR FOR SELECT FileName FROM @TabFileNames

   SET NOCOUNT ON

   SET @readDirCmd = 'dir /b "' + @AttachFromDir + '"\*.mdf'

   INSERT into @TabFileNames 
   EXEC xp_cmdshell @readDirCmd
   
   DELETE from @TabFileNames where FileName IS NULL or FileName = 'File Not Found'

  
   DELETE FROM @TabFileNames 
   WHERE FileName IN (SELECT FileName FROM @TabFileNames a INNER JOIN sys.master_files b ON lower(@AttachFromDir + '\' + a.FileName) = lower(b.physical_name) )

  
   IF not exists (SELECT TOP 1 * FROM @TabFileNames)
   BEGIN
      PRINT 'Keine Dateien gefunden. @AttachFromDir falsch???'
      RETURN
   END

  
   SELECT  @VersionServer = CONVERT (int,DATABASEPROPERTYEX('master', 'version'))

   BEGIN TRY

      OPEN cf

      FETCH NEXT FROM cf INTO @Aktuell

      WHILE @@FETCH_STATUS = 0 
      BEGIN 
 
         SET @phys_name =  @AttachFromDir + '\' + @Aktuell
         SET @dbccstmt = 'DBCC checkprimaryfile (' + '"' + @phys_name + '"' + ',2)'

         INSERT INTO @TabFileAttribiute 
         EXEC (@dbccstmt)
         
         SELECT @dbs = convert (nvarchar(256), attrValue)
         FROM @TabFileAttribiute
         WHERE attrName = 'Database name'
      
        
         SELECT @dbsVersionAttach = convert (int, attrValue)
         FROM @TabFileAttribiute
         WHERE attrName = 'Database version'

   
         IF (@dbsVersionAttach > @VersionServer)
            OR
            (exists (SELECT 1 
                     FROM sys.databases d 
                     WHERE RTRIM(LTRIM(lower(d.name))) = RTRIM(LTRIM(lower(@dbs)))))
         BEGIN 
            PRINT ''
            PRINT ' Attach  ' + @dbs + ' abgebrochen! '
            PRINT ' Eventuelle Probleme : ' 
            PRINT '1. ' +  @dbs + ' DBS Version is grösser als die  Server Version.'
            PRINT '2. ' +  @dbs + ' DBS ist bereits am Server.'
            PRINT ''
         END 
         ELSE 
         BEGIN 
            EXEC sp_attach_single_file_db @dbname= @dbs , @physname = @phys_name
            PRINT ''
            PRINT 'Database "' + @dbs + '" attached Datei: ' + @Aktuell + '".'
            PRINT ''
            DELETE FROM @TabFileAttribiute
         END 

         FETCH NEXT FROM cf INTO @Aktuell

      END 
    
      CLOSE cf
      DEALLOCATE cf
   END TRY
   BEGIN CATCH
      PRINT 'Fehler bei Datei: ' + @phys_name + ''
      CLOSE cf
      DEALLOCATE cf
   END CATCH 

   SET NOCOUNT OFF
END 
GO
sp_configure 'xp_cmd',0
reconfigure
go

siehe auch Alle Datenbanken Detachen