'backup'에 해당되는 글 3건

  1. 2009.03.04 MS Sql Server Backup Stored Procedure
  2. 2009.03.04 Backup databases stored procedure
  3. 2009.02.16 SQL Server 2005 Database Backup and Restore using C# and .NET 2.0
2009. 3. 4. 17:38

MS Sql Server Backup Stored Procedure

2009. 3. 4. 17:38 in SQL
출처 : http://www.sqlteam.com

----------------------------------------------------------------------------------------------------
-- OBJECT NAME      : isp_Backup
--
-- AUTHOR           : Tara Kizer
--
-- 수정일           : 2009년 3월 4일
-- 수정자           : 김민국
-- 수정 내역        : Microsoft Sql Server 2008 지원, compression 옵션 추가 
--
-- INPUTS           :   @path - 백업 파일 위치, null 인 경우에는 Default Backup Directory에 백업 파일 생성
--                      @dbType - 백업할 데이터베이스 유형 or '-' 옵션 시에는 백업할 데이터베이스명
--                        All, System, User, or dash followed by database name (ex. -Toolbox)
--                      @bkpType - 백업 타입
--                        Full, TLog, Diff
--                      @compression - 압축 여부, Enterprise, Developer Edition 에서만 사용 가능
--                        N, Y 
--                      @retention - 백업 파일 유지 기간(days), -1 옵션은 제한 없음.
--                      @liteSpeed - perform backup using LiteSpeed (Imceda product)
--                        N, Y
--
-- OUTPUTS              : None
--
-- RETURN CODES         : 0-10 (see @error table variable at the end for the messages)
--
-- DEPENDENCIES         : None
--
-- DESCRIPTION          : Performs backups.
--
-- EXAMPLES (optional)  : EXEC isp_Backup @path = 'C:\MSSQL\Backup\', @dbType = '-acct_bar', @bkpType = 'Full', @compression='N', @retention = 5, @liteSpeed = 'N'
--
-- Drop Proc            : Drop proc [dbo].[isp_Backup]
----------------------------------------------------------------------------------------------------
CREATE PROC [dbo].[isp_Backup]
(@path varchar(100), @dbType sysname = 'All', @bkpType char(4) = 'Full', @compression char(1) = 'N', @retention smallint = 2, @liteSpeed char(1) = 'N')
AS

SET NOCOUNT ON

DECLARE @now char(14)           -- current date in the form of yyyymmddhhmmss
DECLARE @dbName sysname         -- database name that is currently being processed
DECLARE @cmd nvarchar(4000)     -- dynamically created DOS command
DECLARE @result int             -- result of the dir DOS command
DECLARE @rowCnt int             -- @@ROWCOUNT
DECLARE @fileName varchar(200)  -- path and file name of the BAK file
DECLARE @edition int            -- edition of SQL Server (1 - Personal or Desktop Engine; 2 - Standard; 3 - Developer or Enterprise)
DECLARE @rc int                 -- return code
DECLARE @extension char(4)      -- extension for backup file
DECLARE @version varchar(2)     -- one or two digit version number, i.e. 8 (2000) or 9 (2005) or 10 (2008)

-- log shipping tables have been renamed in 2005
SET @version = CONVERT(varchar(2), SERVERPROPERTY('ProductVersion'))

IF @version NOT IN ('8', '9', '10')
BEGIN
    SET @rc = 1
    GOTO EXIT_ROUTINE
END

-- Enterprise and Developer editions have msdb.dbo.log_shipping* tables, other editions do not
SET @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))
-- Developer or Enterprise Edition이 아닌 경우에는 @compression을 'N'으로 설정
IF @compression ='Y' AND @edition <> 3
BEGIN
    --SET @compression = 'N'
    SET @rc = 12
    GOTO EXIT_ROUTINE
END

-- validate input parameters
IF @dbType IS NOT NULL AND @dbType NOT IN ('All', 'System', 'User') AND @dbType NOT LIKE '-%'
BEGIN
    SET @rc = 2
    GOTO EXIT_ROUTINE
END

IF @dbType LIKE '-%' AND @version = '8'
BEGIN
    IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
    BEGIN
        SET @rc = 3
        GOTO EXIT_ROUTINE
    END
END
ELSE IF @dbType LIKE '-%' AND ( @version = '9' or @version = '10' )
BEGIN
    IF NOT EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
    BEGIN
        SET @rc = 3
        GOTO EXIT_ROUTINE
    END
END

IF @bkpType IS NOT NULL AND @bkpType NOT IN ('Full', 'TLog', 'Diff')
BEGIN
    SET @rc = 4
    GOTO EXIT_ROUTINE
END

