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.