Beginners guide to Backing up Databases

If your company doesn’t have an external tool to back up databases then these databases are backed up using ‘Native’ sql server backups. Native means that we use the backup utility which ships with SQL Server. The backups can be grouped in a job for System database, one for user databases and a job for Transaction log backups.

BACKUP T-SQL Syntax – http://msdn.microsoft.com/en-us/library/ms186865.aspx

Eg:

BACKUP DATABASE [master] TO DISK = 'F:\Backups\SystemDbs\master.bak' WITH init

To manage transaction logs, we use Backup Devices. These are virtual devices which manage the backups held in 1 physical file. More information here: http://msdn.microsoft.com/en-us/library/ms179313.aspx

--SEM5_TLOG is the backup device on the sql server
BACKUP LOG [SEM5] TO  [SEM5_TLOG] WITH  RETAINDAYS = 3

Looking in the Backup device shows that there are many transaction log backups held within 1 phyical OS file.

tlogs

Useful Scripts

Last transaction log backup for databases in FULL recovery:

SELECT D.[name] AS [database_name], D.[recovery_model_desc], [last_log_backup_date]
FROM sys.databases D LEFT JOIN  
   ( 
   SELECT BS.[database_name],  
       MAX(BS.[backup_finish_date]) AS [last_log_backup_date] 
   FROM msdb.dbo.backupset BS  
   WHERE BS.type = 'L' 
   GROUP BY BS.[database_name] 
   ) BS1 ON D.[name] = BS1.[database_name] 
WHERE D.[recovery_model_desc] <> 'SIMPLE' 
   --AND BS1.[last_log_backup_date] IS NULL 
ORDER BY D.[name];

Backup status for all databases:

SELECT 
SUBSTRING(s.name,1,100) AS 'Database',
ISNULL(CONVERT(Varchar(20),b.backup_start_date, 113),'NULL') AS 'Backup Date',
CASE 
WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
    THEN 'Backup is current within a day'
WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
    THEN 'Backup is current within a week'
WHEN b.backup_start_date > DATEADD(dd,-28,getdate())
    THEN 'Backup is current within a month'
ELSE 'Not being Backed up'
END AS 'Comment'
FROM master..sysdatabases    s
LEFT OUTER JOIN   msdb..backupset b ON s.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
                           FROM msdb..backupset
                           WHERE database_name = b.database_name
                           AND type = 'D') -- full database backups only
WHERE s.name <> 'tempdb'
ORDER BY b.database_name ASC,b.backup_start_date DESC

Backup Striping

Striped Backup to Disk:

BACKUP DATABASE northwind
TO disk='c:\nwind\n1.bak', disk='c:\nwind\n2.bak', disk='c:\nwind\n3.bak'
WITH init, NAME='test'

Single Backup to Disk:

BACKUP DATABASE northwind
TO DISK='c:\nwind\nfull.bak'

Striped Backup to Device:

BACKUP DATABASE northwind
TO backupdevice1, backupdevice2, backupdevice3
WITH init, name='test'

Single Backup to Device:

BACKUP DATABASE northwind
TO backupdevice1

Changing Recovery Model

Dynamic script to generate syntax for changing Simple recovery to Full recovery:

SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL WITH NO_WAIT'
FROM sys.databases where name in (
SELECT name from sys.databases where [recovery_model_desc] = 'SIMPLE')

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: