#1 2009-08-03 12:45:53

**_seanboyce_**

Post your SQL Server maintenance scripts here!

This one is for reindexing all tables in a database:

USE breeze
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_MSforeachtable @command1=" UPDATE STATISTICS ?"
GO

And this is my daily backup script:

SET NOCOUNT ON;
DECLARE 
    @FilePath AS varchar(100), 
    @LogicalName AS varchar(200), 
    @FileName AS varchar(200), 
    @Today AS datetime, 
    @db AS varchar (255);

-- The database to back up and path for the backups -- modify this as necessary
SET @db = $(db);
SET @FilePath = $(filepath);

-- Make sure filepath ends with \ and take into account net locations
SET @FilePath = REPLACE(@FilePath, '\\', '?');
SET @FilePath = @FilePath + '\';
SET @FilePath = REPLACE(@FilePath, '\\', '\');
SET @FilePath = REPLACE(@FilePath, '?', '\\');

-- Get the current date and time to assign to the dump file
SET @Today = GETDATE();
SET @LogicalName = @db + '_' +
    CAST ( DATEPART(year, @Today) as varchar ) +
        RIGHT('0' + CAST ( DATEPART(month, @Today) as varchar ), 2) +
        RIGHT('0' + CAST ( DATEPART(day, @Today) as varchar ), 2) + '-' +
        RIGHT('0' + CAST ( DATEPART(hour, @Today) as varchar ), 2) +
        RIGHT('0' + CAST ( DATEPART(minute, @Today) as varchar ), 2) +
        RIGHT('0' + CAST ( DATEPART(second, @Today) as varchar ), 2);
    
-- Create the filename for the dump file
SET @FileName = @FilePath + @LogicalName + '.bak';

-- Perform the backup and provide feedback to the user    
PRINT 'Adding dump device: ' + @LogicalName;
EXEC('sp_addumpdevice ''disk'', ''' + @LogicalName + ''', ''' + @filename + '''');
PRINT 'Backing up database: ' + @db;
EXEC('BACKUP DATABASE [' + @db + '] TO [' + @LogicalName + ']');
PRINT 'Dropping dump device:  ' + @LogicalName;
EXEC('sp_dropdevice ''' + @LogicalName + '''');
PRINT 'Database ' + @db + ' written to: ' + @FileName;