SQL Server

Backup

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

backup-AWS

-- restaurar o backup
exec msdb.dbo.rds_restore_database 
        @restore_db_name='Internacao', 
        @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extensio';

-- como fazer backup
exec msdb.dbo.rds_backup_database 
        @source_db_name='database_name', 
        @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
        @overwrite_S3_backup_file=1,
        @type='differential';

-- exec msdb.dbo.rds_task_status     
exec msdb.dbo.rds_task_status @db_name='nome_da_base';
-- EXECUTE msdb.dbo.rds_drop_database  N'nome_da_base';

Calcular Idade

create function CalcIdade 
	(
		@dtIni as DateTime, 
		@dtFim as DateTime 
	) returns varchar(20) as
begin 
DECLARE @tmpdate datetime
DECLARE @years int
DECLARE @months int
DECLARE @days int

  SELECT @tmpdate = @dtIni

  SELECT @years = DATEDIFF(yy, @tmpdate, @dtFim) - CASE WHEN (MONTH(@dtIni) > MONTH(@dtFim)) OR (MONTH(@dtIni) = MONTH(@dtFim) AND DAY(@dtIni) > DAY(@dtFim)) THEN 1 ELSE 0 END
  SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
  SELECT @months = DATEDIFF(m, @tmpdate, @dtFim) - CASE WHEN DAY(@dtIni) > DAY(@dtFim) THEN 1 ELSE 0 END
  SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
  SELECT @days = DATEDIFF(d, @tmpdate, @dtFim)

  return right(Cast(@years + 1000 as varchar(4)),3) + '.' + right(cast(@months + 100 as varchar(3)),2) + '.' + right(cast(@days + 100 as varchar(3)),2)
end

CREATE LOGIN banco somente leitura

CREATE LOGIN oftalmo 
    WITH PASSWORD    = N'balblabla#2018',
    CHECK_POLICY     = OFF,
    CHECK_EXPIRATION = OFF
GO

Use clinic
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'oftalmo')
BEGIN
    CREATE USER [oftalmo] FOR LOGIN [oftalmo]
    -- EXEC sp_addrolemember N'db_owner', N'oftalmo'
END
GO


GRANT SELECT ON [dbo].[Exames_Finalizados] TO oftalmo
go

GRANT SELECT ON [dbo].[Proced_Finalizados] TO oftalmo
go

Criando Usuario SQL

USE [master]
GO
CREATE LOGIN [LASERVIEW] WITH PASSWORD=N'balblabla#2013', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [LASERVIEW] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, NO CREDENTIAL
GO
USE [Clinic]
GO
CREATE USER [LASERVIEW] FOR LOGIN [LASERVIEW]
GO
GRANT SELECT ON [dbo].[pac_conv] TO [LASERVIEW]
GO
GRANT ALL ON [dbo].[clid060] TO [LASERVIEW]
GO
GRANT ALL ON [dbo].[clid015] TO [LASERVIEW]
GO
USE [Cadastros]
GO
CREATE USER [LASERVIEW] FOR LOGIN [LASERVIEW]
GO
GRANT SELECT ON [dbo].[SCCHD015] TO [LASERVIEW]
GO

Descobre a maquina que esta travando

SELECT client_net_address as [IP do cliente],
p.hostname as [Nome da máquina do cliente],
[text] as [Texto da consulta],
DB_NAME(p.dbid) as [Nome do BD no qual foi executada a query],
p.[program_name] as [Programa solicitante]
FROM sys.dm_exec_connections c
INNER JOIN sys.sysprocesses p on c.session_id = p.spid
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST

Descobre qual SQL consumiu mais maquina

