Maintenance

Regular Tasks

1.Index Maintenance:

sql

-- Rebuild Fragmented Indexes
DECLARE @TableName NVARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +
       QUOTENAME(OBJECT_NAME(object_id)) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
    FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

2.Statistics Update:

sql

-- Update Statistics
EXEC sp_updatestats;

3.Backup Strategy

sql

-- Full Backup
BACKUP DATABASE [YourDB]
TO DISK = 'C:\Backup\YourDB.bak'
WITH COMPRESSION, CHECKSUM;
-- Transaction Log Backup
BACKUP LOG [YourDB]
TO DISK = 'C:\Backup\YourDB.trn'
WITH COMPRESSION;