IF @dbType = 'System' AND @bkpType <> 'Full'
BEGIN
    SET @rc = 5
    GOTO EXIT_ROUTINE
END

IF @liteSpeed IS NOT NULL AND @liteSpeed NOT IN ('N', 'Y')
BEGIN
    SET @rc = 6
    GOTO EXIT_ROUTINE
END

-- use the default backup directory if @path is null
IF @path IS NULL
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @path output, 'no_output'

-- we need the backslash after the path, so add it if it wasn't provided in the input parameter
IF RIGHT(@path, 1) <> '\'
    SET @path = @path + '\'

CREATE TABLE #WhichDatabase(dbName SYSNAME NOT NULL)

-- put the databases to be backed up into temp table
IF @dbType LIKE '-%'
BEGIN
    IF @bkpType = 'TLog' AND DATABASEPROPERTYEX(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)), 'RECOVERY') = 'SIMPLE'
    BEGIN
        SET @rc = 7
        GOTO EXIT_ROUTINE
    END
    
    IF @edition = 3
    BEGIN
        IF @version = '8'
        BEGIN
            IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases WHERE database_name = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
            BEGIN
                SET @rc = 8
                GOTO EXIT_ROUTINE
            END
        END
        ELSE IF @version = '9' or @version = '10'
        BEGIN
            IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
            BEGIN
                SET @rc = 8
                GOTO EXIT_ROUTINE
            END
        END
    END

    IF @version = '9' or @version ='10'
    BEGIN
        IF EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)) AND source_database_id IS NOT NULL)
        BEGIN
            SET @rc = 11
            GOTO EXIT_ROUTINE
        END
    END

    INSERT INTO #WhichDatabase(dbName)
    VALUES(SUBSTRING(@dbType, 2, DATALENGTH(@dbType))) 
END
ELSE IF @dbType = 'All' 
BEGIN
    IF @edition = 3 AND @version = '8'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE 
            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND
            [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE IF @edition = 3 AND ( @version = '9' or @version ='10' )
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE 
            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND
            [name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE IF @version = '8'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE 
            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE -- version is 9
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE 
            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
END
ELSE IF @dbType = 'System'
BEGIN
    IF @version = 8
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE [name] IN ('master', 'model', 'msdb')
        ORDER BY [name]
    ELSE
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE [name] IN ('master', 'model', 'msdb')
        ORDER BY [name]
END
ELSE IF @dbType = 'User'
BEGIN
    IF @edition = 3 AND @version = '8'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE 
            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
            [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE IF @edition = 3 AND ( @version = '9' or @version = '10' )
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE 
            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
            [name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE IF @version = '8'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE 
            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE 
            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
END
ELSE -- no databases to be backed up
BEGIN
    SET @rc = 9
    GOTO EXIT_ROUTINE
END

-- Remove snapshots
IF @version = '9' or @version = '10'
    DELETE t
    FROM #WhichDatabase t 
    INNER JOIN master.sys.databases d
    ON t.dbName = d.[name]
    WHERE d.source_database_id IS NOT NULL

-- Get the database to be backed up
SELECT TOP 1 @dbName = dbName
FROM #WhichDatabase

SET @rowCnt = @@ROWCOUNT

-- Iterate throught the temp table until no more databases need to be backed up
WHILE @rowCnt <> 0
BEGIN 

    IF @bkpType = 'TLog' AND @dbType IN ('All', 'User') AND DATABASEPROPERTYEX(@dbName, 'RECOVERY') = 'SIMPLE'
        PRINT 'Skipping transaction log backup of ' + @dbName
    ELSE IF @bkpType = 'Diff' AND @dbName IN ('master', 'model', 'msdb')
        PRINT 'Skipping differential backup of ' + @dbName
    ELSE
    BEGIN
        -- Build the dir command that will check to see if the directory exists
        SET @cmd = 'dir ' + @path + @dbName
    
        -- Run the dir command, put output of xp_cmdshell into @result
        EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT
    
        -- If the directory does not exist, we must create it
        IF @result <> 0
        BEGIN
            -- Build the mkdir command        
            SET @cmd = 'mkdir ' + @path + @dbName
    
            -- Create the directory
            EXEC master..xp_cmdshell @cmd, NO_OUTPUT
    
            IF @@ERROR <> 0
            BEGIN
                SET @rc = 10
                GOTO EXIT_ROUTINE
            END
        END
        -- The directory exists, so let's delete files older than two days
        ELSE IF @retention <> -1
        BEGIN
            -- Stores the name of the file to be deleted
            DECLARE @whichFile VARCHAR(1000)
    
            CREATE TABLE #DeleteOldFiles(DirInfo VARCHAR(7000))
    
            -- Build the command that will list out all of the files in a directory
            SELECT @cmd = 'dir ' + @path + @dbName + ' /OD'
    
            -- Run the dir command and put the results into a temp table
            INSERT INTO #DeleteOldFiles
            EXEC master..xp_cmdshell @cmd
    
            -- Delete all rows from the temp table except the ones that correspond to the files to be deleted
            DELETE FROM #DeleteOldFiles
            WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @retention
    
            -- Get the file name portion of the row that corresponds to the file to be deleted
            SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
            FROM #DeleteOldFiles        
    
            SET @rowCnt = @@ROWCOUNT
            
            -- Interate through the temp table until there are no more files to delete
            WHILE @rowCnt <> 0
            BEGIN
                -- Build the del command
                SELECT @cmd = 'del ' + @path + + @dbName + '\' + @whichFile + ' /Q /F'
                
                -- Delete the file
                EXEC master..xp_cmdshell @cmd, NO_OUTPUT
                
                -- To move to the next file, the current file name needs to be deleted from the temp table
                DELETE FROM #DeleteOldFiles
                WHERE SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))  = @whichFile
    
                -- Get the file name portion of the row that corresponds to the file to be deleted
                SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
                FROM #DeleteOldFiles
            
                SET @rowCnt = @@ROWCOUNT
            END
            DROP TABLE #DeleteOldFiles
        END
        -- Get the current date using style 120, remove all dashes, spaces, and colons
        SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
    
        SET @extension =
            CASE
                WHEN @bkpType = 'Full' THEN '.BAK'
                WHEN @bkpType = 'TLog' THEN '.TRN'
                ELSE '.DIF'
            END
    
        -- Build the backup path and file name, backup the database
        IF @liteSpeed = 'N'
        BEGIN
            SET @fileName = @path + @dbName + '\' + @dbName + '_' + @now + @extension
            IF @compression = 'N'
            BEGIN 
                IF @bkpType = 'FULL'
                    BACKUP DATABASE @dbName
                    TO DISK = @filename
                    WITH INIT
                ELSE IF @bkpType = 'DIFF'
                    BACKUP DATABASE @dbName
                    TO DISK = @filename
                    WITH INIT, DIFFERENTIAL
                ELSE
                    BACKUP LOG @dbName
                    TO DISK = @filename
                    WITH INIT    
            END
            ELSE --@compression = 'Y'
            BEGIN 
                IF @bkpType = 'FULL'
                    BACKUP DATABASE @dbName
                    TO DISK = @filename
                    WITH INIT, COMPRESSION
                ELSE IF @bkpType = 'DIFF'
                    BACKUP DATABASE @dbName
                    TO DISK = @filename
                    WITH INIT, DIFFERENTIAL, COMPRESSION
                ELSE
                    BACKUP LOG @dbName
                    TO DISK = @filename
                    WITH INIT    
            END 
        END
        ELSE
        BEGIN
            SET @fileName = @path + @dbName + '\' + @dbName + '_LS_' + @now + @extension
    
            DECLARE @regOutput varchar(20) -- stores the output from the registry
            DECLARE @numProcs INT -- stores the number of processors that the server has registered

            -- Get the number of processors that the server has
            EXEC master..xp_regread 
                  @rootkey = 'HKEY_LOCAL_MACHINE', 
                  @key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
                  @value_name = 'NUMBER_OF_PROCESSORS',
                  @value = @regOutput OUTPUT
            
            --  We want n - 1 threads, where n is the number of processors
            SET @numProcs = CONVERT(int, @regOutput) - 1
    
            IF @bkpType = 'FULL'
                EXEC master.dbo.xp_backup_database
                    @database = @dbName,
                    @filename = @fileName,
                    @threads = @numProcs,
                    @init = 1
            ELSE IF @bkpType = 'DIFF'
                EXEC master.dbo.xp_backup_database
                    @database = @dbName,
                    @filename = @fileName,
                    @threads = @numProcs,
                    @init = 1,
                    @with = 'DIFFERENTIAL'
            ELSE
                EXEC master.dbo.xp_backup_log
                    @database = @dbName,
                    @filename = @fileName,
                    @threads = @numProcs,
                    @init = 1
        END
    END
        -- To move onto the next database, the current database name needs to be deleted from the temp table
        DELETE FROM #WhichDatabase
        WHERE dbName = @dbName
    
        -- Get the database to be backed up
        SELECT TOP 1 @dbName = dbName
        FROM #WhichDatabase
    
        SET @rowCnt = @@ROWCOUNT
        
        -- Let the system rest for 5 seconds before starting on the next backup
        WAITFOR DELAY '00:00:05'
END

SET @rc = 0

EXIT_ROUTINE:

IF @rc <> 0
BEGIN
    DECLARE @rm varchar(500)
    DECLARE @error table (returnCode int PRIMARY KEY CLUSTERED, returnMessage varchar(500))

    INSERT INTO @error(returnCode, returnMessage)
    SELECT  0, 'Success' UNION ALL
    SELECT  1, 'Version is not 2000 or 2005' UNION ALL
    SELECT  2, 'Invalid option passed to @dbType' UNION ALL
    SELECT  3, 'Database passed to @dbType does not exist' UNION ALL
    SELECT  4, 'Invalid option passed to @bkpType' UNION ALL
    SELECT  5, 'Only full backups are allowed on system databases' UNION ALL
    SELECT  6, 'Invalid option passed to @liteSpeed' UNION ALL
    SELECT  7, 'Can not backup tlog when using SIMPLE recovery model' UNION ALL
    SELECT  8, 'Will not backup the tlog on a log shipped database' UNION ALL
    SELECT  9, 'No databases to be backed up' UNION ALL
    SELECT 10, 'Unable to create directory' UNION ALL
    SELECT 11, 'Can not backup database snapshots' UNION ALL
    SELECT 12, 'Developer 또는 Enterprise Edition이 아닌 경우에는 compression 옵션을 사용할 수 없습니다.'

    SELECT @rm = returnMessage 
    FROM @error 
    WHERE returnCode = @rc

    RAISERROR(@rm, 16, 1)
END

RETURN @rc


저작자표시 비영리 동일조건
Posted by 막동이
2009. 3. 4. 15:34

Backup databases stored procedure

2009. 3. 4. 15:34 in SQL
출처 : http://weblogs.sqlteam.com/tarad/archive/2008/05/21/60606.aspx

Backup databases stored procedure - new version

I have updated my backup stored procedure, isp_Backup, as the old version had two issues. It did not exclude database snapshots, which can't be backed up. It also was not checking for the number of CPUs in the registry properly. The CPU check only affects backups when SQL Litespeed is used.

In the next version, I will exclude those databases that are being log shipped by SQL Litespeed as we don't want to interfere with the LSNs. I am already checking for this condition for native backups. I will also add code to perform a full backup when it is performing a differential or transaction log backup and a full backup does not exist. This will be done to avoid backup errors. 

----------------------------------------------------------------------------------------------------  

-- OBJECT NAME : isp_Backup  

--  

-- AUTHOR          : Tara Kizer  

--  

-- 수정일          : 2009년 3월 4일  

-- 수정자          : 김민국  

-- 수정 내역       : sql server 2008 지원, compression 옵션 추가   

--  

-- INPUTS           :   @path - 백업 파일 위치, null 인 경우에는 Default Backup Directory에 백업 파일 생성  

--                      @dbType - 백업할 데이터베이스 유형 or '-' 옵션 시에는 백업할 데이터베이스명  

--                        All, System, User, or dash followed by database name (ex. -Toolbox)  

--                      @bkpType - 백업 타입  

--                        Full, TLog, Diff  

--                      @compression - 압축 여부, Enterprise, Developer Edition 에서만 사용 가능  

--                        N, Y   

--                      @retention - 백업 파일 유지 기간(days), -1 옵션은 제한 없음.  

--                      @liteSpeed - perform backup using LiteSpeed (Imceda product)  

--                        N, Y  

--  

-- OUTPUTS              : None  

--  

-- RETURN CODES         : 0-10 (see @error table variable at the end for the messages)  

--  

-- DEPENDENCIES         : None  

--  

-- DESCRIPTION          : Performs backups.  

--  

-- EXAMPLES (optional)  : EXEC isp_Backup @path = 'C:\MSSQL\Backup\', @dbType = '-acct_bar', @bkpType = 'Full', @compression='N', @retention = 5, @liteSpeed = 'N'  

--  

-- Drop Proc            : Drop proc [dbo].[isp_Backup]  

----------------------------------------------------------------------------------------------------  

CREATE PROC [dbo].[isp_Backup]  

(@path varchar(100), @dbType sysname = 'All', @bkpType char(4) = 'Full', @compression char(1) = 'N', @retention smallint = 2, @liteSpeed char(1) = 'N')  

AS  

  

SET NOCOUNT ON  

  

DECLARE @now char(14)           -- current date in the form of yyyymmddhhmmss  

DECLARE @dbName sysname         -- database name that is currently being processed  

DECLARE @cmd nvarchar(4000)     -- dynamically created DOS command  

DECLARE @result int             -- result of the dir DOS command  

DECLARE @rowCnt int             -- @@ROWCOUNT  

DECLARE @fileName varchar(200)  -- path and file name of the BAK file  

DECLARE @edition int            -- edition of SQL Server (1 - Personal or Desktop Engine; 2 - Standard; 3 - Developer or Enterprise)  

DECLARE @rc int                 -- return code  

DECLARE @extension char(4)      -- extension for backup file  

DECLARE @version varchar(2)     -- one or two digit version number, i.e. 8 (2000) or 9 (2005) or 10 (2008)  

  

-- log shipping tables have been renamed in 2005  

SET @version = CONVERT(varchar(2), SERVERPROPERTY('ProductVersion'))  

  

IF @version NOT IN ('8', '9', '10')  

BEGIN  

    SET @rc = 1  

    GOTO EXIT_ROUTINE  

END  

  

-- Enterprise and Developer editions have msdb.dbo.log_shipping* tables, other editions do not  

SET @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))  

-- Developer or Enterprise Edition이 아닌 경우에는 @compression을 'N'으로 설정  

IF @compression ='Y' AND @edition <> 3  

BEGIN  

    --SET @compression = 'N'  

    SET @rc = 12  

    GOTO EXIT_ROUTINE  

END  

  

-- validate input parameters  

IF @dbType IS NOT NULL AND @dbType NOT IN ('All', 'System', 'User') AND @dbType NOT LIKE '-%'  

BEGIN  

    SET @rc = 2  

    GOTO EXIT_ROUTINE  

END  

  

IF @dbType LIKE '-%' AND @version = '8'  

BEGIN  

    IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))  

    BEGIN  

        SET @rc = 3  

        GOTO EXIT_ROUTINE  

    END  

END  

ELSE IF @dbType LIKE '-%' AND ( @version = '9' or @version = '10' )  

BEGIN  

    IF NOT EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))  

    BEGIN  

        SET @rc = 3  

        GOTO EXIT_ROUTINE  

    END  

END  

  

IF @bkpType IS NOT NULL AND @bkpType NOT IN ('Full', 'TLog', 'Diff')  

BEGIN  

    SET @rc = 4  

    GOTO EXIT_ROUTINE  

END  

  

IF @dbType = 'System' AND @bkpType <> 'Full'  

BEGIN  

    SET @rc = 5  

    GOTO EXIT_ROUTINE  

END  

  

IF @liteSpeed IS NOT NULL AND @liteSpeed NOT IN ('N', 'Y')  

BEGIN  

    SET @rc = 6  

    GOTO EXIT_ROUTINE  

END  

  

-- use the default backup directory if @path is null  

IF @path IS NULL  

    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @path output, 'no_output'  

  

-- we need the backslash after the path, so add it if it wasn't provided in the input parameter  

IF RIGHT(@path, 1) <> '\'  

    SET @path = @path + '\'  

  

CREATE TABLE #WhichDatabase(dbName SYSNAME NOT NULL)  

  

-- put the databases to be backed up into temp table  

IF @dbType LIKE '-%'  

BEGIN  

    IF @bkpType = 'TLog' AND DATABASEPROPERTYEX(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)), 'RECOVERY') = 'SIMPLE'  

    BEGIN  

        SET @rc = 7  

        GOTO EXIT_ROUTINE  

    END  

      

    IF @edition = 3  

    BEGIN  

        IF @version = '8'  

        BEGIN  

            IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases WHERE database_name = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))  

            BEGIN  

                SET @rc = 8  

                GOTO EXIT_ROUTINE  

            END  

        END  

        ELSE IF @version = '9' or @version = '10'  

        BEGIN  

            IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))  

            BEGIN  

                SET @rc = 8  

                GOTO EXIT_ROUTINE  

            END  

        END  

    END  

  

    IF @version = '9' or @version ='10'  

    BEGIN  

        IF EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)) AND source_database_id IS NOT NULL)  

        BEGIN  

            SET @rc = 11  

            GOTO EXIT_ROUTINE  

        END  

    END  

  

    INSERT INTO #WhichDatabase(dbName)  

    VALUES(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))   

END  

ELSE IF @dbType = 'All'   

