Das Script erleichtert die Arbeit beim Verschieben von Datenbankfiles.
DECLARE @datafile VARCHAR(255) ,@logfile VARCHAR(255) ,@dbid TINYINT ,@SQLText VARCHAR(8000) ,@dbname VARCHAR(255) ,@SQLText2 VARCHAR(8000) --2. Detach All Local Databases and prepare for Attach IF EXISTS ( SELECT 1 FROM tempdb..sysobjects WHERE NAME LIKE '%#filetable%' ) BEGIN DROP TABLE #filetable END CREATE TABLE #filetable ( mdf VARCHAR(255) ,ldf VARCHAR(255) ,dbid TINYINT ,dbname VARCHAR(100) ,fileid TINYINT ) -- INSERT #filetable ( mdf ,dbid ,fileid ) SELECT physical_name ,database_id ,data_space_id FROM sys.master_files WHERE data_space_id = 1 INSERT #filetable ( ldf ,dbid ,fileid ) SELECT physical_name ,database_id ,data_space_id FROM sys.master_files WHERE data_space_id = 0 UPDATE u SET u.dbname = s.NAME FROM #filetable u INNER JOIN master..sysdatabases s ON u.dbid = s.dbid UPDATE #filetable SET mdf = replace(mdf, 'C:', 'D:') ,ldf = replace(ldf, 'C:', 'D:') FROM #filetable SELECT @dbid = min(dbid) FROM #filetable WHERE dbid > 4 WHILE @dbid IS NOT NULL BEGIN SELECT @SQLText = 'alter database [' + dbname + ']' FROM #filetable WHERE dbid = convert(VARCHAR, @dbid) SELECT @SQLText = @SQLText + CHAR(10) + ' set single_user with rollback immediate;' SELECT @SQLText = @SQLText + CHAR(10) + ' exec master..sp_detach_db ' + dbname FROM #filetable WHERE dbid = convert(VARCHAR, @dbid) PRINT @SQLText --Exec(@SQLText) SELECT @SQLText2 = 'exec master..sp_attach_db ''' + dbname + '''' FROM #filetable WHERE dbid = @dbid SELECT @SQLText2 = @SQLText2 + ',''' + mdf + '''' FROM #filetable WHERE dbid = @dbid AND mdf IS NOT NULL SELECT @SQLText2 = @SQLText2 + ',''' + ldf + '''' FROM #filetable WHERE dbid = @dbid AND ldf IS NOT NULL PRINT @SQLText2 --Exec(@SQLText) SELECT @dbid = min(dbid) FROM #filetable WHERE dbid > 4 AND dbid > @dbid END DROP TABLE #filetable