برای مدیریت کامل پایگاه داده ها و پشتیبان گیری پایگاه داده ها ، کپی کردن در شبکه و حذف آن ها تنها از روش stored procedure استفاده می شود.در این مقاله قصد داریم در مورد این روش ها صحبت نماییم.برای درک بهتر موضوع مقاله را مطالعه نمایید.

در این مقاله می خواهیم در مورد مدیریت پشتیبان گیری پایگاه داده صحبت نماییم و تنها روش انجام آن هم استفاده از روش stored procedure است.

این procedure ها که استفاده می شوند با دو سرور و یک شبکه از سرور و یک pc مدیریت می شوند.

اهداف این سیستم عبارت اند از :

1-انجام پشتیبان گیری پایگاه داده

2-کپی پشتیبان گیری بین هر سرور

3-کپی پشتیبان گیری بر روی کامپیوتر دیگر در همان شبکه

4-حذف پشتیبان گیری قدیمی تر از n روز

برای فعال کردن command shell می توانید از کوئری زیر استفاده کنید:

EXEC sp_configure 'show advanced options', 1

GO

EXEC sp_configure'xp_cmdshell', 1

GO

این سیستم بر اساس 3 روش مختلف است:

usp_DatabaseBackup_CheckFolderPath :چک می کند که آیا stored procedure ذخیره شده در مسیر ورودی وجود داشته باشد یا نه؟

usp_DatabaseBackUp_CleanUp:تمام فایل ها از یک مسیر خاص حذف می شوند.

usp_DatabaseBackup_Main:انجام پشتیبان گیری محلی، و استفاده از دو روش دیگر برای کپی کردن و حذف پشتیبان گیری از راه های دیگر

1-usp_DatabaseBackup_CheckFolderPath:

این دستور در نتیجه مجموعه ای با تمام فایل ها و پوشه های موجود در یک مسیر خاص را بر می گرداند، فایل ها با زمینه ی isFile=1 و پوشه ها با زمینه ی isFile=0 مشخص شده اند.

پارامتر ورودی Folder_Path مسیر کامل یک پوشه است.

به صورت زیر:

@Folder_Path ex.: "C:\Folder1\Backup"

زمانی که فایل backup در پوشه ی پشتیبان گیری مقصد است، stored procedure آخرین پوشه را از این مسیر و با استفاده از xp_dirtree در مسیر resault به دنبال آخرین نام پوشه ی مشخص شده با isfile=0 است.

CREATE PROCEDURE [dbo].[usp_DatabaseBackup_CheckFolderPath]
@Folder_Path VARCHAR(1000),
@FolderExist INT OUTPUT
AS


iF @Folder_Path LIKE '%' + '\'
   SET @Folder_Path = substring(@Folder_Path, 1, len(@Folder_Path) - len('\'))


   --Create a temp table to hold the results.


IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
      DROP TABLE #DirectoryTree;


CREATE TABLE #DirectoryTree (
       id INT IDENTITY(1,1)
      ,fullpath VARCHAR(2000)
      ,subdirectory NVARCHAR(512)
      ,depth INT
      ,isfile BIT);


DECLARE @LastFolder AS NVARCHAR(255),@SemiPath as nvarchar(255)
SET @LastFolder = REVERSE(SUBSTRING(REVERSE(@Folder_Path),0,CHARINDEX('\',REVERSE(@Folder_Path))))
set @SemiPath =  REPLACE(@Folder_Path,@LastFolder,'')


INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @SemiPath,1,1;


DECLARE @RETVAL AS INT = 0


SELECT  @FolderExist = 1
FROM #DirectoryTree
WHERE isfile = 0 AND subdirectory = @LastFolder


RETURN


GO

2-usp_DatabaseBackUp_CleanUp

برای نام گذاری فایل های پشتیبان با استفاده از این روش:

YYYYMMDD_SERVERNAME_DBVersion_DatabaseName

برای نام گذاری این فایل ها باید از تاریخ انقضای فایل های backup و بعد ورژن پایگاه داده و بعد نام دیتابیس خود را اضافه نماییم.

GO
CREATE PROCEDURE [dbo].[usp_DatabaseBackUp_Cleanup]
@Folder_Path VARCHAR(1000),
@Exiration_Days INT
AS


DECLARE  @Path VARCHAR(1000)
  ,@FullPath VARCHAR(2000)
  ,@Id INT;


--Create a temp table to hold the results.
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
      DROP TABLE #DirectoryTree;


CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,fullpath varchar(2000)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);


IF OBJECT_ID('tempdb..#BackUpTable') IS NOT NULL
    DROP TABLE #BackUpTable


  CREATE TABLE #BackUpTable (
    Id int identity(1,1),
 FileDate Datetime,
 Name varchar(1000),
 FullPath varchar(8000),
   )


--Populate the table using the initial base path.
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @Folder_Path,1,1;


UPDATE #DirectoryTree SET fullpath = @Folder_Path;


--Loop through the table as long as there are still folders to process.
WHILE EXISTS (SELECT id FROM #DirectoryTree WHERE isfile = 0)
BEGIN
      --Select the first row that is a folder.
      SELECT TOP (1)
             @Id = id
            ,@FullPath = fullpath
            ,@Path = @Folder_Path + '\' + subdirectory
      FROM #DirectoryTree WHERE isfile = 0;


      IF @FullPath = @Path
      BEGIN
            --Do this section if the we are still in the same folder.
            INSERT #DirectoryTree (subdirectory,depth,isfile)
            EXEC master.sys.xp_dirtree @Path,1,1;


            UPDATE #DirectoryTree
            SET fullpath = @Path
            WHERE fullpath IS NULL;


            --Delete the processed folder.
            DELETE FROM #DirectoryTree WHERE id = @Id;
      END
      ELSE
      BEGIN
            --Do this section if we need to jump down into another subfolder.
            SET @Folder_Path = @FullPath;


            --Select the first row that is a folder.
            SELECT TOP (1)
                   @Id = id
                  ,@FullPath = fullpath
                  ,@Path = @Folder_Path + subdirectory
            FROM #DirectoryTree WHERE isfile = 0;


            INSERT #DirectoryTree (subdirectory,depth,isfile)
            EXEC master.sys.xp_dirtree @Path,1,1;


            UPDATE #DirectoryTree
            SET fullpath = @Path
            WHERE fullpath IS NULL;


            --Delete the processed folder.
            DELETE FROM #DirectoryTree WHERE id = @Id;
      END
END


--Output the results.
insert into #BackUpTable
SELECT CASE
  WHEN CHARINDEX('_',subdirectory) > 0 AND ISNUMERIC(SUBSTRING(subdirectory,0,CHARINDEX('_',subdirectory))) = 1 THEN CAST(SUBSTRING(subdirectory,0,CHARINDEX('_',subdirectory)) AS DATETIME)
  ELSE DATEADD(DD,DATEDIFF(DD,0,GETDATE()),0)
  END AS FileDate
  ,
  subdirectory,
  fullpath + '\' + subdirectory AS 'CompleteFileList'
FROM #DirectoryTree
WHERE subdirectory LIKE '%.bak'
ORDER BY fullpath,subdirectory;


DECLARE @CMDTOEXEC AS NVARCHAR(4000)
SET @CMDTOEXEC = ''


SELECT @CMDTOEXEC = @CMDTOEXEC + 'exec xp_cmdshell ''del "' + FullPath + '"'';'
FROM #BackUpTable
WHERE FileDate < DATEADD(DD,DATEDIFF(DD,@Exiration_Days,GETDATE()),0)


EXEC sp_executesql @CMDTOEXEC


--Cleanup.
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
      DROP TABLE #DirectoryTree;


IF OBJECT_ID('tempdb..#BackUpTable') IS NOT NULL
    DROP TABLE #BackUpTable;

3-usp_DatabaseBackup_Main

با استفاده از این procedure شما می توانید از دو روش دیگر برای انجام عمل پشتیبان گیری استفاده نمایید.

3 پارامتر وجود دارد:

1-@Server_Local_Path

یک مسیر محلی برای صرفه جویی در backup استفاده می شود.

2-@Backup_Expiration_Days

پاک کردن فایل های backup مثلا به صورت پیش فرض 10 روز

3-@Other_Network_Path

یک مسیر برای صرفه جویی فایل های پشتیبان گیری که به صورت پیش فرض خالی است.

CREATE PROCEDURE  [dbo].[usp_DatabaseBackup_Main] 
  @Server_Local_Path AS VARCHAR(1000),
  @Backup_Expiration_Days INT =10,
  @Other_Network_path AS NVARCHAR(4000) = ''
 AS 


 -- ******************************** Declare stored procedure variables **************************************
                                    
 DECLARE @dbName AS sysname 
 DECLARE @dbVers AS NVARCHAR(1000) 
 DECLARE @Date as DATETIME,@MESSAGE AS NVARCHAR(MAX)
 DECLARE @now AS DATETIME 
 DECLARE @nowStr AS VARCHAR(25)  
 DECLARE @backupFileFullPath AS VARCHAR(MAX) 
 DECLARE @serverName AS sysname 
 DECLARE @MirroredServerName AS VARCHAR(50)  
 DECLARE @mirroring_partner_instance  AS VARCHAR(50)
 DECLARE @Copy_cmd_From_Mirror_To_Principal AS VARCHAR(4000)
 DECLARE @Copy_cmd_From_Principal_To_Mirror AS VARCHAR(4000)
 DECLARE @CopyFromPrincipalToOtherCMD AS VARCHAR(4000)


 -- ******************************** Fill variable with correct value **************************************


 SELECT @mirroring_partner_instance = mirroring_partner_instance
 FROM sys.database_mirroring
 WHERE mirroring_partner_instance IS NOT NULL


 SET @MirroredServerName = SUBSTRING(@mirroring_partner_instance,0,CHARINDEX('\',@mirroring_partner_instance))


 SET @DATE = GETDATE()


 SELECT @dbName = DB_NAME() 
 SET @serverName = REPLACE(CONVERT(sysname, SERVERPROPERTY(N'servername'))  , '\', '$')


                                 
 SELECT @Other_Network_path = CASE
         WHEN SUBSTRING(@Other_Network_path,LEN(@Other_Network_path),LEN(@Other_Network_path)) = '\' THEN @Other_Network_path
         WHEN @Other_Network_path <> '' THEN @Other_Network_path + '\'
         ELSE @Other_Network_path
         END


 SELECT @dbVers = SUBSTRING(SUBSTRING(@@VERSION,CHARINDEX('-',@@VERSION)+2,LEN(@@VERSION)),0,CHARINDEX(' ',SUBSTRING(@@VERSION,CHARINDEX('-',@@VERSION)+2,LEN(@@VERSION))))
                                        
 set @Server_Local_Path = CASE
         WHEN SUBSTRING(@Server_Local_Path,LEN(@Server_Local_Path),LEN(@Server_Local_Path)) = '\' THEN @Server_Local_Path
         WHEN @Server_Local_Path <> '' THEN @Server_Local_Path + '\'
         ELSE @Server_Local_Path
        END                      
                                        
 SET @now = GETDATE() 
                                        
 SET @nowStr = CAST(DATEPART(yyyy, @now) AS VARCHAR) 
 IF DATEPART(mm, @now) < 10 SET @nowStr = @nowStr + '0' 
 SET @nowStr = @nowStr + CAST(DATEPART(mm, @now) AS VARCHAR) 
 IF DATEPART(dd, @now) < 10 SET @nowStr = @nowStr + '0' 
 SET @nowStr = @nowStr + CAST(DATEPART(dd, @now) AS VARCHAR) 
                                        
 SET @nowStr = @nowStr + '_' 
                                        
 IF DATEPART(hh, @now) < 10 SET @nowStr = @nowStr + '0' 
 SET @nowStr = @nowStr + CAST(DATEPART(hh, @now) AS VARCHAR) 
 IF DATEPART(mi, @now) < 10 SET @nowStr = @nowStr + '0' 
 SET @nowStr = @nowStr + CAST(DATEPART(mi, @now) AS VARCHAR) 
 IF DATEPART(ss, @now) < 10 SET @nowStr = @nowStr + '0' 
 SET @nowStr = @nowStr + CAST(DATEPART(ss, @now) AS VARCHAR) 
                                        
                                        
 
 -- ******************************** Perform backup **************************************
                       
 SET @backupFileFullPath = @Server_Local_Path  + @nowStr + '_' + @serverName + '_' +@dbVers+'_' + @dbName + '.bak'                                
       
 BACKUP DATABASE @dbName 
 TO DISK = @backupFileFullPath 
 WITH INIT 
                 
 PRINT 'DB ''' + @dbName + ''' backed up to ''' + @backupFileFullPath + ''' file.'


 -- set bak file full path
  SET @backupFileFullPath =  @Server_Local_Path  + @nowStr + '_' + @serverName + '_' +@dbVers+'_' + @dbName + ' [TRANLOG].bak'


 -- backup transaction log
 BACKUP LOG @dbName
 TO  DISK = @backupFileFullPath
 WITH   NOFORMAT
   , NOINIT
   , SKIP
   , STATS = 10;


 PRINT 'BackUp database at following path: ' + @Server_Local_Path


 -- ******************************** Check if all path exist **************************************


 DECLARE @Mirror_Network_Path AS VARCHAR(4000)
 SET @Mirror_Network_Path = '\\' + @MirroredServerName + '\' +REPLACE(@Server_Local_Path,':','$')
 
 DECLARE @Server_Local_Path_Exist AS INT,@Mirror_Network_Path_Exist AS INT,@Other_Network_Path_Exist AS INT


 EXECUTE [dbo].[usp_DatabaseBackup_CheckFolderPath] @Server_Local_Path,@FolderExist = @Server_Local_Path_Exist OUTPUT;


 EXECUTE [dbo].[usp_DatabaseBackup_CheckFolderPath] @Mirror_Network_Path,@FolderExist = @Mirror_Network_Path_Exist OUTPUT;


 EXECUTE [dbo].[usp_DatabaseBackup_CheckFolderPath] @Other_Network_path,@FolderExist = @Other_Network_Path_Exist OUTPUT;


 -- ******************************** Delete old dabatase on current path **************************************


 IF @Server_Local_Path <> '' AND @Server_Local_Path_Exist = 1
 BEGIN
  EXEC usp_DatabaseBackUp_Cleanup @Server_Local_Path,@Backup_Expiration_Days
 END
  -- ******************************** Delete old dabatase on other server path **************************************
 
  IF @Mirror_Network_Path <> '' AND @Mirror_Network_Path_Exist = 1
  BEGIN
   EXEC usp_DatabaseBackUp_Cleanup @Mirror_Network_Path,@Backup_Expiration_Days
   PRINT 'System clear up old bakcup files from following path: ' + ISNULL(@Mirror_Network_Path,'')


  END
  ELSE
  BEGIN
   PRINT 'Configuration key MirrorPath it''s not correctly setted or the path not exist. MirroPath calculated @Mirror_Network_Path: ' +  ISNULL(@Mirror_Network_Path,'')
  END


  PRINT 'COPY START'


  -- ******************************** Copy dabatase from principal server to mirror server *********************************
  
  BEGIN TRY


   SET @Copy_cmd_From_Mirror_To_Principal =  'xcopy "' + @Mirror_Network_Path + '*.bak" "' + @Server_Local_Path + '" /Y'
   EXECUTE xp_cmdshell @Copy_cmd_From_Mirror_To_Principal


   PRINT 'System copy backup from [' + @Mirror_Network_Path + '] to [' + @Server_Local_Path + ']'
   
   SET @Copy_cmd_From_Principal_To_Mirror = 'xcopy "'+ @Server_Local_Path + '*.bak" "' + @Mirror_Network_Path + '" /Y'
   EXECUTE xp_cmdshell @Copy_cmd_From_Principal_To_Mirror


   PRINT 'System copy backup from [' + @Server_Local_Path + '] to [' + @Mirror_Network_Path + ']'


  END TRY
  BEGIN CATCH
    PRINT 'Error Number = ' +  ERROR_NUMBER() + '; Error Message = ' + ERROR_MESSAGE()
  END CATCH;


  
  -- ******************************** Delete old dabatase on other external PC path *********************************


  -- if exernal path is not empty delete external old db files
  IF @Other_Network_path <> ''
  BEGIN
 
  --select @Other_Network_Path_Exist as ExternalPathExist
   IF @Other_Network_Path_Exist = 1
   BEGIN
   EXEC usp_DatabaseBackUp_Cleanup @Other_Network_path,@Backup_Expiration_Days
   PRINT 'System clear up old bakcup files from following path: ' + ISNULL(@Other_Network_path,'')
   
   -- ******************************** Copy dabatase from principal server to external PC *********************************


   BEGIN TRY


    SET @CopyFromPrincipalToOtherCMD = 'xcopy "'+ @Server_Local_Path + '*.bak" "'+ @Other_Network_path + '" /Y'
    EXECUTE xp_cmdshell @CopyFromPrincipalToOtherCMD
    PRINT 'System copy the backup from [' + @Server_Local_Path + '] to [' + @Other_Network_path + ']'
   END TRY
   BEGIN CATCH
    PRINT 'Error Number = ' +  ERROR_NUMBER() + '; Error Message = ' + ERROR_MESSAGE()
   END CATCH;
   END
END

آموزش نصب sql server 2017