One of the Primary tasks of any Database Administrator is Backup and then to eventually restore those backups somewhere. Database Administrators create fallback plans for every possible failure. Microsoft introduced the detach and attach commands in SQL Server 7.0. Those commands also became a part of a DBAs bread and butter when it comes to moving databases or cleaning up the log tables.
Database backups are required when
- Database gets corrupted and not recoverable
- Database Deleted by accident or by purpose
- SQL Server Crashes and data drive is not recoverable
- When Operating system crashes and data drive not recoverable
- When there is a hardware failure like disk/SAN failures
- When doing Log shipping
- Copying a database
- Information needed from archive
- Simulate production server
This article series is going to illustrate the various types of backup, take backups, restoring different types of backups, detaching databases, attaching databases, move database, Backup a table, compress a backup, encrypting the backups etc.
Microsoft SQL Server provides three different types of backups. They are Full Backup, Differential Backup and Transactional log backups. There is also another backup called File group backups. We will look in to all of them in detail with different scenarios.
A full backup or complete backup is basically a backup of both data the file and log file. When taking a full backup SQL Server backs up enough of transaction log to produce consistent database when restored. At least one full backup is needed in order take a differential or transactional log backup. In order to restore a backup definitely we need a full backup and optionally differential and or transactional log backups.
Microsoft SQL Server provides three different recovery model for any databases that we create. They are Full recovery mode, Simple mode and Bulk Logged mode. We can take full backup of any database irrespective of database recovery model.
Now let us create a database by using the following Data definition command as shown below.
USE [master]
GO
/****** Object: Database [MyDatabase] Script Date: 06/14/2008 14:34:31 ******/
IF EXISTS (SELECTname FROM sys.databases WHERE name= N'MyDatabase')
DROP DATABASE [MyDatabase]
GO
USE [master]
GO
/****** Object: Database [MyDatabase] Script Date: 06/14/2008 14:34:47 ******/
CREATE DATABASE [MyDatabase] ON PRIMARY
( NAME = N'MyDatabase',
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.mdf' ,
SIZE = 1216KB , MAXSIZE= UNLIMITED,FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyDatabase_log',
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase_log.LDF' ,
SIZE = 504KB , MAXSIZE= 2048GB , FILEGROWTH= 10%)
GO
Now let us create a table “Employee” in the database MyDatabase and add some data as shown below.
USE [MyDatabase]
GO
/****** Object: Table [dbo].[Employee] Script Date: 06/14/2008 15:14:38 ******/
IF EXISTS (SELECT* FROM sys.objects WHERE
object_id = OBJECT_ID(N'[dbo].[Employee]') ANDtype in (N'U'))
DROP TABLE [dbo].[Employee]
GO
USE [MyDatabase]
GO
/****** Object: Table [dbo].[Employee] Script Date: 06/14/2008 15:14:49 ******/
CREATE TABLE [dbo].[Employee](
[id] [int] NULL,
[Firstname] [varchar](100) NULL,
[LastName] [varchar](100) NULL
) ON [PRIMARY]
GO
Insert into Employee values (1001,'Adam','Smith')
Insert into Employee values (1002,'Barbara','Spears')
Insert into Employee values (1003,'Robert','White')
Insert into Employee values (1004,'Jeff','Carter')
Insert into Employee values (1005,'Suki','Chiu')
GO
As a database administrator, we should take at least a full backup of all the databases so that in future any of the above crash scenario happens, we could recover it. So, let us take a full backup of this database as shown below.
Backup database MyDatabase to disk='d:\Mydatabase_06_14_2008.bak'
Result:
Processed 160 pages for database 'MyDatabase', file 'MyDatabase' on file 1.
Processed 5 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
BACKUP DATABASE successfully processed 165 pages in 0.776 seconds (1.739 MB/sec).
Now let us assume that the database was deleted by mistake. Let us simulate the deletion of the database as shown below.
USE [master]
GO
/****** Object: Database [MyDatabase] Script Date: 06/14/2008 14:34:31 ******/
IF EXISTS (SELECTname FROM sys.databases WHERE name= N'MyDatabase')
DROP DATABASE [MyDatabase]
Now as a Database Administrator we are supposed to recover the database. Since the database is really gone, the only way to recover the database is by restoring the database backup. Let us assume that backup we took was not deleted from the server and the backup was not from any other server. We could restore the database using the following restore command as shown below.
Restore database MyDatabase from disk='d:\Mydatabase_06_14_2008.bak'
Result:
Processed 160 pages for database 'MyDatabase', file 'MyDatabase' on file 1.
Processed 5 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
RESTORE DATABASE successfully processed 165 pages in 0.457 seconds (2.953 MB/sec).
Now, let us query the table and see whether we have all the data. Execute the following transact SQL statement as shown below.
Use MyDatabase
Go
Select * from Employee
Go
Result:
Fig 1.0
Conclusion
This is Part I of “Bread and Butter of DBA” article series. This part of the series explained the necessity of the backups and demonstrated how to take full backup and restore that backup when the database was deleted.
