Archive for the ‘Backup & Restore’ Category

        Reasons to Backup Master Database – Why Should Master Database Backedup (Pinal Dave) :- http://blog.sqlauthority.com/2009/02/15/sql-server-reasons-to-backup-master-database-why-should-master-database-backedup/

1.Backup the Master Database.

2.For Testing purpose intentionally delete any login from SQL Server.

4. Login was Deleted from SQL Server.

Now we will restore the Master Database From Backup file

1.Open SQL Server Configuration Manager

2.Stop the SQL Server Service.

3. Add -m; Parameter in SQL Server Startup Parameters for start the SQL Server with Single User Mode.

4. Start the SQL Server Service.

5.Open command prompt for restore a master database.

6.Now Open SQL Server Using SSMS Check the Deleted Login Id restored.

Read Full Post »


Backup type

Database backup A full backup of the whole database. Database backups represent the whole database at the time the backup finished.
Differential database backups A backup of all files in the database. This backup contains only the data extents that were modified since the most recent database backup of each file.

Applies to: SQL Server 2000, SQL Server 2005

Understanding SQL Server backup typesDatabase backups are at the core of any SQL Server disaster recovery planning for any production system. Backups may be used to provide a means of recovery to a point-in-time when the database was last operational. Microsoft® SQL Server™ provides several types of backups that may be combined to formulate a customized disaster recovery plan depending on the nature of the data and the recovery requirements. It is highly recommended that all SQL Server databases be backed up periodically.

SQL Server backup media

A database may be backed up to disk or to tape. The examples in this article assume a disk backup directly into a disk file (as opposed to a disk backup device). Any database can be backed up to a random disk file at any time. The file may either be initialized (using WITH INIT) or appended with the new backup.

Types of backups in SQL Server

SQL Server provides several different kinds of backups including CompleteDifferentialTransaction Log, andFile(s) and Filegroup(s) backup. A combination of these backups may be used to formulate a robust disaster recovery strategy. The following paragraphs explain each SQL Server backup type

Complete database backup

A complete database backup creates a stand-alone image of the entire database. A complete database backup is self-dependent and may be restored to either the same or a new database on the same or a different server. This provides plenty of flexibility at the time when this backup has to be restored. A complete backup may be restored without the need for any other kind of backup. It may also be performed for databases in any recovery model. Restoring a complete database backup typically would be considered a starting point for a disaster recovery situation where the entire database is lost or damaged. It is recommended that a complete database backup be performed at regular intervals for all production databases. It is also recommended that a complete backup should be performed for system databases if there are any changes performed to the SQL Server operating environment such as creating or removing databases, configuring security, creating and modifying DTS/SSIS packages or scheduled jobs, adding and removing linked servers, etc.

Backup syntax

TO DISK = ‘c:\backups\northwind.bak’

Restore syntax (Same database)

FROM DISK = ‘c:\backups\northwind.bak’

Restore syntax (New database and/or server)

FROM DISK = ‘c:\backups\northwind.bak’
WITH MOVE ‘northwind’ TO ‘c:\new_location\Northwind_new.mdf’
MOVE ‘northwind_log’ TO ‘c:\new_location\Northwind_new_log.ldf’

Differential database backup

A differential backup backs up only modified extents since the last complete backup. An extent is a group of 8 data pages each consisting of 8 KB (64 KB in total). By definition, differential backups are cumulative. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup. Differential backups may be considered as an alternative for databases that are large and are modified infrequently. These would include data warehouse type of databases. Differential backups have several limitations including the following:

  • They do not provide point-in-time restore capabilities
  • They may only be restored after a complete database backup is restored
  • They may not be performed on the master database

Backup syntax

TO DISK = ‘c:\backups\northwind_diff.bak’

Restore syntax (Same database – Note that a complete database backup is restored first using WITH NORECOVERY)

FROM DISK = ‘c:\backups\northwind.bkp’

FROM DISK = ‘c:\northwind_diff.bkp’

The differential bitmap is not updated by a copy-only backup. Therefore a copy-only backup cannot serve as a differential base or differential backup. A copy-only backup does not affect subsequent differential backups.

Transaction log backup

An SQL Server database consists of two components: data file(s) and transaction log file(s). A transaction log captures the modifications made to the database. A simple transaction may place several records in the transaction log. Each of these records is known as a log record and is assigned a unique identification number known as the log sequence number (LSN). Log records that belong to the same transaction are linked together through the LSN. If SQL Server service shuts down unexpectedly, upon restart the recovery process examines the entries in the transaction log and if there are transactions that have not been rolled forward completely, the recovery process rolls back the changes performed as part of these incomplete transactions. This operation is extremely important as it forms the basis of transactional recovery. Entries in the transaction log are also used if transactional replication is configured for the specific database.

A transaction log backup backs up all transactions since either the previous transaction log backup, or the complete database backup if there have been no transaction log backups performed for the database in the past. This backup may then be used to apply the backed-up changes, in case disaster recovery is required. Transaction log backups may only be applied to a database in an unrecovered state. A database may be in an unrecovered state if it is being restored from a set of backups as part of a disaster recovery procedure, or if it is configured as a standby database on a warm backup server. A transaction log backup also truncates the inactive portion of the transaction log, unless the database is configured as a Publisher in transactional replication and there are transactions pending propagation to Subscribers.

Each transaction log backup contains a First and Last log sequence number (LSN). Consecutive transaction log backups should have sequential LSNs for the boundary log records. These LSN values may be examined using the RESTORE HEADERONLY command. If LastLSN from the previously restored transaction log backup does not match the FirstLSN from the backup that is currently being restored, the restore operation fails with the following error: “This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log”. If the above message is generated while restoring a particular transaction log backup, which is part of a set of transaction log backups that are to be restored, any attempts to restore further transaction log backups will fail with this message. There could be several reasons for consecutive transaction log backups being out of sequence. Some of the most common reasons noted from support experience have been:

  • The database recovery model has been changed to Simple and back to either Full or Bulk-Logged. Switching the recovery mode to Simple causes the transaction log to be truncated
  • Another transaction log backup was performed between the previous successfully restored backup and the one generating this message
  • The transaction log was manually truncated between the two backups
  • The database was in Bulk-Logged recovery model and non-logged operations were performed
  • Transaction log backups are not allowed for databases in Simple Recovery model. While in Simple Recovery model, a database’s transaction log is truncated every time a CHECKPOINT is invoked for the database

Transaction log backups provide the possibility of performing a point-in-time restore or point-of-failure restore. You can also perform a restore to a named transaction with transaction log backups.

Backup syntax

BACKUP LOG Northwind
TO DISK = ‘c:\backups\northwind_log_1.bak’

Restore syntax (Same database – Note that a complete database backup is restored first using WITH NORECOVERY, then a sequence of transaction log backups)

FROM DISK = ‘c:\backups\northwind.bkp’

FROM DISK = ‘c:\northwind_log_1.bkp’

File(s) and Filegroup(s) backup

Use BACKUP to back up database files and filegroups instead of the full database when time constraints make a full database backup impractical. To back up a file instead of the full database, put procedures in place to ensure that all files in the database are backed up regularly. Also, separate transaction log backups must be performed. After restoring a file backup, apply the transaction log to roll the file contents forward to make it consistent with the rest of the database.

For More Details :- http://msdn.microsoft.com/en-us/library/ms175477.aspx




Read Full Post »

sometime we save the SQL Server database backups with user defined ext(like .backup,.back).that we will need to want backup file information Using Restore Headeronly We Get Backup File Header Information.

URLs :– http://msdn.microsoft.com/en-us/library/ms178536(v=SQL.90).aspx


Example Script :-

Use Master

Create Database Backup_Type_Test
Backup Database Backup_Type_Test to Disk=’C:\Backup1.backup’
Backup Database Backup_Type_Test to Disk=’C:\Backup2.backup’ with differential
Backup Log Backup_Type_Test to Disk=’C:\Backup3.backup’
Backup Database Backup_Type_Test to Disk=’C:\Backup1.backup’
Restore Headeronly From Disk =’C:\Backup1.backup’

Read Full Post »

Sample Database backup Scenario.

Every Day 12 AM Full Backup of Database.
Every 6 Hours Differential Backup.
Every 15 Min Transactional Log Backup.

The Sample Database was Corrupted on 3.25 AM. The Transaction Log file also Corrupted.

Question :-

Can we restore the Sample Database on Specific Point in time (3.25) AM?

Answer :-


Explanation :-

Because we have a Transactional log Backup till 3.15 AM . Transaction log File also Corrupted,so we can’t take a tail log backup for the database.so we recover the sample database till 3.15 AM only.

Note :-

Restore should be your last resort.You can try to repair the database using DBCC CheckDB REPAIR_REBUILD or if possible use some 3rd party tool to repair the Tlog file.

Link Forum :-


Read Full Post »