Why
we need backup?
1) All hard drives will
eventually go bad:
Since hard drives are mechanical devices, they will all eventually fail. Common
causes are Power surges, external trauma or head crash, where the internal read-and-write
head of the device, usually just hovering above the surface, touches a platter,
or scratches the magnetic data-storage surface. The head rotates few nanometers
from the platter's surface which makes a collision an acknowledged risk.
Airlifter
is a part of hard drive that works towards equalizing the atmospheric pressure
and moisture between the hard-drive enclosures. If the filter fails to capture
a dust particle, the particle can land on the platter, causing a head crash.
If we
have recent backup of database we will be able to restore the databases it can be
used for creating
2) Test purpose database for developer: Usually when developers are
designing new procedures and queries, they will be able to test those functions
on a backup copy of the database which resembles actual live database
When the testing proves to be correct, they
can be used on the original database else if we directly run our stored
procedures & functions on live database and if the procedures or queries
are found to be faulty, the original database will be compromised.
3) Copying database from one
server to other:
When we want to set up a copy on database from one server to other we can use
recently backed database from which we can create a new database on other
server.
How can we backup database in mssql server?
There
are different types of backups that one can create in mssql server
a) Full backups: These backups create a complete
backup of your database as well as part of the transaction log, so the database
can be recovered. This allows for the simplest form of database restoration,
since all of the contents are contained in one backup.
BACKUP DATABASE OurDatabaseName TO DISK = 'C:\ OurDatabaseName.BAK'
This command will create a file
named ‘OurDatabaseName.BAK’ at C drive containing complete backup of our
database named ‘OurDatabaseName’
b) Differential backups: A differential backup is based
on the most recent, previous full data backup. A differential backup captures
only the data that has changed since that full backup. The full backup upon which
a differential backup is based is known as the base of the differential.
BACKUP
DATABASE OurDatabaseName
TO
DISK = 'C: \ OurDatabaseName.DIF'
WITH DIFFERENTIAL
c) File backups: Every SQL Server database
has atleast two operating system files: a data file and a log file. Data file
contain data and objects such as tables, indexes, stored procedures, and view
while Log file contain the information that is required to recover all
transactions in the database.
Sql server
can have three types of files:
The primary data file contains the startup
information for the database and points to the other files in the database. User data and objects can be stored in this
file or in other type of file called secondary data files. Every database has
one primary data file. Usually have extension as .mdf.
Secondary data files are optional, are user-defined,
and store user data. Secondary files can be used to spread data across multiple
disks by putting each file on a different disk drive. Additionally, if a
database exceeds the maximum size for a single Windows file, you can use
secondary data files so the database can continue to grow. These files usually have
an extension .ndf.
The transaction log files hold the log
information that is used to recover the database. There must be at least one
log file for each database. These files usually have an extension .ldf.
It is
possible to create multiple transaction log files for single database It is recommended
by Microsoft to put data and log files on separate disks.
File
Backup is only relevant when we have multiple data files for given database.
This option allows you to backup each file independently instead of having to back
up the entire database. If we have a very large database and have large data
files this option make sense
Suppose we database named ‘MultiDataFileDb’ with two data files say
MultiDataFileDb.mdf & MultiDataFileDb1.ndf with names PrimMultiDataFileDb & SecMultiDataFileDb1 respectively then to
create backup we can run following TSQL command
BACKUP
DATABASE MultiDataFileDb FILE = 'PrimMultiDataFileDb' TO DISK = 'C: \ MultiDataFileDb_
PrimMultiDataFileDb.FIL'
BACKUP
DATABASE MultiDataFileDb FILE = 'SecMultiDataFileDb1' TO DISK = 'C:\ MultiDataFileDb_
SecMultiDataFileDb1.FIL'
Other types of backup are Filegroup backups,
Partial backups, Copy-Only backups, Mirror backups & Transaction log backups.
We will look into these types of backup in other next article.
Note: Author does not claim originality of content, information presented
in article is from various sources from internet subjected to author’s comprehension.
Author sincerely thanks all the distinguished persons for making available useful
information in public domain in simpler form.
References:
No comments:
Post a Comment