Friday, May 27, 2011

Backup and Restore commands of SQL Server 2005/2008

ü  Backup commands:

Full Backup:

Backup database <dbanme>
To disk=N’path’
With init,stats=10

Differential Backups:

Backup database <dbanme>
To disk=N’path’
With differential, init, stats=10

Log Backup:

Backup LOG <dbname>
To disk =N’path’
With init, stats=10

Tail Log Backup:

Backup LOG <dbname>
To disk =N’path’
With NO_TRUNCATE, init, stats=10

ü  Restore commands:

Full/Differential backup restoring:

Restore database <dbname>
From disk=N’C:\dbname.bak’

Log restores:

Restore log <dbname>
From disk=N’C:\.. .trn’
With norecovery

Tail Log Backup Restore:

Restore log <dbname>
From disk=N’C:\.. .trn’
With recovery,
STOPAT = ‘Feb 1, 1985 3:00:00 AM’


Thursday, May 26, 2011

What is Schema in SQL Server 2005/2008

It is a logical container of objects. A schema can have all types of objects and it can have objects owned by different owners. A set of objects can be grouped as a schema.

The default schema is ‘dbo’, any object created without specifying particular schema comes under dbo schema.

Users can be restricted to access only to objects belongs to a schema.

Wednesday, May 25, 2011

BULK_LOGGED Recovery Model

This model is to be used only while performing bulk operations only as we cannot perform point in time recovery in this model.

I always learnt it minimally logs bulk logged operations and only learnt this point. So finally in one interview a smart guy asked what it logs I said “it logs the bulk operation minimally and fully logs other transactions”. He asked minimally means what it will log actually. I don’t have any answer other than a smile.

Later went back to home and started asking the only guru available always ‘google’ and found below details:

A database set to this model will log only the details of which extents got affected due to bulk operation but the contents of the pages are not logged and hence the log file will not grow huge. So when performing backup only the affected extents due to bulk operation will be copied to the log backup.

So we are sure now if any log file backup contains bulk logged operations we cannot perform point in time recovery. How sql server knows what extents to copy to log backup is, it keeps a bitmap to all the extents that have been modified that is a bit is set to 1 on every extent that is changed due to bulk operation and these are copied.

Saturday, May 21, 2011

Isolation levels in SQL Server 2008.

Isolation levels:

These levels are different ways of isolating/separating execution of different transactions from each other.


Read Un-Committed:
This allows reading un-committed (dirty reads) data.

Read Committed (default isolation level)
This allows reading only committed data but it can’t obtain and hold lock hence causes issues like Lost Updates, Nonrepeatable reads and Phantom reads.

Repeatable Read:
This allows reading only committed date and also it can obtain and hold locks till the transactions are completed. But it cannot avoid phantom reads as it cannot manage range locks. A session1 runs select transaction retrieving data depending on some where condition like sal<10000 and another session2 tries to insert new row with sal=5000, if session1 run select again with condition sal<10000 it will show the new row sal=5000 also which is a phantom read because this row was inserted before the session1 transaction is completed and it should not show the new row but it shows. This can be avoided in Serializable.

This is highest of isolation level. It avoids all concurrency problems. But this holds locks until the transaction is complete. If any read is happening in a transaction and another transaction tries to update the same row it waits until the first read transaction is completed. This kind of locks can be avoided in snapshot isolations.

It works using ROW Versioning. So when any row update happening a version of already committed row is kept in tempdb and in between the update if any select command fires for the same row the data will be shown of the row version in temdb. Until the update transaction is done sql will use the row version already in tempdb. This causes performance problems when situations raise like too many row versions are stored in tempdb as too many updates are happening on same row. So this isolation can be used on less frequently updated tables.

Read Committed Snapshot:
It also works on ROW Versioning but it also avoids performance problems raise in snapshot isolation. This takes snapshot of the row version for every statement execution in a transaction but snapshot isolation takes snapshot at transaction level means it maintains the older row version until the entire transaction is completed.

Wednesday, May 18, 2011

How to truncate log file.

Once I was asked a question how to truncate log file. In a scenario where log file is growing rapidly and the disk is almost full.

We can use ‘backup log’ command with any of the options TRUNCATE_ONLY, NO_LOG this options will truncate the log file without taking the backup of log files.


This command will help in truncating the log file size. One thing we have to make note is after running this command we need to take fresh FULL/Differential backup as the above command will break the log sequence and hence it won’t allow to take any log backup. So to avoid any issue we must take a full backup.

TRUNCATE_ONLY/ NO_LOG: (This options is no longer valid in SQL Server 2008)

NO_TRUNCATE option is used when needed to backup a corrupt database.

To get the entire list of features that are no longer valid in SQL Server 2008 please check this link.

Sunday, May 15, 2011

New Features in SQL Server 2008

TSQL Merge Command
Policy Based Management
Date, Time & Date Time Offset

Complete list of SQL Server 2008 interview questions by Pinal Dave: