Bei Migrationen kann man entweder mit Backup/Restore arbeiten, oder aber mit Detach ,Attach.
Das hier vorgestellte Script für für alle User Datenbanken die in einem bestimmten Verzeichnis den Attach durch:
BEGIN
DECLARE @AttachFromDir nvarchar(500) = 'E:\LZHD5DB_DATA' --- <-- 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
Download: