Перейти к содержимому

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

Задание.

Не обходим аудит который отслеживал кто изменил , создал, удалил, задание или базу, а также кто менял настройки mssql.

Был выбран Profiler так как в нем уже есть шаблоны аудита.

Для на чала создаем трассировку в самом Profiler.

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

Потом делаем определение трассировки

Аудит mssql 2005 с помощью SQL Server Profiler и создания задания в агенте.

Получаем вот такой код

-- ======================================================================================================

-- Author: Константин Москвичёв

-- Create date: 13/04/2015

-- Description:    0.2b

-- Описание: Запускает трасировку для аудита. 

-- ======================================================================================================

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @filename nvarchar(128)
declare @filecount int

--указываем максимальный размер файла
set @maxfilesize = 99

--указываем путь и имя файла, к имени файла дописывается 
Читать далее

Шаблон для отправки оповещения на почту

Email уведомление должно выглядеть так:

Заголовок письма [server name].[job status].[job desc]
Тема письма [job name]
Тело письма [job time]
declare @status nvarchar(5)
declare @jobdesc nvarchar(256)
declare @jobname nvarchar(256)
declare @body_alert nvarchar (max)
declare @subject_alert nvarchar (256)
declare @getdate nvarchar(26)
 
set @status = 'OK' -- статус OK или ERROR
set @jobname = 'Mirror exceed base backup' -- имя задания в агенте mssql
set @jobdesc = 'Создание бэкапа бд exceed'  -- краткое описание задания,понятное человеку
 
set @getdate = CONVERT(nvarchar(26),getdate(),113)
set @body_alert = ' <p><small>Имя сервера</small>: '+@@servername + '</p>
					<p><small>Название задания в агенте sql server</small>: '+@jobname+'</p>
					<p><small>Статус задания</small>: '+@status + '</p>
					<p><small>Описание задания</small>: 
Читать далее

MSsql чтение excel и txt

чтение excel

SELECT * INTO XLImportBetaX FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT jobid,userid,start,stop,date FROM [14.09.2012$]')

insert INTO XLImport512 select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [13.09.2012$]')

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT jobid,userid,start,stop,date FROM [14.09.2012$]')

select * from XLImportBeta2 exec sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE

чтение txt

 SELECT * FROM OPENROWSET(BULK N'C:\aos1.txt',SINGLE_CLOB ) AS Document

SELECT a.* FROM OPENROWSET( BULK 'c:\aos1.txt',
 FORMATFILE = 'c:\values.fmt') AS a;
 
   file format
 
 9.0
2
1  SQLCHAR  0  20 ":" 1 ID ""
2  SQLCHAR  0  20 "*" 2 IwD ""

 

Создание n баз из одного бэкапа под разными именами

declare @name nvarchar(56)
declare @cmd nvarchar(512)
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FOR
select '0'+right(servername,2) as name from s0055.rkur_dbastat.dbo.ServerName
where is_disable = 0
--and servername = 's1027'
 
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @name
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
print @name
set @cmd = 'RESTORE DATABASE [planograms'+@name+'] 
FROM DISK= N''H:\MSSQL\Backup\etalon_planograms_20150112.bak'' 
WITH  FILE = 1 
,MOVE  N''Planograms'' TO N''H:\MSSQL\Data\planograms'+@name+'.mdf''
,MOVE N''Planograms_log'' TO N''H:\MSSQL\Data\planograms'+@name+'_1.ldf''
,  NOUNLOAD
,  STATS = 10
'
print @cmd
--exec (@cmd)
 
FETCH NEXT FROM @Cursor INTO @name
end

Хранимая процедура для добавления новой бд

Как использовать

use master
go
exec CreateNewPlanograms
@name='999'

код процедуры

USE [master]
Читать далее

Пинг компа из ssms

declare @cmnd  varchar(2000)
declare @ip varchar(15)
declare @res varchar(15)
 
set @ip = 's0049'   -- ip-адрес проверяемого узла
set @cmnd = 'ping -n 1 '+@ip
 
CREATE TABLE #tmp(s varchar(1000))
INSERT #tmp exec master.dbo.xp_cmdshell @cmnd
 
