A Learning HUB for Database Administration: Backup, Restore & Recovery

BACKUP AND RESTORATION

---------------------------------------------------------------------------------------------

CHECKPOINT

* It is a program executed by SS to take all committed transactions from T.Log file into data file.

* It takes all committed transaction details (LSN, Pageid) from T.Log file and submits to lazy writer.

* Lazy Writer is one of a background process which takes the pages from Data Cache and writes to data file.

* Checkpoint truncates T.Log file if the recovery model is SIMPLE.

* Checkpoint occurs in the following scenarios

* In other recovery models (FULL and BULK LOGGED) checkpoint cannot truncate T.Log file.

When Checkpoint Occurs?

1. Periodically (Depends on recovery interval of SS)

* R.C on Server --> Take properties --> Database settings -->

Recovery interval =1

2. When the database backup is about to start

3. If the server is started.

4. If the T.Log is 70% full and it is in Log truncate mode.

FAQ: - What is difference between checkpoint and lazy writer?

Ans:

Checkpoint reads all committed transaction details from previous checkpoint and it submits the details like LSN no’s and effected page ids to lazy writer. Lazy writer copies all the pages to data file.

RECOVERY MODELS

It is database level property.

The following features depends on recovery model

· What is recorded in T.Log file

· Point in time recovery is possible or not?

· T.Log backups are possible or not?

· When T.Log is truncated?

· Log shipping and mirroring

etc

SQL Server supports 3 recovery models

1. FULL

2. BULK LOGGED

3. SIMPLE

We can set recovery model as follows

USE MASTER

ALTER database <dbName> SET RECOVERY FULL/BULK_LOGGED/SIMPLE

Ex:

use master

ALTER DATABASE Test SET RECOVERY FULL

--To find recovery model of a database

select * from sys.databases

select DATABASEPROPERTYEX('Test','Recovery')

1. Full Recovery Model

The "Full" recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated. The way this works is that all transactions that are issued against SQL Server first get entered into the transaction log and then the data is written to the appropriate data file. This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason. So when the database is set to the "Full" recovery model since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table.

Explanation

The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.

Here are some reasons why you may choose this recovery model:

Data is critical and data cannot be lost.

You always need the ability to do a point-in-time recovery.

You are using database mirroring

Type of backups you can run when the data is in the "Full" recovery model:

Complete backups, Differential backups, File and/or Filegroup backups, Partial backups

Copy-Only backups, Transaction log backups

How to set the full recovery model using T-SQL.

ALTER DATABASE dbName SET RECOVERY recoveryOption

--Example: change AdventureWorks database to "Full" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY FULL

Scenario

We have configured every Sunday 9pm FULL backup. Every day 9pm differential

backups and every 1hr T.Log backups.

Database was failed at 9:30am on Friday. Then what are the db recovery steps?

Steps

1. Take 9 to 9:30 duration transactions into backup i.e. taking tail log backup.

2. Restore last sunday FULL backup.

3. Restore differential backup generated on Thursday.

4. Restore all Log backups upto 9am (12 Log backups)

5. Restore tail log backup.

FAQ:- My db recovery model is SIMPLE. My T.Log file is growing fastly. What may be the scenarios?

Ans:

· If there are concurrent un-committed transactions.

· If the db is configured for transactional replication and the transactions made towards

Replication is not delivered from Log file into distributor.

2. BULK LOGGED

The "Bulk-logged" recovery model sort of does what it implies. With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... That are not fully logged in the transaction log and therefore do not take as much space in the transaction log.

Explanation

The advantage of using the "Bulk-logged" recovery model is that your transaction logs will not get that large if you are doing bulk operations and it still allows you to do point in time recovery as long as your last transaction log backup does not include a bulk operation as mentioned above. If no bulk operations are run this recovery model works the same as the Full recovery model. One thing to note is that if you use this recovery model you also need to issue transaction log backups otherwise your database transaction log will continue to grow.

Minimally logged operations

Minimally logged operations are ones that don’t write every single individual row because doing so modifies the transaction log. At a minimum, enough information has to be logged when a minimally logged operation is performed to allow SQL Server to rollback a transaction that has failed. Even when SQL Server logs the changed data, it doesn’t actually write it to the log in the sequence that it was changed.

These minimally logged operations include the following.

· SELECT INTO This command always creates a new table in the default filegroup.

· Bulk import operations these include the BULK INSERT command and the bcp executable.

· INSERT INTO . . . SELECT this command is used in the following situations.

o When data is selected using the OPENROWSET (BULK. . .) function.

o When more than an extent’s worth of data is being inserted into a table without non-clustered indexes and the TABLOCK hint is used. If the destination table is empty, it can have a clustered index. If the destination table is already populated, it can’t. (This option can be useful to create a new table in a non-default filegroup with minimal logging. The SELECT INTO command doesn’t allow specifying a filegroup.)

· Partial updates Columns having a large value data type receive partial updates (as discussed in Chapter 8, “Special storage”).

· WRITE This clause is used in the UPDATE statement when inserting or appending new data.

· WRITETEXT and UPDATETEXT These statements are used when inserting or appending new data into LOB data columns (text, ntext, or image). Minimal logging isn’t used in these cases when existing data is updated.

· Index operations These include the following.

CREATE INDEX, including indexes on views

ALTER INDEX REBUILD or DBCC DBREINDEX

· DROP INDEX, in which the creation of the new heap is minimally logged, but the page de-allocation is always fully logged

Here are some reasons why you may choose this recovery model:

Data is critical, but you do not want to log large bulk operations

Bulk operations are done at different times versus normal processing.

You still want to be able to recover to a point in time

Type of backups you can run when the data is in the "Simple" recovery model:

Complete backups

Differential backups

File and/or Filegroup backups

Partial backups

Copy-Only backups

Transaction log backups

How to set the bulk-logged recovery model using T-SQL.

ALTER DATABASE dbName SET RECOVERY recoveryOption

--Example: change AdventureWorks database to "Bulk-logged" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

3. SIMPLE

The "Simple" recovery model does what it implies, it gives you a simple backup that can be used to replace your entire database in the event of a failure or if you have the need to restore your database to another server. With this recovery model you have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup). With this recovery model you are exposed to any failures since the last backup completed.

Explanation

The "Simple" recovery model is the most basic recovery model for SQL Server. Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions. Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed. Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the "Full" recovery model.

Here are some reasons why you may choose this recovery model:

Your data is not critical and can easily be recreated

The database is only used for test or development

Data is static and does not change

Losing any or all transactions since the last backup is not a problem

Data is derived and can easily be recreated

Type of backups you can run when the data is in the "Simple" recovery model:

Complete backups

Differential backups

File and/or Filegroup backups

Partial backups

Copy-Only backups

Difference between SIMPLE vs FULL recovery model

BASE	SIMPLE Recovery Model	FULL Recovery Model
Incremental Backups	Transaction LOG database backup options are not available in Simple recovery Model.	Transaction LOG database backup options are available in FULL / Bulk Logged recovery Model.
POINT in Time Recovery	Point in Time recovery is not supported.	POINT IN Time recovery can be performed in case of FULL recovery model
DISK Space	No large disk space is required for transaction logs.	LARGE DISK Space might be required in case of FULL Recovery model for heavily utilized OLTP system.
Auto Truncate	Simple recovery, forces the log to truncate (marked area for reuse) when the database writes data to the disk (CHECKPOINT)	DBA need to manually backup and clear the database log file.
Transaction Behavior	When the Transaction completes, relevant portion of the log is marked as inactive and marked for reuse	When the Transaction completes, relevant portion of the log is still kept occupied in log.
PERFORMENCE	Permits high-performance bulk copy operations.	Delay the bulk logged operations as every transactions is written t log file. In case if Log file fills, it grows, which is added delay.
Data LOSS	In case complete disk corruption, Simple recovery might loose some transactions which occurred after full backup	Data can by recovered up to any level.
LOG Management	SQL Server automatically managed transaction log.	DBA assistance is required to manage transaction log, in case of no backup log plan, Log file will keep growing and you might see log files is larger than data files.
Virtual Log File Count	As SQL Server automatically clears transaction log on checkpoint, which keeps transaction log size and control and result higher possibility for not to increase VLF counts	As every transaction is recorded in transaction and log will keep growing in case of high transaction which might result in increase number of VLF, which affect performance. Check my articlehttp://goo.gl/8SNuH for more details

