SQL Server - Como ocultar una instancia

Por defecto cuando instalamos SQL Server nuestra instancia esta abierta y puede ser descubierta vía red, para demostrar esto simplemente abrimos un SSMS (SQL Server Management Studio) y vamos al browser de servidores SQL de nuestra red.

Como se observa en la imagen al hacer un browser de servidores va a buscar en toda la red. todo SQL Server que este activo.

Para que esto no suceda lo que necesitamos hacer es configurar la instancia para que este en modo hide, para eso hacemos:

 - Accedemos al SQL Server configuration Manager.

 - Vamos a SQL Server Network Configuration.

 - Protocolos de SQL Server y hacemos clic del mouse sobre esa opción para ir a propiedades.

 - Ponemos en Yes la opción de “Hide Instance”.

 - Reiniciamos el servicio.

Con esta configuración nuestra instancia queda mas segura ya que la misma no podrá ser browsiada a nivel red.

SQL Server - Backup a todas las bases de datos

Las copias de seguridad de las bases de datos son muy importantes es por eso que se recomienda hacerlas periódicamente para no perder alguna información valiosa que estas contengan.

Ya sea que estés trabajando locamente o en un servidor hacerlo una a una puede ser tedioso y hasta aburrido, menos mal que SQL no da la facilidad de hacerlo vía Script y con un cursos podemos sacarle copia a todas nuestras bases de datos.

Para ello pueden usar este script:

DECLARE @name VARCHAR(50) -- Nombre de la base de datos  
DECLARE @path VARCHAR(256) -- Ruta de acceso para archivos de copia de seguridad
DECLARE @fileName VARCHAR(256) -- Nombre de archivo para copia de seguridad  
DECLARE @fileDate VARCHAR(20) -- Utilizado para el nombre de archivo
 
-- Especificar directorio de copia de seguridad de la base de datos
SET @path = 'C:\Backup\'  
 
-- Especificar el formato del nombre de archivo
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')  -- Excluir estas bases de datos
 
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

Fuente: https://www.mssqltips.com

SQL Server - Quién esta conectado a la base de datos y desconectar

1. Si usted quiere saber que usuarios tienen conexiones abiertas en una base de datos, puede incluir cláusula WHERE dbid = db_id ('AdventureWorks2012').

2. Debe tener cuidado y excluir su propia conexión por ejemplo, y spid <> 55 (mi id de conexión).

3. Ejemplo:

DECLARE @kill VARCHAR(8000)='';
SELECT @kill = @kill+'kill '+CONVERT(VARCHAR(5) ,spid)+';'
FROM   MASTER..sysprocesses
WHERE  dbid = DB_ID('AdventureWorks2012')
       AND spid<>55

PRINT @kill

EXEC (@kill);

SQL - Lista de procedimientos almacenados modificados en los últimos 7 días

Este sencillo script ayuda a identificar los procedimientos almacenados que se han cambiado / modificado en los últimos 7 días.

SELECT *
FROM   sys.objects
WHERE  TYPE = 'P'
       AND DATEDIFF(D ,modify_date ,GETDATE())<7

SQL - Conocer las ultimas consultas ejecutadas

El siguiente script permite saber que consultas se acaban de ejecutar en la base de datos.

SELECT deqs.last_execution_time  AS [Time],
       dest.TEXT                 AS [Query]
FROM   sys.dm_exec_query_stats   AS deqs
       CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY
       deqs.last_execution_time DESC

La gracia de la consulta es justamente  esa, listar las ultimas consultas SQL que se han ejecutado.

SQL - Vaciar los registros de distintas tablas en una base de datos

Vamos a recorrer todas las tablas de nuestra base de datos y eliminar sus registros. Para ello, utilizaremos la información que nos brinda information_schema.tables. Es muy importante que limitemos de alguna forma las tablas que se van a vaciar, por ejemplo, si las tablas de nuestra aplicación empiezan por "TBL_" podríamos especificar una condición WHERE que filtre por aquellas tablas que empiezan con ese nombre.

USE DB_PRUEBA
 
DECLARE @SQL_DELETE     VARCHAR(255),
        @NOM_TABLA      VARCHAR(255)

DECLARE CURSOR_DELETE CURSOR  
FOR
    SELECT 'DELETE FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME,
           TABLE_SCHEMA + '.' + TABLE_NAME
    FROM   information_schema.tables
    WHERE  TABLE_NAME NOT LIKE 'sysdiagrams'
    ORDER BY
           TABLE_NAME

OPEN CURSOR_DELETE FETCH NEXT FROM CURSOR_DELETE INTO @SQL_DELETE, @NOM_TABLA
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@SQL_DELETE)
    FETCH NEXT FROM CURSOR_DELETE INTO @SQL_DELETE, @NOM_TABLA
END
CLOSE CURSOR_DELETE
DEALLOCATE CURSOR_DELETE

SQL Server - Utilizando Arrays de parametros en Procedimientos Almacenados

Una característica muy solicitada por los desarrolladores en SQL Server es la de poder pasar varios valores a un procedimiento almacenado en forma de array. Desde hace mucho tiempo utilizo una función de SQL que me permitía trabajar con Arrays a través de una cadena de texto delimitada por comas, normalmente los utilizaba en procesos de selección como en el ejemplo siguiente:

CREATE PROCEDURE SP_GetPerson
 @param VARCHAR(MAX)
AS
 SELECT p.BusinessEntityID,
        p.FirstName,
        p.LastName
 FROM   Person.Person p
 WHERE  p.BusinessEntityID IN (SELECT *
                               FROM   [dbo].[ArrayToTable] (@param))
 ORDER BY
        p.BusinessEntityID ASC

En este procedimiento el parametro @param recibe una cadena delimitada por comas con los valores '1,2,3,4,5', el SP_GetPerson devuelve todas las personas con estos códigos, la definición del parametro '@param' se realiza con varchar(MAX), para que la cadena pueda almacenar la mayor cantidad de códigos posible, sin embargo varchar(MAX) tiene la limitación de 8000 caracteres y en algunas situaciones se nos puede quedar pequeño.
La definición de la función en Transact-Sql es la siguiente:

CREATE FUNCTION [dbo].[ArrayToTable]
(
 @delimStr NVARCHAR(MAX)
)
RETURNS @StrValTable TABLE 
        (StrVal VARCHAR(20))
AS
BEGIN
 DECLARE @strlist     NVARCHAR(MAX),
         @pos         INT,
         @delim       CHAR,
         @lstr        NVARCHAR(MAX)
 
 SET @strlist = ISNULL(@delimStr, '')
 SET @delim = ','
 
 WHILE ((LEN(@strlist) > 0) AND (@strlist <> ''))
 BEGIN
     SET @pos = CHARINDEX(@delim, @strlist)
     
     IF @pos > 0
     BEGIN
         SET @lstr = SUBSTRING(@strlist, 1, @pos -1)
         SET @strlist = LTRIM(SUBSTRING(@strlist, CHARINDEX(@delim, @strlist) + 1, 8000))
     END
     ELSE
     BEGIN
         SET @lstr = @strlist
         SET @strlist = ''
     END
     
     INSERT @StrValTable
     VALUES
       (
         @lstr
       )
 END
 RETURN
END
GO

Básicamente lo que hace es crear una tabla temporal conformada por un campo y cada valor en un único registro, de esta forma la consulta de arriba puede ser ejecutada sin problemas, hay que tener en cuenta que el uso de tablas temporales puede reducir el rendimiento, sin embargo es mejor esto a llamar al SP muchas veces. Podriamos tambien optar por pasar los arrays con XML y readaptar la función.