Full Database Backup (Transact-SQL) on SQL Server 2008 R2

Full Database Backup (Transact-SQL)

---

A. Backing up to a disk device

The following example backs up the complete AdventureWorks2008R2 database to disk, by using FORMAT to create a new media set.

USE AdventureWorks2008R2;
GO
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2008R2';
GO

B. Backing up to a tape device

The following example backs up the complete AdventureWorks2008R2 database to tape, appending the backup to the previous backups.

USE AdventureWorks2008R2;
GO
BACKUP DATABASE AdventureWorks2008R2
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of AdventureWorks2008R2';
GO

C. Backing up to a logical tape device

The following example creates a logical backup device for a tape drive. The example then backs up the complete AdventureWorks2008R2 database to that device.

-- Create a logical backup device, 
-- AdventureWorks2008R2_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'AdventureWorks2008R2_Bak_Tape', '\\.\tape0'; 
USE AdventureWorks2008R2;
GO
BACKUP DATABASE AdventureWorks2008R2
   TO AdventureWorks2008R2_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'AdventureWorks2008R2_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0', 
      NAME = 'Full Backup of AdventureWorks2008R2';
GO

Microsoft SQL Server

Microsoft SQL server:

Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft which is designed to run on platforms ranging from laptops to large multiprocessor servers.

As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet).

SQL Server is commonly used as the backend system for websites and corporate CRM(Customer Relationship Managements) s and can support thousands of concurrent users.

Its primary query languages are T-SQL and ANSI SQL.SQL Server comes with a number of tools to helps with database administration and programming tasks.

T-SQL:

T-SQL (Transact-SQL) is the primary means of programming and managing SQL Server. It exposes keywords for the operations that can be performed on SQL Server, including creating and altering database schemas, entering and editing data in the database as well as monitoring and managing the server itself.

ANSI SQL:

SQL( Structured Query Language ) is a special-purpose programming language designed for managing data held in a relational database management systems (RDBMS).
SQL consists of a data definition language and a data manipulation language. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control, it also includes procedural elements.

DBA ( Database Administrator )

DBA ( Database Administrator ):
      A Database Administrator ( DBA) is a person responsible for the installation, configuration, upgrade, administration, monitoring and maintenance of databases in an organization.

DBA Responsibilities:
      A database administrator's responsibilities can include the following tasks:

• Installation, configuration and upgrading of Database server software and related products.
• Evaluate Database features and Database related products.
• Planning for backup, Restore and recovery of database information.
• Take care of the Database design and implementation.
• Implement and maintain database security (create and maintain users and roles, assign privileges).
• Controlling and monitoring user access to the database.
• Database tuning and performance monitoring.
• Application tuning and performance monitoring.
• Setup and maintain documentation and standards.
• Plan growth and changes (capacity planning).
• Do general technical troubleshooting and give cons.
• Database recovery.
• Ensuring compliance with database vendor license agreement.
• Generating various reports by querying from database as per need.
• Work as part of a team and provide 24x7 support when required.

 DBA Types:

There are three types of DBAs:

1. Systems DBAs (also referred to as Physical DBAs, Operations DBAs or Production Support DBAs): focus on the physical aspects of database administration such as DBMS installation, configuration, patching, upgrades, backups, restores, refreshes, performance optimization, maintenance and disaster recovery.
2. Development DBAs: focus on the logical and development aspects of database administration such as data model design and maintenance, DDL (data definition language) generation, SQL writing and tuning, coding stored procedures, collaborating with developers to help choose the most appropriate DBMS feature/functionality and other pre-production activities.
3. Application DBAs: usually found in organizations that have purchased 3rd party application software such as ERP (enterprise resource planning) and CRM (customer relationship management) systems. Examples of such application software includes Oracle Applications, Siebel and PeopleSoft (both now part of Oracle Corp.) and SAP. Application DBAs straddle the fence between the DBMS and the application software and are responsible for ensuring that the application is fully optimized for the database and vice versa. They usually manage all the application components that interact with the database and carry out activities such as application installation and patching, application upgrades, database cloning, building and running data cleanup routines, data load process management, etc.