BEGIN  

    IF @edition = 3 AND @version = '8'  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.dbo.sysdatabases  

        WHERE   

            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND  

            [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND  

            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND  

            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'  

        ORDER BY [name]  

    ELSE IF @edition = 3 AND ( @version = '9' or @version ='10' )  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.sys.databases  

        WHERE   

            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND  

            [name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND  

            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND  

            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'  

        ORDER BY [name]  

    ELSE IF @version = '8'  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.dbo.sysdatabases  

        WHERE   

            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND  

            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND  

            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'  

        ORDER BY [name]  

    ELSE -- version is 9  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.sys.databases  

        WHERE   

            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND  

            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND  

            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'  

        ORDER BY [name]  

END  

ELSE IF @dbType = 'System'  

BEGIN  

    IF @version = 8  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.dbo.sysdatabases  

        WHERE [name] IN ('master', 'model', 'msdb')  

        ORDER BY [name]  

    ELSE  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.sys.databases  

        WHERE [name] IN ('master', 'model', 'msdb')  

        ORDER BY [name]  

END  

ELSE IF @dbType = 'User'  

BEGIN  

    IF @edition = 3 AND @version = '8'  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.dbo.sysdatabases  

        WHERE   

            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND  

            [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND  

            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND  

            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'  

        ORDER BY [name]  

    ELSE IF @edition = 3 AND ( @version = '9' or @version = '10' )  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.sys.databases  

        WHERE   

            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND  

            [name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND  

            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND  

            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'  

        ORDER BY [name]  

    ELSE IF @version = '8'  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.dbo.sysdatabases  

        WHERE   

            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND  

            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND  

            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'  

        ORDER BY [name]  

    ELSE  

        INSERT INTO #WhichDatabase (dbName)  

        SELECT [name]  

        FROM master.sys.databases  

        WHERE   

            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND  

            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND  

            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'  

        ORDER BY [name]  

END  

ELSE -- no databases to be backed up  

BEGIN  

    SET @rc = 9  

    GOTO EXIT_ROUTINE  

END  

  

-- Remove snapshots  

IF @version = '9' or @version = '10'  

    DELETE t  

    FROM #WhichDatabase t   

    INNER JOIN master.sys.databases d  

    ON t.dbName = d.[name]  

    WHERE d.source_database_id IS NOT NULL  

  

-- Get the database to be backed up  

SELECT TOP 1 @dbName = dbName  

FROM #WhichDatabase  

  

SET @rowCnt = @@ROWCOUNT  

  

-- Iterate throught the temp table until no more databases need to be backed up  

WHILE @rowCnt <> 0  

BEGIN   

  

    IF @bkpType = 'TLog' AND @dbType IN ('All', 'User') AND DATABASEPROPERTYEX(@dbName, 'RECOVERY') = 'SIMPLE'  

        PRINT 'Skipping transaction log backup of ' + @dbName  

    ELSE IF @bkpType = 'Diff' AND @dbName IN ('master', 'model', 'msdb')  

        PRINT 'Skipping differential backup of ' + @dbName  

    ELSE  

    BEGIN  

        -- Build the dir command that will check to see if the directory exists  

        SET @cmd = 'dir ' + @path + @dbName  

      

        -- Run the dir command, put output of xp_cmdshell into @result  

        EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT  

      

        -- If the directory does not exist, we must create it  

        IF @result <> 0  

        BEGIN  

            -- Build the mkdir command          

            SET @cmd = 'mkdir ' + @path + @dbName  

      

            -- Create the directory  

            EXEC master..xp_cmdshell @cmd, NO_OUTPUT  

      

            IF @@ERROR <> 0  

            BEGIN  

                SET @rc = 10  

                GOTO EXIT_ROUTINE  

            END  

        END  

        -- The directory exists, so let's delete files older than two days  

        ELSE IF @retention <> -1  

        BEGIN  

            -- Stores the name of the file to be deleted  

            DECLARE @whichFile VARCHAR(1000)  

      

            CREATE TABLE #DeleteOldFiles(DirInfo VARCHAR(7000))  

      

            -- Build the command that will list out all of the files in a directory  

            SELECT @cmd = 'dir ' + @path + @dbName + ' /OD'  

      

            -- Run the dir command and put the results into a temp table  

            INSERT INTO #DeleteOldFiles  

            EXEC master..xp_cmdshell @cmd  

      

            -- Delete all rows from the temp table except the ones that correspond to the files to be deleted  

            DELETE FROM #DeleteOldFiles  

            WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @retention  

      

            -- Get the file name portion of the row that corresponds to the file to be deleted  

            SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))   

            FROM #DeleteOldFiles          

      

            SET @rowCnt = @@ROWCOUNT  

              

            -- Interate through the temp table until there are no more files to delete  

            WHILE @rowCnt <> 0  

            BEGIN  

                -- Build the del command  

                SELECT @cmd = 'del ' + @path + + @dbName + '\' + @whichFile + ' /Q /F'  

                  

                -- Delete the file  

                EXEC master..xp_cmdshell @cmd, NO_OUTPUT  

                  

                -- To move to the next file, the current file name needs to be deleted from the temp table  

                DELETE FROM #DeleteOldFiles  

                WHERE SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))  = @whichFile  

      

                -- Get the file name portion of the row that corresponds to the file to be deleted  

                SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))   

                FROM #DeleteOldFiles  

              

                SET @rowCnt = @@ROWCOUNT  

            END  

            DROP TABLE #DeleteOldFiles  

        END  

        -- Get the current date using style 120, remove all dashes, spaces, and colons  

        SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')  

      

        SET @extension =  

            CASE  

                WHEN @bkpType = 'Full' THEN '.BAK'  

                WHEN @bkpType = 'TLog' THEN '.TRN'  

                ELSE '.DIF'  

            END  

      

        -- Build the backup path and file name, backup the database  

        IF @liteSpeed = 'N'  

        BEGIN  

            SET @fileName = @path + @dbName + '\' + @dbName + '_' + @now + @extension  

            IF @compression = 'N'  

            BEGIN   

                IF @bkpType = 'FULL'  

                    BACKUP DATABASE @dbName  

                    TO DISK = @filename  

                    WITH INIT  

                ELSE IF @bkpType = 'DIFF'  

                    BACKUP DATABASE @dbName  

                    TO DISK = @filename  

                    WITH INIT, DIFFERENTIAL  

                ELSE  

                    BACKUP LOG @dbName  

                    TO DISK = @filename  

                    WITH INIT      

            END  

            ELSE --@compression = 'Y'  

            BEGIN   

                IF @bkpType = 'FULL'  

                    BACKUP DATABASE @dbName  

                    TO DISK = @filename  

                    WITH INIT, COMPRESSION  

                ELSE IF @bkpType = 'DIFF'  

                    BACKUP DATABASE @dbName  

                    TO DISK = @filename  

                    WITH INIT, DIFFERENTIAL, COMPRESSION  

                ELSE  

                    BACKUP LOG @dbName  

                    TO DISK = @filename  

                    WITH INIT      

            END   

        END  

        ELSE  

        BEGIN  

            SET @fileName = @path + @dbName + '\' + @dbName + '_LS_' + @now + @extension  

      

            DECLARE @regOutput varchar(20) -- stores the output from the registry  

            DECLARE @numProcs INT -- stores the number of processors that the server has registered  

  

            -- Get the number of processors that the server has  

            EXEC master..xp_regread   

                  @rootkey = 'HKEY_LOCAL_MACHINE',   

                  @key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',  

        @value_name = 'NUMBER_OF_PROCESSORS',  

                  @value = @regOutput OUTPUT  

              

            --  We want n - 1 threads, where n is the number of processors  

            SET @numProcs = CONVERT(int, @regOutput) - 1  

      

            IF @bkpType = 'FULL'  

                EXEC master.dbo.xp_backup_database  

                    @database = @dbName,  

                    @filename = @fileName,  

                    @threads = @numProcs,  

                    @init = 1  

            ELSE IF @bkpType = 'DIFF'  

                EXEC master.dbo.xp_backup_database  

                    @database = @dbName,  

                    @filename = @fileName,  

                    @threads = @numProcs,  

                    @init = 1,  

                    @with = 'DIFFERENTIAL'  

            ELSE  

                EXEC master.dbo.xp_backup_log  

                    @database = @dbName,  

                    @filename = @fileName,  

                    @threads = @numProcs,  

                    @init = 1  

        END  

    END  

        -- To move onto the next database, the current database name needs to be deleted from the temp table  

        DELETE FROM #WhichDatabase  

        WHERE dbName = @dbName  

      

        -- Get the database to be backed up  

        SELECT TOP 1 @dbName = dbName  

        FROM #WhichDatabase  

      

        SET @rowCnt = @@ROWCOUNT  

          

        -- Let the system rest for 5 seconds before starting on the next backup  

        WAITFOR DELAY '00:00:05'  

