Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

SQL Server 2005 – Backing up a Database

By Exforsys | on January 1, 2006 |
SQL Server 2005

SQL Server 2005 – Backing up a Database

In this tutorial you will learn about Backing up a Database in SQL Server 2005 – Backup and Restore operations, Restoring System Databases, Media Reliability, Using Checksums during backup and Restore and Backup Media mirroring.

Backup and Restore operations.

A backup is a copy of the data that is maintained for the purpose of restoration at a later date if a requirement arises due system failure. Failures can occur due to media problems, user errors, hardware failures or natural disasters. Backup of data are also useful for copying data from one server to another, for setting up database mirroring, archiving and disaster recovery.

As is evident from the preceding paragraph a backup strategy contains a restore strategy as a natural sequel. The backup strategy defines the type and frequency of backups while a recovery strategy defines roles and responsibilities of the personnel who have to do the restoration within the time for recovery defined. It is advisable to document the backup and restore procedures in a run book.

It is also evident from the preceding paragraphs that backup and restore operations require a lot of planning. Planning involves taking into consideration the production goals of the organization, the nature of the databases, the constraints on the resources and so on. The DBA must be fully aware of the implications of data loss to the business and must plan to restore the database within the shortest possible time. This in turn involves the selection of a correct recovery model. The staffing requirements for the recovery procedure must also be considered and put in place to meet the stringent requirements of recovery.

A recovery model is a database property that controls the basic behavior of the backup and restores operations for a database. Recovery models determine how transactions are logged, which logs require backups and the kinds of restore operations available to the DBA. A new database inherits its recovery model from the model database. Recovery models simplify recovery planning, backup and recovery procedures, clarify tradeoff among system operational requirements and clarify tradeoffs among availability and recovery requirements.

SQL Server 2005 introduces changes to the BACKUP statement. It no longer supports the BACKUP LOG WITH NO_LOG and BACKUP LOG WITH TRUNCATE_ONLY options.

There are three type of recovery models in SQL Server. The simple recovery model logs most transactions minimally. It logs as much information as required for ensuring database consistency after a system crash. When old transactions are committed the log is truncated. This poses the potential danger of data loss in the event of disaster. This type of recovery model is considered inappropriate for production environments.

The full recovery model logs all transactions and retains all the log records until backup. The SQL Server 2005 Enterprise edition allows a database to be recovered up to the point of failure. Full database backups include log steps to reach a consistent state if transactions are in process when the backup is made.

Bulk logged recovery model performs a minimal log of bulk operations such as creation of indexes and bulk loads. This improves performance of bulk operations and is used as supplement to the full recovery model.

Mirrored backups helps DBAs create two or four identical copies of backup files in case one of the sets is damaged. Online Restores provides the ability to do an online restore to an offline filegroup while keeping the database online. Copy only backups provide the DBA with the ability to make a copy of the backup file in the middle of the backup sequence without disrupting the sequencing of other backup files. No changes have been made to the way differential backups are made. Transaction log backups have now the ability to make copy only backups. Partial backups are similar to differential backups except that it backs up all file groups other than those marked read only. Finally SQL Server 2005 also provides the DBA the ability to restore from earlier versions of the database.

Restore is a process that restores data from one or more backups and recovers the database when the last backup is restored. SQL Server Management Studio can also be used to restore databases and logs. The RESTORE statement contains a new option RESTRICTED_USER. This restricts access to the recovered database to members of the db_owner, dbcreator or sysadmin roles. It replaces the DBO_ONLY option of SQL Server 2000.

Point in Time recovery functionality has been added to SQL Server 2005 by allowing the STOPAT option to be used to restore the log to a specific point in time. Partial restore operations have also been enabled in this version. Errors in isolated parts of the database can be corrected using this option. A partial restore of the primary file group containing a specific part of the database can be executed to restore the contents to an original state in a point in time. Page level restore operations are useful in restoring individual pages when using the full or bulk logged recovery models. This greatly reduces the time duration that the database is offline. Piecemeal restore operations have also been introduced to allow the DBA restore file groups in stages, bringing the groups online as the restoration operation is completed. For full and bulk logged recovery models piecemeal restore is valid only for secondary file groups. In simple recovery model secondary file groups can only be restored if they were read only when the backup was executed and have remained so since. This restore is attempted after partial restore of the primary filegroup.