Difference between FULL recovery AND Bulkloged model

In bulk logged recovery mode certain bulk operations are minimally logged. In FULL recovery mode these are fully logged. These bulk operations are as mentioned below

1. SELECT INTO

2. BULK IMport operations including BULK INSERT and BCP

3. INSERT INTO SELECT command using the OPENROWSET (BULK) function

4. Partial updates to columns having large value data type

5. Using WRITE clause in UPDATE statements

6. Index operations e.g CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX

In Bulk Logged Recovery Mode when you execute these operations SQL Server only logs the fact that these operation occurred and information about space allocation. The actual change in the data is maintained in the BCM (Bulk Changed Map)

Since the actual changes are not recorded in the log file, the log file size in the relatively less in size but this tradeoff comes with the price of increased backup time. This is so because during the log backup it’s not just the log being backup, but also the extents that are marked by the Bulk Changed Map as changed.

SQL SERVER – Four Different Ways to Find Recovery Model for Database

Perhaps, the best thing about technical domain is that most of the things can be executed in more than one ways. It is always useful to know about the various methods of performing a single task. Today, we will observe four different ways to find out recovery model for any database.

Method 1

Right Click on Database >> Go to Properties >> Go to Option. On the Right side you can find recovery model.

Method 2

Click on the Database Node in Object Explorer. In Object Explorer Details, you can see the column Recovery Model.

Method 3

This is a very easy method and it gives all the database information in one script.

SELECT name AS [Database Name],

recovery_model_desc AS [Recovery Model]

FROM sys.databases

Method 4

This method provides only one database at a time.

SELECT 'ADVENTUREWORKS' AS [Database Name],

DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY')

AS [Recovery Model]

How to change recovery model for multiple databases in SQL Server

Database Administrators have always had a tough time to ensuring that all the SQL Servers administered by them are configured according to the policies and standards of organization. Using SQL Server’s Policy Based Management feature DBAs can now manage one or more instances of SQL Server 2008 and check for policy compliance issues. In this article we will utilize Policy Based Management (aka Declarative Management Framework or DMF) feature of SQL Server to implement and verify database settings on all production databases.

It is best practice to enforce the below settings on each Production database. However, it can be tedious to go through each database and then check whether the below database settings are implemented across databases. In this article I will explain it to you how to utilize the Policy Based Management Feature of SQL Server 2008 to create a policy to verify these settings on all databases and in cases of non-complaince how to bring them back into complaince.

Database setting to enforce on each user database:

· Auto Close and Auto Shrink Properties of database set to False

· Auto Create Statistics and Auto Update Statistics set to True

· Compatibility Level of all the user database set as 100

· Page Verify set as CHECKSUM

· Recovery Model of all user database set to Full

· Restrict Access set as MULTI_USER

Configure a Policy to Verify Database Settings

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio

2. In the Object Explorer, Click on Management > Policy Management and you will be able to see Policies, Conditions & Facets as child nodes

3. Right click Policies and then select New Policy…. from the drop down list as shown in the snippet below to open the Create New Policy Popup window.

4. In the Create New Policy popup window you need to provide the name of the policy as “Implementing and Verify Database Settings for Production Databases” and then click the drop down list under Check Condition. As highlighted in the snippet below click on the New Condition… option to open up the Create New Condition window.

5. In the Create New Condition popup window you need to provide the name of the condition as “Verify and Change Database Settings”. In the Facet drop down list you need to choose the Facet as Database Options as shown in the snippet below. Under Expression you need to select Field value as @AutoClose and then choose Operator value as ‘=’ and finally choose Value as False. Now that you have successfully added the first field you can now go ahead and add rest of the fields as shown in the snippet below.

Once you have successfully added all the above shown fields of Database Options Facet, click OK to save the changes and to return to the parent Create New Policy – Implementing and Verify Database Settings for Production Database windows where you will see that the newly created condition “Verify and Change Database Settings” is selected by default.

Database Backups

* It is the process of taking data, meta data and services present in the database into O/S file.

* Backup can be generated in two types of files

*.BAK (Can consists of any type of backup)

*.TRN (Transaction Log backup)

* Multiple backups also we can write in a single file which maintains backups with position no.