END  

  

SET @rc = 0  

  

EXIT_ROUTINE:  

  

IF @rc <> 0  

BEGIN  

    DECLARE @rm varchar(500)  

    DECLARE @error table (returnCode int PRIMARY KEY CLUSTERED, returnMessage varchar(500))  

  

    INSERT INTO @error(returnCode, returnMessage)  

    SELECT  0, 'Success' UNION ALL  

    SELECT  1, 'Version is not 2000 or 2005' UNION ALL  

    SELECT  2, 'Invalid option passed to @dbType' UNION ALL  

    SELECT  3, 'Database passed to @dbType does not exist' UNION ALL  

    SELECT  4, 'Invalid option passed to @bkpType' UNION ALL  

    SELECT  5, 'Only full backups are allowed on system databases' UNION ALL  

    SELECT  6, 'Invalid option passed to @liteSpeed' UNION ALL  

    SELECT  7, 'Can not backup tlog when using SIMPLE recovery model' UNION ALL  

    SELECT  8, 'Will not backup the tlog on a log shipped database' UNION ALL  

    SELECT  9, 'No databases to be backed up' UNION ALL  

    SELECT 10, 'Unable to create directory' UNION ALL  

    SELECT 11, 'Can not backup database snapshots' UNION ALL  

    SELECT 12, 'Developer 또는 Enterprise Edition이 아닌 경우에는 compression 옵션을 사용할 수 없습니다.'  

  

    SELECT @rm = returnMessage   

    FROM @error   

    WHERE returnCode = @rc  

  

    RAISERROR(@rm, 16, 1)  

