SQL SERVER 2008

تجارب من در رابطه با SQL SERVER

چند Script خوب در رابطه با تعریف Job
ساعت ٥:٤۸ ‎ب.ظ روز ۱۳۸٧/۱٠/٢٤  کلمات کلیدی: sql server 2008 ، job ، automatic backup ، پشتیبانگیری خودکار

برای انجام کارها در SQL SERVER به صورت خودکار باید JOB تعریف کرد. این کار میتواند به صورت دستی از داخل برنامه انجام شود یا اینکه برای آن Script نوشت و آن را اجرا کرد. یکی سوالات متداول این است که چگونه میتوانیم به صورت خودکار از SQL SERVER پشتیبانگیری کنیم، این کار با JOB ها قابل انجام است. در زیر چند Job کاربردی برای نمونه آورده شده است:

Back up به صورت کامل : کد مثال برای تمام Job ها یک دایرکتوری به ازای هر پایگاه داده خواهد ساخت و از آن پشتیبانگیری میکند :

DECLARE @basePath varchar(300);
SET @basePath = N'M:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\';

DECLARE @db sysname;
DECLARE user_db_cursor CURSOR FOR
      SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN user_db_cursor
FETCH NEXT FROM user_db_cursor
INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
      DECLARE @backupPath varchar(1000);
      SET @backupPath = @basePath + @db + '\';

      DECLARE @backupName varchar(1000);
      DECLARE @time datetime2;
      SET @time = SYSDATETIME();
      SET @backupName = @db + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar, @time, 20),'-','_'),':',''),' ','_')
      + '_' +  CAST(DATEPART(NANOSECOND, @time)/100 as varchar)

      --Create sub-directory
      EXECUTE master.dbo.xp_create_subdir @backupPath
     
      DECLARE @backupFileName varchar(1000);
      SET @backupFileName = @backupPath + @backupName + '.bak';
     
      --Backup database
      BACKUP DATABASE @db TO DISK=@backupFileName
            WITH RETAINDAYS=14, NOFORMAT, NOINIT, NAME=@backupName ,
            SKIP, REWIND, NOUNLOAD, STATS=10

CLOSE user_db_cursor
DEALLOCATE user_db_cursor

پشتیبان Differential :

DECLARE @basePath varchar(300);
SET @basePath = N'M:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\';

DECLARE @db sysname;
DECLARE user_db_cursor CURSOR FOR
 SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN user_db_cursor
FETCH NEXT FROM user_db_cursor
INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
      DECLARE @backupPath varchar(1000);
      SET @backupPath = @basePath + @db + '\';

      DECLARE @backupName varchar(1000);
      DECLARE @time datetime2;
      SET @time = SYSDATETIME();
      SET @backupName = @db + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar, @time, 20),'-','_'),':',''),' ','_')
      + '_' + CAST(DATEPART(NANOSECOND, @time)/100 as varchar)

      --Create sub-directory
      EXECUTE master.dbo.xp_create_subdir @backupPath
     
      DECLARE @backupFileName varchar(1000);
      SET @backupFileName = @backupPath + @backupName + '.bak';
     
      --Backup database
      BACKUP DATABASE @db TO DISK=@backupFileName
            WITH DIFFERENTIAL, RETAINDAYS=14, NOFORMAT, NOINIT, NAME=@backupName ,
            SKIP, REWIND, NOUNLOAD, STATS=10
      
CLOSE user_db_cursor
DEALLOCATE user_db_cursor

پشتیبان  Transaction Log :

DECLARE @basePath varchar(300);
SET @basePath = N'M:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\';

DECLARE @db sysname;
DECLARE user_db_cursor CURSOR FOR
      SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN user_db_cursor
FETCH NEXT FROM user_db_cursor
INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
      DECLARE @backupPath varchar(1000);
      SET @backupPath = @basePath + @db + '\';

      DECLARE @backupName varchar(1000);
      DECLARE @time datetime2;
      SET @time = SYSDATETIME();
      SET @backupName = @db + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar, @time, 20),'-','_'),':',''),' ','_')
      + '_' +  CAST(DATEPART(NANOSECOND, @time)/100 as varchar)

      --Create sub-directory
      EXECUTE master.dbo.xp_create_subdir @backupPath
     
      DECLARE @backupFileName varchar(1000);
      SET @backupFileName = @backupPath + @backupName + '.trn';
     
      --Backup database
      BACKUP LOG @db TO DISK=@backupFileName
            WITH RETAINDAYS=14, NOFORMAT, NOINIT, NAME=@backupName ,
            SKIP, REWIND, NOUNLOAD, STATS=10
      
CLOSE user_db_cursor
DEALLOCATE user_db_cursor

اجرای Job Agent Task در SQL SERVER :

EXEC msdb.dbo.sp_start_job '<job name>';

نکته : این عملیات به کمک SQL Server Agent انجام میشود. اطمینان حاصل کنیدکه سرویس مربوط به آن run است.