From time to time, some friends or colleagues I have worked with in the past, either become a database administrator (DBA) or assume some DBA responsibilities. And they always ask me the same question … what should I be doing as a DBA?
Well, if that sounds like you or you have ever wondered that yourself, you have come to the right place!
So, one of a DBA’s primary responsibilities is to protect their company’s data. Of course, there are many different aspects of how data can be protected. But, the most basic way is to make sure you have backups of a database in case of a problem.
Now, even before starting to backup a database, a DBA needs to have a backup strategy. And the thing to keep in mind is that not every database, even ones on the same server, needs to follow the exact same backup strategy. The determination on what type of strategy to use depends on the database and its usage.
For instance, one database may be used in monetary transactions, so we would need to protect the data (or recover it) up to any point in time. Any potential loss of data could be disastrous and would not be tolerated by the business. On the other hand, a second database could be used for reporting and just loaded once per day. In this case, we may only need to recover the data to the last load of the database. So, for these two databases, the backup strategy would differ, even if they resided on the same server.
Implement a Backup Strategy
In order to implement a backup strategy, the first thing that needs to be understood is recovery models. A recovery model controls how transactions are logged in the database, what types of backups can be taken, and what restore operations are available.
The three recovery models used in SQL Server are: simple, full, and bulk-logged. (Recovery Models: http://msdn.microsoft.com/en-us/library/ms189275.aspx).
The basic differences between recovery models are as follows:
- Simple – Since the space is re-used in the transaction log, there is no need for log maintenance (or backing up the log). Additionally, you can take full and/or differential backups, but not log backups. So, a database in this mode can’t be restored to any point in time.
- Full – All transactions are logged, and you need to perform log maintenance, otherwise the log file will grow out of control. All types of backups can be taken and the database can be restored to any point in time, provided log backups are being performed.
- Bulk logged – Similar to the full recovery model, but does minimal logging for most bulk copy operations against the database.
Code to determine the recovery model of a database:
name AS DatabaseName,
DATABASEPROPERTYEX (name, N'Recovery') AS RecoveryModel
Now, once we determine what recovery model we need, now we have to setup a backup of the database.
The three types of backups that can be performed are: full, differential, and log. (Backup command: http://msdn.microsoft.com/en-us/library/ms186865.aspx)
The basic differences between the types of backups are as follows:
- Full – A complete database backup that can be restored to only the time at which it was taken.
- Differential – A database backup of what has changed since the last full backup.
- Transaction log – A backup of the transactional data which allows restoration to any point in time, which can only be taken if there is a full backup.
Basic sample code to take a backup of a database:
-- Back up the full database
BACKUP DATABASE [DatabaseName]
TO DISK = '[FilePath]'
-- Back up the database with a differential
BACKUP DATABASE [DatabaseName]
TO DISK = '[FilePath]' WITH DIFFERENTIAL
-- Back up the database log
BACKUP LOG [DatabaseName]
As you may imagine, taking a full database backup may take a considerable amount of time and resources, if the database is large. That is why generally full database backups are taken during a company’s off-hours or even during a certain window to minimize the performance impact. However, if you decide to have your databases in the full recovery model, you will most likely be taking transaction log backups during the day. Otherwise, the log file may grow out of control and fill the disk, which can bring that database down.
Additionally, at times when I am running a backup, I may wonder when it will finish. Here is a handy piece of code to determine when a backup will finish:
DB_NAME(r.database_id) AS DatabaseName,
s.text AS BackupCommand,
r.start_time AS StartTime,
r.percent_complete AS PercentComplete,
CAST(((DATEDIFF(s,start_time,GETDATE()))/3600) AS VARCHAR) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GETDATE())%3600)/60 AS VARCHAR) + 'min, '
+ CAST((DATEDIFF(s,start_time,GETDATE())%60) AS VARCHAR) + ' sec' AS RunningTime,
CAST((estimated_completion_time/3600000) AS VARCHAR) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 AS VARCHAR) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 AS VARCHAR) + ' sec' AS EstimatedTimeToGo,
DATEADD(SECOND,estimated_completion_time/1000, GETDATE()) AS EstimatedCompletionTime
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('BACKUP DATABASE', 'BACKUP LOG')
So, there you have it. The first thing you should be doing as a DBA is making sure you protect your data by backing up your databases!