T-SQL::Attach

Attach 스크립트 만들기

 

 

--==========================================
--DB 복원
--==========================================
SET NOCOUNT ON
DECLARE @sql            nvarchar(max)
DECLARE @sql_move       nvarchar(max)
DECLARE @move           nvarchar(200)
DECLARE @dbid           int


SET @sql_move = ''
SET @sql = ''
DECLARE cur_restore CURSOR FOR
      SELECT dbid
            , 'CREATE DATABASE ' + name + ' ON' + char(13)
      FROM sys.sysdatabases WHERE dbid > 4 and NAME != 'LiteSpeedLocal' 
      ORDER BY name

OPEN cur_restore
FETCH NEXT FROM cur_restore
INTO @dbid, @sql_move

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @move = ''

        DECLARE cur_move CURSOR FOR
            
            select '(NAME = ''' + name + ''', FILENAME = ''' + filename + '''),' 
            from sys.sysaltfiles
            where dbid = @dbid

        OPEN cur_move
        FETCH NEXT FROM cur_move
        INTO @move


        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql_move = @sql_move + @move + char(13)

            FETCH NEXT FROM cur_move
            INTO @move
        END
        CLOSE cur_move
        DEALLOCATE cur_move


    SET @sql = @sql + @sql_move + 'FOR ATTACH' + char(13)
  --  select @sql
    print @sql
    set @sql = ''
    FETCH NEXT FROM cur_restore 
    INTO @dbid, @sql_move

END
CLOSE cur_restore
DEALLOCATE cur_restore