A new concept of Online restore has been introduced in SQL Server 2005. The database remains online while restoring individual files or pages to a database. This enhances uptime of tables in full and bulk logged recovery models. These can be of two types: Page online restore and file online restore.

Multiple RESTORE statements can be used to restore sequences that copy data, roll it forward and bring it online at the end of the roll back phase. Valid restore sequences can be derived from the catalog views, the msdb database and backup that contains the required information. Catalog views also provide information regarding the state of the database.

Restoring System Databases

The restoration of system databases require that the instance of the SQL Server must be running. To run the SQL server instance the master must be accessible. To restore the master from a current database backup or restore it from a full backup. If the master is damaged beyond restoration, it must be rebuilt and then the backup must be used to restore it.

The model, msdb or distribution database must be restored from a full backup.

Backup and Restore operations in SQL Server 2005 can be performed in two ways

1) Using multiple backup devices in parallel with enough throughput.

2) Using a combination of full, full differential and transaction log backups to minimize recovery time. The full differential backups are faster to create and reduce the amount of transaction log required to recover the database.

Differential backup operations involve copying data from the database files to the backup devices and copying the portion of the transaction log needed to roll forward the database to a consistent state with the backup devices. The process is similar to creating a data backup of the changed data. The database file is copied to the backup device and the files are sorted by disk device and a reader thread is assigned to each device. The reader threads reads the data from the database files and a writer thread is assigned to each backup device. Thereafter the writer thread writes to the backup device. Parallel read operations can be increased by spreading the database over more backup devices and parallel write operations can be increased using more backup devices. However, if optimal throughput is not available bottlenecks can occur. A similar procedure is followed for Transaction log backups.

Media Reliability

Backed up data could still be at risk due to errors occurring on the backup media. SQL Server 2005 provides functionalities to alert the DBA about such errors during the process of backup or restore process.

Using Checksums during backup and Restore

The first of these is the CHECKSUM option of the BACKUP statement. This calculates a checksum on each page of the data and verifies the checksum before writing the information to the backup media. This helps ensure that valid data is written on to the media. If an error is detected during checksum creation process, the backup will fail. However, this can be overridden using the CONTINUE_AFTER_ERROR option. The CHECKSUM option of the RESTORE statement can be used to verify the data before restoring it to the server.

The CONTINUE_AFTER_ERROR option is to be used with discretion. The nature of the error will determine its usage. If the checksum verification fails it is possible that the remainder of the data to be restored in valid and the process can continue. However, if an error occurs in a tape drive, it might prove impossible to continue with the restore operation. If errors do occur the database may be marked as SUSPECT at the end of the restore process and the media may be checked manually for errors that impact the restore operation.

Backup Media mirroring.

The potential of recoverability is increased through the use of backup mirroring. Using this method data can be backed up in two locations of the same media type. The precondition is that when this kind of backup is attempted, all the media must be accessible and in restore only one need to be accessible.

In this tutorial we have studied backup and restore operations including partial backups, online backups and how to reduce the probability of media failure. In the lesson that follows we shall look in greater depth into data availability and database mirroring.

« « Effective Software Testing
SQL Server 2005 – Introduction to Data Availability » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • SQL Server Monitoring Tools – Server Profiler

    December 12, 2005 - 0 Comment
  • Using Notification Services in SQL Server 2005

    October 25, 2005 - 0 Comment
  • Maintain indexes in a SQL Server 2005 database

    December 20, 2005 - 0 Comment
  • SQL Server 2005 Architecture Overview

    November 23, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Using DDL Triggers

    December 12, 2005 - 0 Comment
  • Using XML in SQL Server 2005

    October 26, 2005 - 0 Comment
  • SQL Server 2005 – Tuning a Database

    December 24, 2005 - 0 Comment
  • SQL server 2005 Editions

    November 23, 2005 - 0 Comment
  • SQL Server 2005 – Using Event Notifications

    December 13, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • Managing SQL Server 2005 Security

    December 24, 2005 - 0 Comment
  • SQL Server 2005 – Using the Database Tuning Advisor

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Using Database Snapshots

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT