블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            
  • 28,566total
  • 2today
  • 5yesterday
2010.11.03 11:34 SQL SERVER

/*****

주 서버 로그 전달 데이터베이스, 백업 폴더 및 공유, 백업 보유 기간, 백업 작업, 최종 백업 시간 및 파일 정보

******/
SELECT primary_database, backup_directory, backup_share, backup_retention_period AS backup_retention_minutes,
       j.name AS backup_job_name, monitor_server, last_backup_file, last_backup_date
FROM     msdb.dbo.log_shipping_primary_databases pd
JOIN msdb.dbo.sysjobs j ON j.job_id = pd.backup_job_id
order by last_backup_date
GO


/*****

주 서버 로그 전달 데이터베이스에 대한 보조 서버 및 데이터베이스 정보

******/
/*****

주 데이터베이스 모니터링 이력 정보

******/
SELECT pd.primary_database,
        CASE agent_type WHEN 0 THEN 'Backup' WHEN 1 THEN 'Copy' WHEN 2 THEN 'Restore' END AS agent_type,
        database_name,
    CASE session_status WHEN 0 THEN 'Starting' WHEN 1 THEN 'Running' WHEN 2 THEN 'Success'
    WHEN 3 THEN 'Error' WHEN 4 THEN 'Warning' END AS session_status,
log_time, message
FROM     msdb.dbo.log_shipping_monitor_history_detail hd
JOIN msdb.dbo.log_shipping_primary_databases pd ON pd.primary_id = hd.agent_id
WHERE   log_time >= CAST(CONVERT(CHAR(10), getdate(), 120) AS DATETIME)
ORDER BY pd.primary_database, log_time
GO

/*****

주 데이터베이스 상세 오류 정보

******/

SELECT pd.primary_database,
 CASE agent_type WHEN 0 THEN 'Backup' WHEN 1 THEN 'Copy' WHEN 2 THEN 'Restore' END AS agent_type,
 database_name, log_time, message
    FROM     msdb.dbo.log_shipping_monitor_error_detail ed
    JOIN msdb.dbo.log_shipping_primary_databases pd ON pd.primary_id = ed.agent_id
    WHERE   log_time >= CAST(CONVERT(CHAR(10), getdate(), 120) AS DATETIME)
    ORDER BY pd.primary_database, log_time

 
/*****

보조 서버에 대한 로그 전달 데이터베이스, 백업 원본 및 대상 폴더, 백업 파일 보유 기간, 백업 파일 복사 및 복원 작업, 최종 복원 시간 정보

******/
SELECT s.primary_server, s.primary_database, last_restored_date , backup_source_directory, backup_destination_directory,
                           file_retention_period AS file_retention_minutes,
                           j1.name AS copy_job_name, j2.name AS restore_job_name, monitor_server
FROM     msdb.dbo.log_shipping_secondary s
                           JOIN msdb.dbo.sysjobs j1 ON j1.job_id = s.copy_job_id
                           JOIN msdb.dbo.sysjobs j2 ON j2.job_id = s.restore_job_id
                           JOIN msdb.dbo.log_shipping_monitor_secondary ms ON ms.secondary_id = s.secondary_id
            
order by last_restored_date
GO 
저작자 표시
신고
posted by bedbmsguru
2010.11.03 11:34 SQL SERVER

비슷한 일을 하는 쿼리를 만들어야 할때 참고할 것
혹시 이글 보시고 더 좋은 방법 아시는분은 알려주세요^^;;

DECLARE CHECK_COLUMN CURSOR FOR
select OBJECT_NAME(object_id) AS table_name, object_id  from sys.columns where system_type_id in(231, 167) and object_id > 100
grOUP BY object_id

DECLARE @table_name AS VARCHAR(100)
DECLARE @table_id AS BIGINT

OPEN CHECK_COLUMN
    FETCH NEXT FROM CHECK_COLUMN INTO @table_name, @table_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @RESULT_QUERY AS VARCHAR(2000)
       
        SET @RESULT_QUERY = 'SELECT TOP 1 '''  + @table_name + ''', '
        DECLARE PROCESS_COLUMN CURSOR FOR
            select name from sys.columns where system_type_id in(231, 167) and object_id = @table_id
           
        DECLARE @column_name AS VARCHAR(100)
        DECLARE @FkFirstColumn BIT SET @FkFirstColumn = 1
        OPEN PROCESS_COLUMN
        FETCH NEXT FROM PROCESS_COLUMN INTO @column_name
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF(@FkFirstColumn = 1)
                SET @FkFirstColumn = 0
            ELSE
                SET @RESULT_QUERY = @RESULT_QUERY + ', '       
            SET @RESULT_QUERY = @RESULT_QUERY + @column_name
           
            FETCH NEXT FROM PROCESS_COLUMN INTO @column_name
        END
        CLOSE PROCESS_COLUMN
        DEALLOCATE PROCESS_COLUMN
    SET @RESULT_QUERY = @RESULT_QUERY + ' FROM ' + @table_name
    PRINT @RESULT_QUERY
    FETCH NEXT FROM CHECK_COLUMN INTO @table_name, @table_id
    END
CLOSE CHECK_COLUMN
DEALLOCATE CHECK_COLUMN
저작자 표시
신고
posted by bedbmsguru
2010.11.03 11:33 SQL SERVER

DECLARE cIX CURSOR FOR
   SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
      FROM Sys.Indexes SI
         LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
      WHERE TC.CONSTRAINT_NAME IS NULL
         AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
      ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE @IXSQL NVARCHAR(4000) --SET @PKSQL = ''
   SET @IXSQL = 'CREATE '

   -- Check if the index is unique
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
      SET @IXSQL = @IXSQL + 'UNIQUE '
   -- Check if the index is clustered
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
      SET @IXSQL = @IXSQL + 'CLUSTERED '

   SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

   -- Get all columns of the index
   DECLARE cIxColumn CURSOR FOR
      SELECT SC.Name
      FROM Sys.Index_Columns IC
         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
      WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
      ORDER BY IC.Index_Column_ID

   DECLARE @IxColumn SYSNAME
   DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

   -- Loop throug all columns of the index and append them to the CREATE statement
   OPEN cIxColumn
   FETCH NEXT FROM cIxColumn INTO @IxColumn
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      IF (@IxFirstColumn = 1)
         SET @IxFirstColumn = 0
      ELSE
         SET @IXSQL = @IXSQL + ', '

      SET @IXSQL = @IXSQL + @IxColumn

      FETCH NEXT FROM cIxColumn INTO @IxColumn
   END
   CLOSE cIxColumn
   DEALLOCATE cIxColumn

   SET @IXSQL = @IXSQL + ')'
   -- Print out the CREATE statement for the index
   PRINT @IXSQL

   FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX
저작자 표시
신고
posted by bedbmsguru

티스토리 툴바