* Advantages

* We can provide security for data.

* We copy/move database from one server to another.

* We can implement standby solutions with Log shipping.

* To recover data upto point of failure i.e. to avoid data loss.

Who can take backup?

* Only members of the following role can take backup

* SysAdmin

* Can take backup of any database

* db_owner

* Can take backup of respective db

* db_backupoperator

* Can take backup of respective db

What backup contains?

* Every backup file consists of two parts

1. Backup Header

2. Backup Data

* Header consists of details of backup like

* Type of backup

* Date generated

* User name

* Host name

* Version no

* Size

etc

* Second Part consists of the following details

Types of backups

Full Backup

Differential Backups

Transaction Log

File or Filegroup backup

Mirrored Backups

Striped Backups

Copy-Only Backups

Tail Log backup

Partial Backup

Full Backup:

syn:

use master

backup database <dbName> to disk='................bak'

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

Ex: Take full backup of AdventureWorks database into d:\SQL_backups folder

Steps

----- 1. Create the folder d:\SQL_backups

2. Grant read write permission to service account on the above folder.

3. Take new query

use master

backup database AdventureWorks to disk='d:\SQL_backups\Adv_Full.bak'

with stats=10

4. To disp backup header

use master

restore headeronly from disk='d:\SQL_Backups\Adv_Full.bak'

5. To disp files taken into backup

restore filelistonly from disk='d:\SQL_Backups\Adv_Full.bak'

6. To verify backups

restore verifyonly from disk='d:\SQL_Backups\Adv_Full.bak'

FAQ:- How can we imagine or calculate size of backup?

Ans:

backup size = sum of used extents from all data files * 64 + backup header (KB)

Note: To check the used extents

use AdventureWorks

dbcc showfilestats

FAQ:- What is your backup strategy?

Ans: * Backup strategy refers to types of backups and frequency which we have scheduled.

* It depends on

* Size of database

* Recovery model

* Transaction Rate

* Availability (24/7)

Scenario1

* My database size is 6GB and daily 500 transactions.

Suggestible strategy

* Daily Full backups

* Every 4 hrs Differential backups.

Scenario2

* My Database size is 800GB. Daily 50000 transactions.

Suggestible strategy

* Weekly FULL backup.

* Daily Differential backup

* Every 1 hr T.Log backups.

2. Differential Backups

* Changes made after full backup can be taken into file with differential backup.

* It is generated quickly as compared with full backup.

* It reduces time of recovery in case of database failure.

syn:

use master

backup database <dbName> to disk='..........'

with differential

Ex:

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH DIFFERENTIAL

Steps

------ 1. Go to AdventureWorks database.

2. Perform some changes in any table.

3. Take new query

use AdventureWorks

backup database Test to disk='d:\SQL_backups\Adv_Full.bak'

with differential

4. View backup header

RESTORE HEADERONLY FROM DISK='d:\backups\Adv_Full.bak'

5. Getting backups information

* We can get backup details from msdb database.

* In MSDB we have 5 tables related to backups.

* backupset

* Complete backup details

* Types

D - Full

I - Differential

F - File/filegroup

L - T.Log backup

* backupmediafamily

* Consists of file path

* backupmediaset

* Compression and mirrors details

* backupfile

* File names which are taken into backup.

* backupfilegroup

* Filegroup names which are taken into backup.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL

3. Transaction Log Backups

* It takes backup of T.Log file only.

* It takes all committed transactions from the last full or differential or log backup.

* These are incremental backups.

* It is possible only in FULL and BULK LOGGED recovery models only.

Advantages

1. To truncate the T.Log file periodically.

2. To get transactions upto point of failure.

3. To implement T.Log shipping

syn:

use master

BACKUP LOG <dbName> TO DISK='...........'

EX:

1. Go to Test database

2. Perform some changes in any one table.

3. Take new query

use master

BACKUP LOG AdventureWorks TO DISK='d:\Backups\Adv_Full.bak'

Scenario

* My database recovery model was SIMPLE and I have taken FULL backup. I was unable to take T.Log backup hence I have changed recovery model to FULL. Can I take T.Log backup now?

Ans:

Not allowed. Full backup of SIMPLE recovery model cannot work as base for T.Log backups. Once again we have to take FULL backup then T.Log backup.

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'

