Tareas de mantenimiento en SQL Server 2008

A continuación publico 3 scripts que serirán para realizar tareas de mantenimiento sobre nuestras bases de datos en un servidor SQL Server 2008. Estos 3 scripts son para realizar una copia de seguridad, reducir el log de transacciones y regenerar índices. Lo bueno de estos scripts es que no son exclusivos para una base de datos, sino que realizarán las tareas sobre todas las bases de datos que tengas en el servidor (o las que tú quieras).

Copia de base de datos (Debes asignar la variable @backupPath con tu ruta):

DECLARE @backupPath AS VARCHAR(MAX)
SET @backupPath = N'C:\SqlServer\MSSQL10.MSSQLSERVER\MSSQL\Backup\'

DECLARE @dbName AS VARCHAR(100)
DECLARE @file AS VARCHAR(MAX)

DECLARE c1 CURSOR FOR
SELECT name FROM master..sysdatabases sdb
WHERE sdb.name NOT IN ('master','model','msdb','pubs','northwind','tempdb')
ORDER BY name

OPEN c1

FETCH NEXT FROM c1
INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @file = @backupPath + @dbName + '.BAK'

    BACKUP DATABASE @dbName
    TO  DISK = @file WITH NOFORMAT, INIT,  NAME = @dbName, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

    FETCH NEXT FROM c1
    INTO @dbName
END

CLOSE c1
DEALLOCATE c1

Reducir el log de transacciones

DECLARE @dbName AS VARCHAR(100)
DECLARE @cmd AS VARCHAR(MAX)
DECLARE c1 CURSOR FOR
SELECT name FROM master..sysdatabases sdb
WHERE sdb.name NOT IN ('master','model','msdb','pubs','northwind','tempdb')
ORDER BY name

OPEN c1

FETCH NEXT FROM c1
INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
    --Establecer la base de datos en uso
    SET @cmd = 'USE ' + @dbName

    --Si se establece el modo de restauración a simple, las partes inactivas del log de transacción deben ser borradas
    --Este comando reducirá el archivo de log un poco
    SET @cmd = @cmd + ' ALTER DATABASE ' + @dbName + ' SET RECOVERY SIMPLE'

    --Obtener el nombre de log de la base de datos
    SET @cmd = @cmd + ' DECLARE @logFile AS NVARCHAR(1000)'
    SET @cmd = @cmd + ' SELECT @logFile = name FROM ' + @dbName + '.sys.database_files WHERE type_desc = ''LOG'''
    --Cambiar el modo de restauración a Simple no es suficiente, esto reduce el log a 1 MB
    SET @cmd = @cmd + ' DBCC SHRINKFILE (@logFile , 1)'

    EXEC(@cmd)

    FETCH NEXT FROM c1
    INTO @dbName
END

CLOSE c1
DEALLOCATE c1

Regenerar índices

SET QUOTED_IDENTIFIER ON

DECLARE @Table VARCHAR(255) 
DECLARE @DataBase VARCHAR(255)
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')  
ORDER BY 1 

OPEN DatabaseCursor 

FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN 

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName  
                    FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''  

   -- create table cursor 
   EXEC (@cmd) 
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       -- SQL 2000 command 
       --DBCC DBREINDEX(@Table,' ',@fillfactor)  
        
       -- SQL 2005 command 
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
       EXEC (@cmd) 

       FETCH NEXT FROM TableCursor INTO @Table  
   END  

   PRINT @Database + ' indexes were rebuilt'

   CLOSE TableCursor  
   DEALLOCATE TableCursor 

   FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

Estos scripts los puedes ejecutar cuando quieras o programarlos para que sean ejecutados periódicamente con el Agente SQL.

Puedes modificar la "SELECT" donde obtiene las bases de datos donde se realizarán las tareas para que se ejecuten sobre las bases de datos que quieras. Si lo ejecutas como está publicado aquí, se ejecutará sobre todas las bases de datos.