SQL Server Database Backups – The Best Practices

Backup is an important key component of a disaster recovery strategy. Using a valid and restorable backup is the last thing when the moment comes to execute a real disaster recovery scenario during an emergency downtime.

Here are some points to be follow as

Make sure you are not storing your backups in the same physical location as the database files. When your physical drive goes bad, you should be able to use the other drive or remote location that stored the backups in order to perform a restore. Keep in mind that you could create several logical volumes or partitions from a same physical disk drive. Carefully study the disk partition and logical volume layouts before choosing a storage location for the backups.
Its necessary to restore the backups on a test server and verify that you can restore with all the options and conditions you need to use during a planned or un-planned downtime. Use the verification options provided by the backup utilities of BACKUP TSQL command, SQL Server Maintenance Plans or other your backup software or solution if you are using.
Its always also recommended to use advanced features like BACKUP CHECKSUM to detect problems with the backup media itself, like as below

BACKUP DATABASE [VirendraTest] TO DISK = N’D:\Database\VirendraTest.Bak’ WITH CHECKSUM;
GO

As we know there are 5 System Databases as MASTER,MSDB,MODEL,TEMPDB ( All these are Primary System Database ) & hidden read only RESOURCE.
Primary System Databases

Master – It Contains information about logins and information about all other databases

MSDB – It Contains Jobs, Operators, Alerts, Backup and Restore History, Database Mail information …….. etc..

MODEL – It Contains a model for all new databases. If you want certain objects to be in all new databases this is where you configure this information.

TEMPDB – Its created each time SQL Server starts and no needs for Backup.

Resource
Database – The Resource database is a read-only hidden database that contains all the system objects which are included within SQL Server. The DBA needs to perform a file-based copy of mssqlsystemresource.mdf and mssqlsystemresource.ldf files of the Resource database as SQL Server doesn’t support backing up the Resource database. In SQL Server 2005 the Resource database is available in “<drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\” location and in SQL Server 2008 the Resource database is available in “<drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\” location

/* Copy Resource Database Files Using XP_CMDSHELL */

EXEC xp_cmdshell ‘COPY /Y “D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\mssqlsystemresource.mdf” “E:\SYSDBBKUP”‘
GO

EXEC xp_cmdshell ‘COPY /Y “D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\mssqlsystemresource.ldf” “E:\SystemDatabaseBackups”‘
GO