Thursday, July 21, 2011

Backup and Restore in SQL Server

Backing Up and Restoring Databases in SQL Server
A copy of data that can be used to restore and recover the data is called a backup.
Microsoft SQL Server enables you to back up and restore your databases. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures.
With good backups, you can recover from many failures, such as:
• Media failure.
• User errors, for example, dropping a table by mistake.
• Hardware failures, for example, a damaged disk drive or permanent loss of a server.
• Natural disasters.
Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up database mirroring, and archiving.
Types of Backups:
• Full backup: A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
• Differential backup: A differential backup is based on the latest full backup of the data. This is known as the base of the differential, or the differential base. A differential backup contains only the data that has changed since the differential base. Differential backups can speed up the process of making frequent backups to decrease the risk of data loss. Usually, a differential base is used by several successive differential backups. At restore time, the full backup is restored first, followed by the most recent differential backup.
• Partial backup: A backup of all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups. A partial backup of a read-only database contains only the primary filegroup.
• Differential Partial backup: A backup that contains only the data extents that were modified since the most recent partial backup of the same set of filegroups.
• File backup: A full backup of all the data in one or more files, or filegroups.
• Differential File backups: A backup of one or more files that contain data extents that were changed since the most recent full backup of each file.
Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup. After the first data backup, under the full recovery model or bulk-logged recovery model, regular transaction log backups (or log backups) are required. Each log backup covers the part of the transaction log that was active when the backup was created, and the log backup includes all log records that were not backed up in a previous log backup.
NOTE: Under the simple recovery model, file backups are basically restricted to read-only secondary filegroups. You can create a file backup of a read/write filegroup, but before you can restore the read/write file backup, you must set the filegroup to read-only and take a differential read-only file backup.
Restrictions:
• Offline Data Cannot Be Backed Up: Any backup operation that implicitly or explicitly references data that is offline fails. Typically, a log backup succeeds even if one or more data files are unavailable. However, if any file contains bulk-logged changes made under the bulk-logged recovery model, all the files must be online for the backup to succeed.
Syntax:
-- Backing Up a Whole Database
BACKUP DATABASE { database_name @database_name_var }
TO [ ,...n ]
[ ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL [ ,...n ] } ]
[;]

-- Backing Up Specific Files or Filegroups
BACKUP DATABASE { database_name @database_name_var }
[ ,...n ]
TO [ ,...n ]
[ ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL [ ,...n ] } ]
[;]

-- Creating a Partial Backup
BACKUP DATABASE { database_name @database_name_var }
READ_WRITE_FILEGROUPS [ , [ ,...n ] ]
TO [ ,...n ]
[ ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL [ ,...n ] } ]
[;]

-- Backing Up the Transaction Log (full and bulk-logged recovery models)
BACKUP LOG { database_name @database_name_var }
TO [ ,...n ]
[ ] [ next-mirror-to ]
[ WITH { } [ ,...n ] ]
[;]
EXAMPLES:
Backing up a complete database
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
WITH FORMAT;
GO
Backing up the database and log
-- To permit log backups, before the full database backup, modify the database
-- to use the full recovery model.
USE master;
GO
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO
-- Create AdvWorksData and AdvWorksLog logical backup devices.
USE master
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
'Z:\SQLServerBackups\AdvWorksData.bak';
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksLog',
'X:\SQLServerBackups\AdvWorksLog.bak';
GO

-- Back up the full AdventureWorks2008R2 database.
BACKUP DATABASE AdventureWorks2008R2 TO AdvWorksData;
GO
-- Back up the AdventureWorks2008R2 log.
BACKUP LOG AdventureWorks2008R2
TO AdvWorksLog;
GO
Creating a full file backup of the secondary filegroups
--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
FILEGROUP = 'SalesGroup1',
FILEGROUP = 'SalesGroup2'
TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck'
GO
Creating a differential file backup of the secondary filegroups
--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
FILEGROUP = 'SalesGroup1',
FILEGROUP = 'SalesGroup2'
TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck'
WITH
DIFFERENTIAL
GO
RESTORE:
Restores backups taken using the BACKUP command. This command enables you to perform the following restore scenarios:
• Restore an entire database from a full database backup (a complete restore).
• Restore part of a database (a partial restore).
• Restore specific files or filegroups to a database (a file restore).
• Restore specific pages to a database (a page restore).
• Restore a transaction log onto a database (a transaction log restores).
• Revert a database to the point in time captured by a database snapshot.
Syntax:
--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name @database_name_var }
[ FROM [ ,...n ] ]
[ WITH
{
[ RECOVERY NORECOVERY STANDBY =
{standby_file_name @standby_file_name_var }
]
, [ ,...n ]
,
,
,
,
} [ ,...n ]
]
[;]

--To perform the first step of the initial restore sequence
-- of a piecemeal restore:
RESTORE DATABASE { database_name @database_name_var }
[ ,...n ]
[ FROM [ ,...n ] ]
WITH
PARTIAL, NORECOVERY
[ , [ ,...n ]
,
] [ ,...n ]
[;]

--To Restore Specific Files or Filegroups:
RESTORE DATABASE { database_name @database_name_var }
[ ,...n ]
[ FROM [ ,...n ] ]
WITH
{
[ RECOVERY NORECOVERY ]
[ , [ ,...n ] ]
} [ ,...n ]
[;]

--To Restore Specific Pages:
RESTORE DATABASE { database_name @database_name_var }
PAGE = 'file:page [ ,...n ]'
[ , ] [ ,...n ]
[ FROM [ ,...n ] ]
WITH
NORECOVERY
[ , [ ,...n ] ]
[;]

--To Restore a Transaction Log:
RESTORE LOG { database_name @database_name_var }
[ [ ,...n ] ]
[ FROM [ ,...n ] ]
[ WITH
{
[ RECOVERY NORECOVERY STANDBY =
{standby_file_name @standby_file_name_var }
]
, [ ,...n ]
,
,
} [ ,...n ]
]
[;]

--To Revert a Database to a Database Snapshot:
RESTORE DATABASE { database_name @database_name_var }
FROM DATABASE_SNAPSHOT = database_snapshot_name
Restoring a full database
RESTORE DATABASE AdventureWorks2008R2 FROM AdventureWorks2008R2Backups

Restoring full and differential database backups

RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.bak'
WITH FILE = 6
NORECOVERY;
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.bak'
WITH FILE = 9
RECOVERY;

Restoring a database using RESTART syntax

-- This database RESTORE halted prematurely due to power failure.
RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
-- Here is the RESTORE RESTART operation.
RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups WITH RESTART

Restoring a database and move files

RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH NORECOVERY,
MOVE 'AdventureWorks2008R2_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.mdf',
MOVE 'AdventureWorks2008R2_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.ldf'
RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups WITH RECOVERY
Copying a database using BACKUP and RESTORE

BACKUP DATABASE AdventureWorks2008R2
TO AdventureWorks2008R2Backups ;

RESTORE FILELISTONLY
FROM AdventureWorks2008R2Backups ;

RESTORE DATABASE TestDB
FROM AdventureWorks2008R2Backups
WITH MOVE 'AdventureWorks2008R2_Data' TO 'C:\MySQLServer\testdb.mdf',
MOVE 'AdventureWorks2008R2_Log' TO 'C:\MySQLServer\testdb.ldf';
GO

Restoring to a point-in-time using STOPAT

RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE=3, NORECOVERY;

RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
RESTORE DATABASE AdventureWorks2008R2 WITH RECOVERY;

Restoring the transaction log to a mark

USE AdventureWorks2008R2;
GO
BEGIN TRANSACTION ListPriceUpdate
WITH MARK 'UPDATE Product list prices';
GO

UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

COMMIT TRANSACTION ListPriceUpdate;
GO

-- Time passes. Regular database and log backups are taken.
-- An error occurs in the database.
USE master
GO

RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE = 3, NORECOVERY;
GO

RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE = 4,
RECOVERY,
STOPATMARK = 'ListPriceUpdate';

Restoring using FILE and FILEGROUP syntax

RESTORE DATABASE MyDatabase
FILE = 'MyDatabase_data_1',
FILE = 'MyDatabase_data_2',
FILEGROUP = 'new_customers'
FROM MyDatabaseBackups
WITH
FILE = 9,
NORECOVERY;
GO
-- Restore the log backups.
RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 10,
NORECOVERY;
GO
RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 11,
NORECOVERY;
GO
RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 12,
NORECOVERY;
GO
--Recover the database:
RESTORE DATABASE MyDatabase WITH RECOVERY;
GO

Reverting from a database snapshot

USE master
RESTORE DATABASE AdventureWorks2008R2 FROM DATABASE_SNAPSHOT = 'AdventureWorks2008R2_dbss1800';
GO


Backup: http://technet.microsoft.com/en-us/library/ms186865.aspx
Restore: http://technet.microsoft.com/en-us/library/ms186858.aspx