FAQ:- My Backup was failed. What may be the possible scenarios?

* No disk space.

* Server was busy

* msdb was offline

* Agent service was stopped.

* Network problem.

4. File or Filegroup backups

As mentioned above you can back up each filegroup individually. The one advantage of using filegroup backups over file backups is that you can create a Read-Only filegroup which means the data will not change. So instead of backing up the entire database all of the time you can just backup the Read-Write filegroups.

syn: use master

backup database <dbName> filegroup='filegroupName'

to disk='..............'

Ex:

use master

backup database Test filegroup='primary'

to disk='d:\backups\Test_Primary_FG.bak'

Ex: Create a backup device with the name Test_BD and take full backup into the device.

--step1: Creating backup device

use master

sp_addumpdevice @devtype = 'disk', @logicalname = 'Test_BD',

@physicalname = 'd:\backups\Test_BD.bak'

--step2: Taking full backup of Sales db into backup device

BACKUP DATABASE Sales TO Test_BD

File Bakcup:

BACKUP DATABASE TestBackup FILE = 'TestBackup'

TO DISK = 'C:\TestBackup_TestBackup.FIL'

GO

BACKUP DATABASE TestBackup FILE = 'TestBackup2'

TO DISK = 'C:\TestBackup_TestBackup2.FIL'

GO

File Group

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly'

TO DISK = 'C:\TestBackup_ReadOnly.FLG'

Copy Only Backups

* Normal backups effects the backup plan and recovery steps.

* If we want to take backup without effecting existing plan we can use Copy-Only backups.

* SS supports

* Copy-only Full backup

* Copy-Only T.Log backup

syn:

backup database/log <dbName> to disk='.........'

with copy_only

ex: Backup database Test to disk='d:\backups\Test_copyOnly.bak'

with copy_only

Striped Backups

* If the size of backup is large where there is no required disk space in any drive we can split backups into multiple files in different disks. These backups are called striped backups.

* If the server consists of multiple CPUs then it provides better performance.

ex:

BACKUP DATABASE [AdventureWorks]

DISK = 'd:\backups\Adv1.bak',

DISK = 'd:\backups\Adv2.bak'

WITH NOFORMAT,STATS = 10

A Partial backup can be issued for either a Full or Differential backup. This can not be used for Transaction Log backups. If a filegroup is changed from Read-Only to Read-Write it will be included in the next Partial backup, but if you change a filegroup from Read-Write to Read-Only you should create a filegroup backup, since this filegroup will not be included in the next Partial backup.

Create a partial backup of the TestBackup database

For this example I created a new database called TestBackup that has three data files and one log file. Two data files are the PRIMARY filegroup and one file is in the ReadOnly filegroup. The code below shows how to do a partial backup.

Create a full partial backup

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS

TO DISK = 'C:\TestBackup_Partial.BAK'

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS

TO DISK = 'C:\TestBackup_Partial.DIF'

WITH DIFFERENTIAL

Restores

* It is the process of creating database from existing backup file.

* It creates database with all the transactions, captured in the backup.

* To restore database the user must have either

* sysadmin or

* dbcreater role

* By default once we try to restore database the data file and T.Log files of database are

created in similar path of source server.

syn:

use master

restore database/Log <dbName> from disk='.............'

[with replace/norecovery/recovery/standby/Move]

SQL Server BACKUP DATABASE command

Create a full backup to disk

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

Create a differential backup

This command adds the "WITH DIFFERENTIAL" option.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH DIFFERENTIAL

Create a file level backup

This command uses the "WITH FILE" option to specify a file backup. You need to specify the logical filename within the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILE = 'TestBackup'

TO DISK = 'C:\TestBackup_TestBackup.FIL'

Create a filegroup backup

This command uses the "WITH FILEGROUP" option to specify a filegroup backup. You need to specify the filegroup name from the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly'

TO DISK = 'C:\TestBackup_ReadOnly.FLG'

Create a full backup to multiple disk files

This command uses the "DISK" option multiple times to write the backup to three equally sized smaller files instead of one large file.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks_1.BAK',

DISK = 'D:\AdventureWorks_2.BAK',

DISK = 'E:\AdventureWorks_3.BAK'

