SQL Backup Recommendations

This is a guide showing how you can setup a daily backup routine of your Totalview database.

For SQL Server Express you need a batch file (.bat) containing a SQL script to run daily as a schedule task. For SQL Server Standard and Enterprise you need to setup 7 jobs in SQL Server Management Studio.

SQL Express

A designated AD-user needs to be created for backup. Password should be set to “Password Never Expires”, and the user has to be a member of the “Backup Operators” group.

Create a .bat file which executes a SQL script. The .bat file needs to run daily with Schedule Task in Windows.

Example:

“tvbackup.bat”:
@del /F /Q “C:\Backup\log\tvbackuplog.txt”
@del /F /Q “C:\Backup\Totalview.bak”
osql.exe /U sa /P 1234 /S NB-OEJ\OEJ /i “C:\Backup\BackupTV.sql” >> “C:\Backup\log\tvbackuplog.txt”
“backupTV.sql”:
USE master
go
EXEC sp_dropdevice ‘TotalviewBU’
EXEC sp_addumpdevice ‘disk’,’TotalviewBU’,’C:\Backup\Totalview.bak’
BACKUP DATABASE Totalview TO TotalviewBU
go

SQL Server Standard and Enterprise

A designated AD-user needs to be created for backup. Password should be set to “Password Never Expires”, and the user has to be a member of the “Backup Operators” group.

“Recovery Model” in databases must always be set to “Full” (Full Recovery Mode).

Start up SQL Server Management Studio and setup these 7 jobs:

1. Full backup of User Data databases – daily e.g. 01.00 o’clock

2. Full backup of System databases – daily e.g. 00.30 o’clock

3. Update Statistics – daily e.g. 02.00 o’clock

4. Cleanup of database and log – daily e.g. 05.00 o’clock

5. Rebuild Indexes. – each weekend (indexes are rebuilt offline)

6. Defragmentation of indexes – every day at 03.00 o’clock

7. Backup of Transaction Log – every 15 minutes during working hours (or at least once a day because of flush)

Example:

Figure 1. Full backup of System-databases. Integritycheck of database, full database backup and daily 00.30 o’clock

Figure 2. Full backup of User-databases. Integritycheck og databases. Daily 01.00 o’clock

Figure 3. Update Statistics of all databases. Daily 02.00 o’clock NB! this can take a long time. If it takes too long, the job has to be split up.

Figure 4: Cleaning up old backup files. Daily 05.00 o’clock

Figure 5: Rebuild Indexes. Recommend running weekend days (indexes are being rebuilt offline). NB! This can result in an oversized Database file, see figure 8

Figure 6: defragmentation of indexes, runs every day at 03.00 o’clock NB! Can result in a too large Database file, see figure 8

Figure 7: Backup Transaction Log – Every 15 minutes during working hours (or at least once a day because of flush) NB! “Verify backup integrity” must NOT BE CHECKED

Figure 8: Put Database in simple mode and back into Full Recovery

Use master;

ALTER DATABASE NAV501 SET RECOVERY SIMPLE;

Use master;

ALTER DATABASE NAV501 SET RECOVERY FULL;