I’ve attached a fresh copy of the Admin database SQL. I found this an easy-peasy method of adding backups and making databases self-service.
Here’s the steps – there’s a more detailed blog article on this elsewhere on my site –
1. create a database called ‘Admin’
2.
setup a weekly job runnning the following steps – either as a sproc or as a set of steps (recommended)
1. EXEC dbo.Daily_Data_Collection – fills daily report
2. EXEC GetLargeDBs (index fragmentation pt 1)
3. EXEC usp_CollectFragmentation – index fragmentation pt 2)
4. exec usp_IndexMaintenance – can run once a day, or weekly
5. EXEC usp_RunCheckDb – runs checkdb on all db’s over 50 gb
6. exec usp_UpdateStat_OnAllDbs – update database statistics
3. Get backups going:
1. enable adavance options if not enabled on MASTER
EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC sp_configure’xp_cmdshell’, 1
RECONFIGURE
2. Then set up a job weekly for System –
EXEC usp_Backup_db @path = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\’, @dbType = ‘System’, @bkpType = ‘Full’, @retention = 1, @liteSpeed = ‘N’
And a similar one for User:
EXEC usp_Backup_db @path =’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\’, @dbType = ‘User’, @bkpType = ‘Full’, @retention = 1, @liteSpeed = ‘N’
Do these things and you’ll have an Admin database that tracks the health of your system, and a more healthy set of indexes/backups.