MS SQL administration : Backup and Restore

How to take backup of a database

Full Backup
This backup includes mdf and ldf files.

BACKUP DATABASE <Databasename>
TO DISK = '<Destination path>'


Transaction Log Backup
This includes only the transaction log backup

 BACKUP LOG <Databasename>
TO DISK = '<Destination path>'

 Differential Backup
This includes all changes done after the last successfull Full backup
BACKUP DATABASE <Databasename>
TO DISK = '<Destination path>'
WITH DIFFERENTIAL

Files and File group Backup
This backup allows us to save individual SQL files
BACKUP DATABASE <Databasename> FILEGROUP='<FilegrpName>'
TO DISK = '<Destination path>'

 How to restore database

Restore Full Backup
RESTORE DATABASE <Databasename>
FROM DISK = '<Destination path>'

Restore Full + Differential Backup

 ----Restore Full backup with NORECOVERY command-----
RESTORE DATABASE <Databasename>
FROM DISK = '<Destination path>'
WITH NORECOVERY

Norecovery command will not make the restored DB live. It will wait for the next recovery restore to complete the restoration.

----Then restore Differential backup with RECOVERY command-----
RESTORE DATABASE <Databasename>
FROM DISK = '<Destination path>'
WITH RECOVERY

If no option is specified explictly, WITH RECOVERY option will be used.

Restore Backup to a particular point of time 
RESTORE DATABASE <Databasename>
FROM DISK = '<Destination path>'
WITH RECOVERY
STOPAT='JAN01,2013 06:00:00'

This restores all transactions in the backup made till 06:00 AM 01/01/2013.

Restore Transaction Log
RESTORE LOG <Databasename>
FROM DISK = '<Destination path>'

Comments

Post a Comment

Popular posts from this blog

VMware and Windows Interview Questions: Part 2

Image
VMware and Windows Interview Questions: Part 1 VMware and Windows Interview Questions: Part 2 VMware and Windows Interview Questions: Part 3 VMware and Windows Interview Questions: Part 4 VMware and Windows Interview Questions: Part 5 VMware and Windows Interview Questions: Part 6 VMware and Windows Interview Questions: Part 7 VMware and Windows Interview Questions: Part 8 Follow this link for scenario based VMware interview questions. Maximum number of LUNs that can be attached to a host (ESXi 5.0) 256 Maximum number of vCPUs that can be assigned to a VM (ESXi 5.0) 32
Deep dive »

VMware and Windows Interview Questions: Part 3

What is vSAN ? It is a hypervisor-converged storage solution built by aggregating the local storage attached to the ESXi hosts managed by a vCenter.  Recommended iSCSI configuration? A separate vSwitch, and a separate network other than VMtraffic network for iSCSI traffic. Dedicated physical NICs should be connected to vSwitch configured for iSCSI traffic. What is iSCSI port binding ? Port binding is used in iSCSI when multiple VMkernel ports for iSCSI reside in the same broadcast domain and IP subnet, to allow multiple paths to an iSCSI array that broadcasts a single IP address.
Deep dive »

VMware vMotion error at 14%

Issue While performing vMotion, the operation fails at 14% with the below error : A general system error occurred: Migrtion to host <Destination ESXi IP> failed with erro Connection closed by remote host, possibly due to timeout (0xbad003f). Migrate virtual machine:A general system error occurred: Migration to host <Destination ESXi IP> failed with error Connection closed by remote host, possibly due to timeout (0xbad003f). vMotion migration [-1062729272:1406020861428172] (19-71629048648008) failed to receive...  Scenarios Scenario 1: Your management network and vmotion network are in the same subnet using the same physical NIC.
Deep dive »