Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC
SELECT
A.name,
case description when '설명이 없습니다.' then '' else description end as description,
CASE convert(
nvarchar(3),
[freq_type],
3
) WHEN 4 THEN N' 매일' WHEN 8 THEN N' 매주 ' + CASE WHEN [freq_interval] & 1 = 1 THEN N'일' ELSE '' END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ' 월' ELSE '' END + CASE WHEN [freq_interval] & 4 = 4 THEN ' 화' ELSE '' END + CASE WHEN [freq_interval] & 8 = 8 THEN ' 수' ELSE '' END + CASE WHEN [freq_interval] & 16 = 16 THEN N' 목' ELSE '' END + CASE WHEN [freq_interval] & 32 = 32 THEN N' 금' ELSE '' END + CASE WHEN [freq_interval] & 64 = 64 THEN N' 토' ELSE '' END WHEN 16 THEN N'하루 한번 '
+ CAST(
[freq_interval] AS VARCHAR(3)
) + N' 매달 ' + CAST(
[freq_recurrence_factor] AS VARCHAR(3)
) + N' 매달' WHEN 32 THEN N'발생 ' + CASE [freq_relative_interval] WHEN 1 THEN N'첫째' WHEN 2 THEN N'둘째' WHEN 4 THEN N'셋째' WHEN 8 THEN N'넷째' WHEN 16 THEN N'마지막' END + ' ' + CASE [freq_interval] WHEN 1 THEN N'일' WHEN 2 THEN N'월' WHEN 3 THEN N'화' WHEN 4 THEN N'수' WHEN 5 THEN N'목' WHEN 6 THEN N'금' WHEN 7 THEN N'토' WHEN 8 THEN N'일' WHEN 9 THEN N'평일' WHEN 10 THEN N'주말' END + N' 모든 ' + CAST(
[freq_recurrence_factor] AS VARCHAR(3)
) + N' 매달' END AS [Recurrence],
CASE convert(
nvarchar(3),
[freq_subday_type],
3
) WHEN 1 THEN STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_start_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) WHEN 2 THEN N'주기적 발생 ' + CAST(
[freq_subday_interval] AS VARCHAR(3)
) + N' 초(s) 간격 ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_start_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) + ' & ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_end_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) WHEN 4 THEN N'주기적 발생 ' + CAST(
[freq_subday_interval] AS VARCHAR(3)
) + N' 분(s) 간격 ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_start_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) + ' & ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_end_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) WHEN 8 THEN N'주기적 발생 ' + CAST(
[freq_subday_interval] AS VARCHAR(3)
) + N' 시간(s) 간격 ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_start_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) + ' & ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_end_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) END [Frequency], D.step_id, D.step_name, D.command
FROM msdb.dbo.sysjobs A
INNER JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id
INNER JOIN msdb.dbo.sysschedules C ON B.schedule_id = C.schedule_id
INNER JOIN msdb.dbo.sysjobsteps D ON A.job_id = D.job_id
ORDER BY A.name
SELECT r.session_id,r.command
,cast(CONVERT(NUMERIC(6,2),r.percent_complete) as varchar) + ' %' AS [Percent Complete]
,CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time]
,CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min]
,CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min]
,CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
,CONVERT(VARCHAR(1000),(SELECT text FROM sys.dm_exec_sql_text(sql_handle))) AS [SQL]
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
-- 프로시저에서 사용하는 테이블 목록
;WITH stored_procedures AS
(
SELECT oo.name AS table_name, o.name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P' AND o.name LIKE '%프로시저명%'
)
SELECT name,Table_name FROM stored_procedures
WHERE row = 1
반대로 특정 테이블을 참조하는 프로시저 목록을 조회하는 SQL문 입니다.
-- 특정 테이블을 참조하는 프로시저 목록
;WITH stored_procedures AS
(
SELECT oo.name AS table_name, o.name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P' AND oo.name = '테이블명'
)
SELECT name,Table_name FROM stored_procedures
WHERE row = 1
BACKUP DATABASE [test] TO DISK = N'D:\Backup\test_full.bak' WITH NOFORMAT, NOINIT, NAME = N'test-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- 차등백업 1
BACKUP DATABASE [test] TO DISK = N'D:\Backup\test_1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'test-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- 차등백업 2
BACKUP DATABASE [test] TO DISK = N'D:\Backup\test_2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'test-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- 기본 전체 백업 + 차등 복구
문법
RESTORE DATABASE [디비명] FROM DISK = N'풀백업위치'
WITH MOVE '논리적 DB명' TO 'mdf 복구위치'
,MOVE '논리적 LOG명' TO 'ldf 복구위치', NORECOVERY, NOUNLOAD, STATS = 5;
RESTORE DATABASE [tes디비명] FROM DISK = N'차등백업위치' WITH FILE = 1, NOUNLOAD, STATS = 5
RESTORE DATABASE [test3] FROM DISK = N'D:\Backup\test_full.bak'
WITH MOVE 'test' TO 'C:\DATABASE\MSSQL15.MSSQLSERVER\MSSQL\DATA\test3.mdf'
,MOVE 'test_log' TO 'C:\DATABASE\MSSQL15.MSSQLSERVER\MSSQL\DATA\test3.ldf', NORECOVERY, NOUNLOAD, STATS = 5;
RESTORE DATABASE [test3] FROM DISK = N'D:\Backup\test_2.bak' WITH FILE = 1, NOUNLOAD, STATS = 5
select table_name ,column_name ,column_default, data_type
, character_maximum_length ,character_octet_length
From information_schema .columns
where column_name='cityCd'
order by character_maximum_length desc
