T-SQL::DB_Restore_move_to

--=============================================================

-- 장비 이동하거나 DB 복원시 파일을 다른 디렉토리로 보관해야할때

-- 파일들이 많은 DB의 경우 일일이 변경하기 어려움이 있어서 원본 기준으로 복원스크립트 생성하고

-- 디렉토리 경로와 백업 파일 경로만 넣어주면 되게끔 생성 한다.

--==============================================================

 

SET NOCOUNT ON

DECLARE @sql            nvarchar(max)

DECLARE @sql_move       nvarchar(3000)

DECLARE @move           nvarchar(200)

DECLARE @backup_type    char(1)

DECLARE @name           sysname

SET @backup_type = 'L' --N

SET @sql_move = ''

DECLARE cur_restore CURSOR FOR

    SELECT name FROM sys.databases WHERE database_id > 4 and NAME != 'LiteSpeedLocal' and state = 0

    ORDER BY name

 

OPEN cur_restore

FETCH NEXT FROM cur_restore

INTO @name

 

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @sql = NULL
    SET @sql_move = ''

    IF @backup_type = 'L'

    BEGIN

        SET @sql = 'exec master.dbo.xp_restore_database' + char(13)

                   + '@database = ''' + @name + '''' + char(13)

                   + ',@filename = ''''' + char(13)

                   + ',@filenumber = 1' + char(13)

                   + ',@with = ''REPLACE''' + char(13)

                   + ',@with = ''NORECOVERY''' + char(13)

 

 

        DECLARE cur_move CURSOR FOR

                SELECT ',@with = ''MOVE ''''' +

                            name + ''''' TO N''''' +

                            filename + ''''''' '

                FROM sys.sysaltfiles  WHERE dbid = db_id(@name)

                ORDER BY fileid

        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

 

  

    END

    ELSE IF @backup_type = 'N'

    BEGIN

        SET @sql = 'RESTORE DATABASE ' + @name + char(13)

                 + 'FROM DISK =''''' + char(13)

                 + 'WITH NORECOVERY' + char(13)

 

        DECLARE cur_move CURSOR FOR

                SELECT ',MOVE ''' + name + ''' TO  ''' + filename + ''''

                FROM sys.sysaltfiles  WHERE dbid = db_id(@name)

                ORDER BY fileid

        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

    END

 

    SET @sql = @sql + @sql_move + char(13)

 

    print @sql

 

    FETCH NEXT FROM cur_restore

    INTO @name

END

 

CLOSE cur_restore

DEALLOCATE cur_restore

출처 : http://ceusee.springnote.com/pages/2786844