END  

  

RETURN @rc 

저작자표시 비영리 동일조건
Posted by 막동이
2009. 2. 16. 09:59

SQL Server 2005 Database Backup and Restore using C# and .NET 2.0

2009. 2. 16. 09:59 in MS Dev./KB
출처 : http://www.codeproject.com/KB/database/SQL_Server_2005_Database.aspx

Introduction

The following article describes accessing a SQL Server 2005 database backup and restoring it programmatically using C#.NET 2.0 and SMO. This article provides coding samples to perform the task.

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

The following namespaces can be used to access SQL Server 2005 programmatically:

  • Microsoft.SqlServer.management
  • Microsoft.SqlServer.Management.NotificationServices
  • Microsoft.SqlServer.Management.Smo
  • Microsoft.SqlServer.Management.Smo.Agent
  • Microsoft.SqlServer.Management.Smo.Broker
  • Microsoft.SqlServer.Management.Smo.Mail
  • Microsoft.SqlServer.Management.Smo.RegisteredServers
  • Microsoft.SqlServer.Management.Smo.Wmi
  • Microsoft.SqlServer.Management.Trace

Pre-Requisite

You need to reference the following namespaces before using this code:

  • Microsoft.SqlServer.Management.Smo;
  • Microsoft.SqlServer.Management.Common;

I used these two class to perform the backup and restore operations:

  • Microsoft.SqlServer.Management.Smo.Backup
  • Microsoft.SqlServer.Management.Smo.Restore

For more information, regarding these two class, check MSDN:

Backup database

Collapse Copy Code
public void BackupDatabase(String databaseName, String userName, 
            String password, String serverName, String destinationPath)
{
    Backup sqlBackup = new Backup();
    
    sqlBackup.Action = BackupActionType.Database;
    sqlBackup.BackupSetDescription = "ArchiveDataBase:" + 
                                     DateTime.Now.ToShortDateString();
    sqlBackup.BackupSetName = "Archive";

    sqlBackup.Database = databaseName;

    BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);
    
    Database db = sqlServer.Databases[databaseName];
    
    sqlBackup.Initialize = true;
    sqlBackup.Checksum = true;
    sqlBackup.ContinueAfterError = true;
    
    sqlBackup.Devices.Add(deviceItem);
    sqlBackup.Incremental = false;

    sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
    sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

    sqlBackup.FormatMedia = false;

    sqlBackup.SqlBackup(sqlServer);
}

Restore Database

Collapse Copy Code
public void RestoreDatabase(String databaseName, String filePath, 
       String serverName, String userName, String password, 
       String dataFilePath, String logFilePath)
{
    Restore sqlRestore = new Restore();
    
    BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
    sqlRestore.Devices.Add(deviceItem);
    sqlRestore.Database = databaseName;

    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);

    Database db = sqlServer.Databases[databaseName];
    sqlRestore.Action = RestoreActionType.Database;
    String dataFileLocation = dataFilePath + databaseName + ".mdf";
    String logFileLocation = logFilePath + databaseName + "_Log.ldf";
    db = sqlServer.Databases[databaseName];
    RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
    
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName+"_log", logFileLocation));
    sqlRestore.ReplaceDatabase = true;
    sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
    sqlRestore.PercentCompleteNotification = 10;
    sqlRestore.PercentComplete += 
       new PercentCompleteEventHandler(sqlRestore_PercentComplete);
            
    sqlRestore.SqlRestore(sqlServer);
    db = sqlServer.Databases[databaseName];
    db.SetOnline();
    sqlServer.Refresh();
}

The portion of code uses full backup features. If you want, you can perform incremental and differential backup as well.

Updates: June 8, 2008

In order to use this code, your SQL Server authentication mode needs to be configured as Mixed Mode authentication. If you use Windows Authentication, then you need to modify the ServerConnection:

Collapse Copy Code
SqlConnection sqlCon = new SqlConnection ("Data Source=Bappi; Integrated Security=True;");
ServerConnection connection = new ServerConnection(sqlCon);

Modify the ServerConnection portion of both code samples using this code in order to use Windows Security.

Conclusion

As this code uses the SQL Server 2005 backup/restore facility, the code follows the rules of SQL Server 2005 while backing up or restoring databases.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Posted by 막동이
prev Prev 1 Next next

티스토리툴바