10 Myths about Backups in SQL Server

By -
There are various types of backups in SQL Server:
  1. Full backup : A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored.
  2. Transaction Log Backup: Transaction log backups are only possible in the FULL or BULK_LOGGED recovery models. A transaction log backup contains all the transaction log records generated since the last log backup and is used to allow the database to be recovered to a specific point in time.
  3. Differential Backup: A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.
  4. Copy Only Backup: A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups.


Myth 1: A full backup only contains data file information.
The backup command contains all the data in the data files and some portion of the transaction log. The backup contains some portions of the transaction log so that database consistency can be maintained and roll forward/roll back operations can take place once the database is restored.

Myth 2: Transaction log and differential backups are possible for the master database in full recovery model.
We cannot take transaction log or differential backups of the master database. This has been configured because the master database cannot be in a restoring mode as this database is essential for your SQL Server to be online. Thus we can only take full and copy only backups for the master database. We can change the recovery model of master database from simple to bulk logged or full.

Myth 3: We cannot take transaction log and differential backup of model and msdb database.
We can perform all kind of backup and restore operation on model and msdb database. Recovery model of both the databases can be changed from simple to bulk logged or full. One thing to keep in mind while restoring the msdb database is that your SQL Server Agent Services should not be running otherwise it will not allow an exclusive access on the msdb database.

Myth 4: We can take backup of tempdb database and we can change the recovery model of the database. 
We cannot take backup of tempdb database nor we can change the recovery model of the database. The recovery model of tempdb will always be simple.

Myth 5: Transaction log backup cannot be restored on model and msdb database
Model and msdb databases can be restored will full and subsequent transaction log or differential backups.
Two things need to be taken into consideration while restoring model and msdb:
1.       Take a tail log backup of both the database
2.       SQL agent services should be in stopped mode, else exclusive lock on msdb database cannot be acquired.

Myth 6: Full and differential backup breaks the log chain.
A full backup doesn’t break a log chain. A log chain is only broken in two scenarios as the LSN mismatch cause the issue.
  1. Change of recovery model from full or bulk logged to simple and then back to full or bulk logged.
  2. Missing log backup

Myth 7: Differential backups are thought to be incremental backups
Differential backups are not incremental backups, rather are cumulative backups as they contain all the data that has been modified from the last full backups. Transaction log backups are incremental backup as it contains the records of the transaction that has taken place after the previous transaction log backups.

Myth 8: We cannot take full and transaction log backup concurrently.
Yes, concurrent full and log backups are possible from SQL server 2005 onwards, but the transaction log will be cleared only when the full backup has finished.

Myth 9: Table level backup is possible in SQL Server
No. Table level backup is not possible in SQL server, thus we can take only take full backup, file group backup, transaction log backup.

Myth 10: Mirrored database cannot be backed up.
We cannot backup a mirrored database. There is only one way to see the content of the mirrored database and that is by generating database snapshot.

Comments

Post a Comment

Popular posts from this blog

Index Clean-Up Scripts

By -
dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Un-Used Indexes Script Declare @dbid int     , @dbName varchar(100);   Select @dbid = DB_ID()     , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count)  As (     Select [object_id]         , index_id         , Sum([rows]) As 'row_count'         , Count(partition_id) As 'partition_count'     From sys.partitions     Group By [object_id]         , index_id )    Select Object_Name(i.[object_id]) as objectName         , i.name         , Case              When i.is_unique = 1          ...
Read more

forgot sa password and no logins are added

By -
Image
Stop the SQL services Edit the properties of the SQL Service Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters Start the SQL services. These are now running in Single User Mode. Start CMD on tthe SQL server Start the SQLCMD command. Now we create a new user. Enter following commands CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy Go Now this user is created Now we grant the user a SYSADMIN roles using the same SQLCMD window. sp_addsrvrolemember ‘recovery’, ‘sysadmin’ go
Read more

The SQL Server DBA’s Guide to Teradata

By -
Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database . Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named. Release History: 2012-08-17 – Added UPDATE examples; reformatted 2012-08-12 – Added date examples 2012-08-08 – Initial Release SELECT Statements Simple SELECT SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...
Read more