What is the recovery model? List the
types of recovery model available in SQL Server?
Ans. Recovery
model basically tells SQL Server what data should be kept in the transaction
log file and for how long. A database can have only one recovery model.
It also
tells SQL server that which backup is possible in a particular recovery model
selected. There are three types of recovery model:
·
Full
·
Simple
·
Bulk-Logged
What are the types of backups available in SQL Server?
Ans. Different
possible backups are:
·
Full
backup
·
Differential
Backup
· Transaction Log Backup
·
Copy
Only Backup
·
File
and File group backup
What is Mirroring? What are the advantages of the Mirroring?
Ans. Mirroring
is a high availability solution. It is designed to maintain a hot standby
server which is consistent with the primary server in terms of a transaction.
Transaction Log records are sent directly from principal server to secondary
server which keeps a secondary server up to date with the principal server.
Advantages
of Mirroring are:
·
It is more
robust and efficient that Log shipping.
·
It has an
automatic failover mechanism.
·
The
secondary server is synced with the primary in near real time.
Which TCL Commands are available on the SQL Server?
Ans. There
are 3 TCL Commands in the SQL Server. These are as follows:
Commit: This command is used to
save the transaction permanently in the database.
Rollback: This is used to roll back
the changes that are done i.e. to restore the database in the last committed
state.
Save Tran: This is used for saving the
transaction so as to provide the convenience that the transaction can be rolled
back to the point wherever required.
What is the difference between a Local and a Global temporary table?
Ans. If
defined in inside a compound statement a local temporary table exists only for
the duration of that statement but a global temporary table exists permanently
in the database but its rows disappear when the connection is closed.