select total_worker_time/execution_count as MediaCPU
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count as MediaDuration
, total_elapsed_time AS TotalDuration
, total_logical_reads/execution_count as MediaLogicalReads
, total_logical_reads AS TotalLogicalReads
, total_physical_reads/execution_count as MediaPhysicalReads
, total_physical_reads AS TotalPhysicalReads
, execution_count 
, substring(st.text, (qs.statement_start_offset/2)+1
, ((case qs.statement_end_offset  when -1 then datalength(st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 1 desc

lista todas tabelas do banco e o tamanho delas para saber se tem dados

sELECT
    OBJECT_NAME(object_id) As Tabela, Rows As Linhas,
    SUM(Total_Pages * 8) As Reservado,
    SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Dados,
        SUM(Used_Pages * 8) -
        SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Indice,
    SUM((Total_Pages - Used_Pages) * 8) As NaoUtilizado
FROM
    sys.partitions As P
    INNER JOIN sys.allocation_units As A ON P.hobt_id = A.container_id
GROUP BY OBJECT_NAME(object_id), Rows
ORDER BY Dados desc

Recovery

--SQL 2005
EXEC sp_resetstatus 'clinic'
ALTER DATABASE clinic SET EMERGENCY
DBCC checkdb('clinic')
ALTER DATABASE clinic SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('clinic', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE clinic SET MULTI_USER


--SQL 2000
Sp_configure 'allow updates', 1 
Reconfigure with override 


update sysdatabases 
set status = status | -32768 
where name = 'SFN' 

update sysdatabases 
set status = 24
where name = 'SFN'

Reoganizar indices

-- Muda o contexto para o MASTER
USE master;
--  Altera o banco para modo único
ALTER DATABASE CLINIC  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Reindexa Banco –  Dependendo do tamanho do DB demora uns Minutos.
DBCC  CHECKDB('CLINIC',REPAIR_REBUILD) WITH NO_INFOMSGS;
--  Volta o Banco a modo Multi usuário
ALTER DATABASE  CLINIC SET MULTI_USER WITH ROLLBACK IMMEDIATE;



--ALTER INDEX IX_CLID061_NEW ON Clinic.dbo.clid061 REORGANIZE ;

Reorganiza indices 2

DECLARE @NOMEBANCO VARCHAR(50)
DECLARE @CONTA INT
DECLARE @REORGANIZABANCO CURSOR
SET @CONTA = 1
  SET @REORGANIZABANCO = CURSOR FOR
    SELECT NAME
      FROM SYSOBJECTS 
     WHERE Xtype = 'U' 
     ORDER BY NAME
  OPEN @REORGANIZABANCO
  FETCH NEXT
    FROM @REORGANIZABANCO INTO @NOMEBANCO
  WHILE @@FETCH_STATUS = 0
  BEGIN
		EXEC('
			ALTER INDEX ALL ON ' + @NOMEBANCO + '
			REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
						  STATISTICS_NORECOMPUTE = ON);
			')
        FETCH NEXT
        FROM @REORGANIZABANCO INTO @NOMEBANCO
		PRINT @NOMEBANCO	 			  
		SET @CONTA = @CONTA + 1
		PRINT @CONTA
  End
CLOSE @REORGANIZABANCO
DEALLOCATE @REORGANIZABANCO

RTF para txt

create function dbo.RTF2TXT(@in varchar(8000)) RETURNS  varchar(8000) AS 
BEGIN

DECLARE @object int
DECLARE @hr int
DECLARE @out varchar(8000)

-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT
EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in
EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT
EXEC @hr = sp_OADestroy @object
return @out

END
GO

select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\uc1\deff0\stshfdbch0\stshfloch0\stshfhich0\stshfbi0\deflang1033\deflangfe1033{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f42\froman\fcharset238\fprq2 Times New Roman CE;}
{\f43\froman\fcharset204\fprq2 Times New Roman Cyr;}{\f45\froman\fcharset161\fprq2 Times New Roman Greek;}{\f46\froman\fcharset162\fprq2 Times New Roman Tur;}{\f47\froman\fcharset177\fprq2 Times New Roman (Hebrew);}
{\f48\froman\fcharset178\fprq2 Times New Roman (Arabic);}{\f49\froman\fcharset186\fprq2 Times New Roman Baltic;}{\f50\froman\fcharset163\fprq2 Times New Roman (Vietnamese);}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;
\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;
\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \fs24\lang1033\langfe1033\cgrid\langnp1033\langfenp1033 \snext0 Normal;}{\*\cs10 \additive \ssemihidden 
Default Paragraph Font;}{\*\ts11\tsrowd\trftsWidthB3\trpaddl108\trpaddr108\trpaddfl3\trpaddft3\trpaddfb3\trpaddfr3\tblind0\tblindtype3\tscellwidthfts0\tsvertalt\tsbrdrt\tsbrdrl\tsbrdrb\tsbrdrr\tsbrdrdgl\tsbrdrdgr\tsbrdrh\tsbrdrv 
\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \fs20\lang1024\langfe1024\cgrid\langnp1024\langfenp1024 \snext11 \ssemihidden Normal Table;}}{\*\rsidtbl \rsid2189369\rsid3043591\rsid3308915\rsid4929009\rsid7289738
\rsid11422153\rsid13314608\rsid14579482}{\*\generator Microsoft Word 10.0.6835;}{\info{\title Four Score And Seven Years Ago\''85}{\author lizaguirre}{\operator lizaguirre}{\creatim\yr2007\mo10\dy24\hr11\min47}{\revtim\yr2007\mo10\dy24\hr11\min47}
{\version1}{\edmins0}{ofpages1}{ofwords4}{ofchars28}{\*\company HDS}{ofcharsws31}{\vern16393}{\*\password 00000000}}{\*\xmlnstbl }\paperw12240\paperh15840\margl720\margr720\margt720\margb720\gutter0 
\widowctrl\ftnbj\aenddoc\grfdocevents0oxlattoyen\expshrtnoultrlspc\dntblnsbdbospaceforul\formshade\horzdoc\dgmargin\dghspace187\dgvspace180\dghorigin720\dgvorigin720\dghshow1\dgvshow2
\jexpand\viewkind1\viewscale100\pgbrdrhead\pgbrdrfoot\splytwnine\ftnlytwnine\htmautspolnhtadjtbl\useltbaln\alntblind\lytcalctblwd\lyttblrtgr\lnbrkruleobrkwrptbl\snaptogridincell\allowfieldendsel\wrppunct\asianbrkrule\rsidroot3308915 \fet0
{\*\wgrffmtfilter 013f}\sectd \linex0\endnhere\sectlinegrid360\sectdefaultcl\sectrsid2189369\sftnbj {\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl2\pnucltr\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl3
\pndec\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl4\pnlcltr\pnstart1\pnindent720\pnhang {\pntxta )}}{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl6\pnlcltr\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}
{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl8\pnlcltr\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}\pard\plain 
\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \fs24\lang1033\langfe1033\cgrid\langnp1033\langfenp1033 {\insrsid7289738 Four Score And Seven Years Ago\''85}{\insrsid11422153 
\par }}')

SHRINK

USE Clinic
GO
DBCC SHRINKFILE(Clinic_log, 1)
BACKUP LOG Clinic WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Clinic_log, 1)


USE Cadastros_Auditoria;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Cadastros_Auditoria
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Cadastros_Auditoria_log, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE Cadastros_Auditoria
SET RECOVERY FULL;
GO



select * from sys.database_files

SHRINKFILE

USE dbname;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE dbname
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE dbname
SET RECOVERY FULL;
GO



USE Cadastros_Auditoria;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Cadastros_Auditoria
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Cadastros_Auditoria_log, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE Cadastros_Auditoria
SET RECOVERY FULL;
GO



select * from sys.database_files

SPLIT

CREATE FUNCTION SPLIT(@ARRAY VARCHAR(8000), @DELIMITADOR VARCHAR(100)) RETURNS VARCHAR(8000) AS 
	--SET NOCOUNT ON
	BEGIN
	DECLARE @S VARCHAR(8000)

	-- VALORES PASSADOS PARA A VARIAVEL @ARRAY
	--SELECT @ARRAY = 'OLA ,TUDO BEM, MAIS OU MENOS, TRANQUILIS'
	-- SETANDO O DELIMITADOR
	--SELECT @DELIMITADOR = ','

	IF LEN(@ARRAY) > 0 SET @ARRAY = @ARRAY + @DELIMITADOR 

	WHILE LEN(@ARRAY) > 0
	BEGIN
	   SELECT @ARRAY = SUBSTRING(@ARRAY, CHARINDEX(@DELIMITADOR, @ARRAY) + 1, LEN(@ARRAY))
	END

	-- MOSTRANDO O RESULTADO JÁ POPULADO NA TABELA TEMPORÁRIA

	--SET NOCOUNT OFF
END

Verifica memoria servidor

with cteSizeDB as ( 
   SELECT 'DB:'+DB_NAME(database_id) AS cDBName , 
          cast(COUNT(*) * 8 / 1024.0 as decimal(7,2)) as nSizeInMemoryMB 
     FROM sys.dm_os_buffer_descriptors 
    GROUP BY DB_NAME(database_id) 
    union all 
    select 'Mem:'+type , sum(pages_kb)/1024  
      from sys.dm_os_memory_clerks 
  where type <> 'MEMORYCLERK_SQLBUFFERPOOL' 
          group by type 
)select * 
   from cteSizeDB 
   where nSizeInMemoryMB > 0 
  union 
 select 'Todos' , SUM(nSizeInMemoryMB) 
   from cteSizeDB 
   order by 2 desc;