Create a full backup with a password

This command creates a backup with a password that will need to be supplied when restoring the database.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH PASSWORD = 'Q!W@E#R$'

Create a full backup with progress stats

This command creates a full backup and also displays the progress of the backup. The default is to show progress after every 10%.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH STATS

Here is another option showing stats after every 1%.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH STATS = 1

Create a backup and give it a description

This command uses the description option to give the backup a name. This can later be used with some of the restore commands to see what is contained with the backup. The maximum size is 255 characters.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

WITH DESCRIPTION = 'Full backup for AdventureWorks'

Create a mirrored backup

This option allows you to create multiple copies of the backups, preferably to different locations.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

MIRROR TO DISK = 'D:\AdventureWorks_mirror.BAK'

WITH FORMAT

Specifying multiple options

This next example shows how you can use multiple options at the same time.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.BAK'

MIRROR TO DISK = 'D:\AdventureWorks_mirror.BAK'

WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$'

TO CHECK WHETHER DATABASE BACKUP IS SUCCESSFUL OR NOT:

---------------------------------------------------------------------------

Full Database Backups

SELECT database_name, backup_start_date, type, *

FROM msdb.dbo.backupset

WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()

AND Type = 'D'

ORDER BY backup_set_id DESC

Transaction Log Backups

SELECT database_name, backup_start_date, type, *

FROM msdb.dbo.backupset

WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()

AND Type = 'L'

ORDER BY backup_set_id DESC

Differential Backups

SELECT database_name, backup_start_date, type, *

FROM msdb.dbo.backupset

WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()

AND Type = 'I'

ORDER BY backup_set_id DESC

File\File Group Backups

SELECT database_name, backup_start_date, type, *

FROM msdb.dbo.backupset

WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()

AND Type = 'F'

ORDER BY backup_set_id DESC

Restore Backups:
----------------------------------------------------------------------------------------
Restore a full backup

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
GO

Restore a full backup with NORECOVERY
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO

Restore full backup using WITH REPLACE
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH REPLACE
GO

Restore a full database with stats showing for each 1 percent complete
RESTORE DATABASE 'AdventureWorks' FROM DISK = 'C:\AdventureWorks.BAK' WITH STATS = 1
GO

Restore full backup WITH MOVE
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH MOVE 'AdventureWorks_Data' TO 'G:\SQLData\AdvnetureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'H:\SQLData\AdvnetureWorks_Log.ldf'
GO

Restore database with STOPAT
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY,
STOPAT = 'Mar 23, 2009 05:31:00 PM'
GO

to get the contents of a SQL Server backup file
RESTORE HEADERONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO

Get labelonly information from a backup file
RESTORE LABELONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO

Restore a differential backup
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
GO

Restore using a backup file that has multiple backups
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY, FILE = 1
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 2
GO

Restore a transaction log backup
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO

Restore multiple transaction log files (NORECOVERY)
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
GO

Restore multiple transaction log backups from the same backup file
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 1
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 2
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH FILE = 3
GO

Check a backup file on disk
RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK
GO

Check a backup file on disk for a particular backup
RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK WITH FILE = 2
GO

Restore full backup, latest differential and two transaction log backups
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks2.TRN'
WITH RECOVERY
GO

T-SQL command for Striping a database backup
BACKUP DATABASE [AdventureWorks2012]
TO
DISK='C:\AdventureWorks2012_1.bak',
DISK='C:\AdventureWorks2012_2.bak',
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'AdventureWorks2012 -Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

T-SQL command to restore from Striped database backup
RESTORE DATABASE [AdventureWorks2012]
FROM
DISK='C:\AdventureWorks2012_1.bak',
DISK='C:\AdventureWorks2012_2.bak',
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10,replace
GO

T-SQL command for Striping transaction log backup
BACKUP LOG [AdventureWorks2012]
TO
DISK='C:\AdventureWorks2012_1.trn',
DISK='C:\AdventureWorks2012_2.trn',
DISK='C:\AdventureWorks2012_3.trn',
DISK='C:\AdventureWorks2012_4.trn'
WITH STATS = 10
GO

A Learning HUB for Database Administration

Tab

Backup, Restore & Recovery

No comments:

Post a Comment

Popular Posts