SET @res = (SELECT * FROM #tmp WHERE s LIKE '% потерь%' )
DROP TABLE #tmp
 
if (rtrim(ltrim(@res)) <> '(0% потерь)' or @res is null)
print 'Нет связи с компьютером'
else 
print @ip  +' '+ @res

 

Перенос файлов базы данных на другой диск или директорию.

ALTER DATABASE ZUP_MOLL SET OFFLINE;
go
ALTER DATABASE ZUP_MOLL MODIFY FILE ( NAME = ZUP_MOLNIYA, FILENAME = 'F:\MSSQL\DATA\ZUP_MOLL.mdf' )
go
ALTER DATABASE ZUP_MOLL MODIFY FILE ( NAME = ZUP_MOLNIYA_log, FILENAME = 'F:\MSSQL\DATA\ZUP_MOLL_1.LDF' )
go
ALTER DATABASE ZUP_MOLL SET ONLINE;
go

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'ZUP_MOLL');

 

Когда был последний бэкап mssql

SELECT  @@Servername AS ServerName ,
        d.Name AS DBName ,
        b.name,
        b.user_name,
        b.type,
        b.backup_start_date,
        b.Backup_finish_date,
        bmf.Physical_Device_name
FROM    sys.databases d
        INNER JOIN msdb..backupset b ON b.database_name = d.name
                                        AND b.[type] = 'D'
        INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
        where d.name = 'имя базы'
ORDER BY d.NAME ,
        b.Backup_finish_date DESC;

 

Изменить владельца mssql job

USE msdb ;
GO
 
EXEC dbo.sp_manage_jobs_by_login
    @action = N'REASSIGN',
    @current_owner_login_name = N'HEAD_OFFICE\v.musalov',
    @new_owner_login_name = N'sa' ;
GO
SELECT  [job_id]
      ,[originating_server_id]
      ,[name]
      ,[enabled]
      ,[description]
      ,[start_step_id]
      ,[category_id]
      ,[owner_sid]
      ,[notify_level_eventlog]
      ,[notify_level_email]
      ,[notify_level_netsend]
      ,[notify_level_page]
      ,[notify_email_operator_id]
      ,[notify_netsend_operator_id]
      ,[notify_page_operator_id]
      ,[delete_level]
      ,[date_created]
      ,[date_modified]
      ,[version_number]
  FROM [msdb].[dbo].[sysjobs]
 
 
  select * from [msdb].[dbo].[sysjobs]
 
  SELECT [job_id]
      ,[step_id]
      ,[step_name]
      ,[subsystem]
      ,command
      ,replace([command],'ssas_molniya','diablo')
      ,[flags]
      ,[additional_parameters]
      ,[cmdexec_success_code]
      ,[on_success_action]
      ,[on_success_step_id]
      ,[on_fail_action]
      ,[on_fail_step_id]
      ,[server]
      ,[database_name]
      ,[database_user_name]
      ,[retry_attempts]
      ,[retry_interval]
      ,[os_run_priority]
      ,[output_file_name]
      ,[last_run_outcome]
      ,[last_run_duration]
      ,[last_run_retries]
      ,[last_run_date]
      ,[last_run_time]
      ,[proxy_id]
      ,[step_uid]
  FROM [msdb].[dbo].[sysjobsteps]
  where database_name = 'ssas_molniya' 
  --and command like '%ssas_molniya%' 
  and job_id in (
      select job_id
  FROM [msdb].[dbo].[sysjobs] where date_created > '20140713'
  )
 
 
  update [msdb].[dbo].[sysjobsteps]
  set database_name = 'ssas_molniya_sz'
  
Читать далее

Генерация скрипта для перестроения индекса

[stextbox id=”info”]генерация скрипта для перестроения отдельного индекса на таблице[/stextbox]

declare @tabname nvarchar(64)
declare @dbname nvarchar(64)
declare @indname nvarchar(64)
declare @q nvarchar(max)
-- **************************************
set @dbname = 'ax_molniya'
set @tabname = 'dbo.INVENTTRANS'
set @indname = 'I_177TRANSTYPETRANSREF_IDX'
-- **************************************
set @q = '/*
 
описание: скрипт для перестроения отдельного индекса на таблице

 
*/
 
PRINT ''Восстановление индекса на таблице ['+@tabname+'] начато '' + Cast(GETDATE() as varchar);
GO  
ALTER INDEX ['+@indname+'] ON '+@tabname+' REORGANIZE
GO
PRINT ''Восстановление индекса на таблице ['+@tabname+'] закончено '' + Cast(GETDATE() as varchar);
GO
declare @at nvarchar(500);
set @at = ''Восстановление индекса ['+@indname+'] на таблице ['+@tabname+'] выполнено'' + Cast(GETDATE() as varchar);
Читать далее