Backup and restore concepts
What is Backup?
Backup
is the maintaining a duplicate copy of a data that is used to recover, when
there is loss of data in the database.
Data
loss will occur due to either hardware failure, user error (by mistake data was
delete by user) or natural disaster.
Types of Backups?
Full backup, differential backup,
transaction log backup and file group backup.
Full back up: Full
backup is to backup the entire data present in the database.
While
doing a backup of the entire data using full backup, whatever the transactions
are going at that time will be recorded in the log and we are able to restore
the entire data from this backup file.
Here the entire backup contains database, logins, permissions etc.
Syntax: Backup Database
<database name> to Disk =’<directory>\<file name>’ with INIT
Example: Backup database
Raghu to disk=’c: \test\raghu.bak’ with init
(Note:
init overwrites the existing data)
Differential backup: A
differential backup captures all the extents that have changed since the last
full backup. Main purpose of this backup is to minimize the transaction log
backup. This data contains only the changes that have made after every full
backup and it is faster than full back up.
We need full backup to take differential backup.
Syntax: backup
database<database name>to disk=’<directory>\<file name>’ with
differential
Example: backup database
Raghu to disk=’c: \test\raghu.bak’ with differential
Transactional log backup: This
backup can be done only for the databases which are configured with either full
or bulk-logged recovery model. Transaction log backup takes only the backup of
committed transactions that has made after every full backup.
After
taking the full backup only we are able to take log backup.
Syntax: backup
log<database name> to disk=’<directory>\<file name>’ with init
Example: backup log Raghu
to disk=’c: \test\raghu.trn’ with init
Scenario:
how backup will works …….
Let us take a full backup @ 6am every
day, differential backup for every 4hours and transaction log backup for every
10 minutes .suppose now the time is 2pm than how many backups will be done?
Full
backup
differential backup transactional log
backups
6am
10am (diff 1)
every 10 mins from 6am
2pm (diff
2)
total log backups 48
Full
backup was taken at 6am after that transaction log backup is going on for every
10 minutes and differential log backup has taken at 10am( contains all changes
made after full backup that means transactions logs between 6am to 10am will be
backed up and deletes log history in that period of time). Again 10am onwards
Transaction log backup is going on for every 10 minutes and 2pm differential backup
will be done (contains data between 6am to 2pm).
During
restoring a database it will restore last full backup (6am), differential
backup @2pm and transactional log backup after 2pm.
Question:
what is the purpose of differential backup? What is the difference between
differential and transactional log backup?
File group backup: with
this we can take backup of required files without taking the entire database
backup. File group backup backups some
portions of the database. File group backup will not be done without full
backup. This consumes less disk space and faster. We can take file group backup
with differential also.
Syntax: backup
database<database name> file group =<’file name’> to
disk=’<directory\name> with init
Example: backup database
Raghu file group=file1 to disk=’c: \test\file1.bak’ with init
Backup database Raghu file
group=file1 to disk=’c: \test\file1.bak’ with differential
Tail of log backup:
Tail of log will capture all log records that have not yet been backed up.it
will capture the tail of log even if the database is offline, damaged or
missing data files.
Syntax:
backup log adventureworks2012 to
disk ='C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.tailbak' with no_truncate
Mirrored Backup: Database
Mirrored backup is that which copies of the backup file are simultaneously
written into different locations.
Mirrored
backups are used to create additional copies to protect your organization from
media failure. When you use mirrored backup. Sql server reads the page from the
data files once and then creates multiple copies as it writes the page to disk
or tape.
This
process has the effect of writing the same page of data to each mirror at the
same time.
Syntax:
Backup database databaseName to
disk=c:\backup----
Mirror to disk=c:\backup---- with
format init
backup database pubs to disk='c:\demo\backup1.bak'
disk='c:\demo\backup2.bak'
mirror to disk='d:\demo\backupmirror1.bak'
disk='d:demo\backupmirror2.bak'
with format go
Note:
with format clause is
used to format the existing data in the disk and if it is not done the mirror
will fail. So, the FORMAT clause is required for a mirrored backup.
Copy-only Backup:
Taking the backup of the database without disturbing
the LSN.
It was introduced in sql server 2005. Normally taking
the backup changes the database and affects how later the backups are restored.
So, this is used to take a backup for a special purpose without effecting the
overall backup and restore procedures for a database. Use with copy-only to
perform this backup.
Stripped Backup:
We will use stripped backup when the
data file is too large and consumes more energy to do backup. So, stripped
backup splits the files and takes the backup in parallel processing to increase
the performance and decrease the time.
Syntax:
Backup database
adw
To disk=’c:\backup\adw1.bak’,
disk=’c:\backup\adw2.bak’
Partial
Backup:
It was introduced in sql server
2005 to take the backup of only Read-write filegroup by eliminating the
Read-only file groups in order to save the disk space and time.
backup database pubs read_write_filegroups to
disk='c:\demo\backups\pubs.bak'.
Restoring a backup data:
restoring a database is moving a data from disk to destination. We can restore
a database with recovery, with no recovery and standby option. Restoring process will be done based on the
type of backup they have taken. Restoring consumes 30% more time than backup data.
Restoring a full backup: restoring
a full backup overwrites the existing database if database with that name
already exists and if not restore operation will automatically create the files
and file groups for the database before restoring pages.
Syntax: restore database <database
name> from disk=’<directory\name>’ with replace
Example1: Restore database
pubs from disk=’c: \test\pubsfull.bak’ with replace, standby=’c: \test\pubsstandby.stn’
In
the above example the database is restoring with replace option in standby
mode. Replace option overwrites the existing data and standby option allows the
users only to select the statements without modifying the database.
Example2: Restore database
pubs from disk=’c: \test\pubsfull.bak’ with recovery, replace
When
a restore operation uses the withrecovery option, the database brought online,
the LSN is rolled forward, and the database is allowed to accept
transactions.no further restore operations are allowed after you recover a
database by using the withrecovery option.
Example3: Restore database
pubs from disk=’c: \test\pubsfull.bak’ with norecovery, replace
When
a restore operation uses the with norecovery option, the database or file group
state remains set to restoring.in this state,you can restore additional
backups, such as differential and transaction log backups, to apply any changes
that have occurred since the full backup was taken.
Restoring a differential backup:
to restore a differential backup, you must first restore a full backup while
ensuring that the database is not recovered. The most recent differential
backup is then applied to the database.
Example: Restore database
pubs from disk=’c: \test\pubsfull.bak’ with norecovery
Restore database pubs from disk=’c:
\test\pubsdiff.bak’ with recovery
The
first command restores the full backup, leaving the database unrecovered. The
second command applies a differential backup and then recovers the database.
For file group restore also same as fallows
Example: Restore database
pubs filegroup=’fg1’ from disk=’c: \test\pubsfull.bak’ with no recovery
Restore database pubs from disk=’c: \test\pubsdiff.bak’ with
recovery
Restoring a transaction log backup:
to restore a transaction log backup first we need to restore the most recently
full backup. We can restore transactional log backup after restoring either
full or differential backup. Based on LSN transactional log, we need to restore
the transactional log backup which is most recently done with recovery.
Example: Restore database
pubs from disk=’c: \test\pubsfull.bak’ with norecovery
Restore database pubs from
disk=’c: \test\pubsdiff.bak’ with norecovery
Restore log pubs from
disk=’c: \test\pubsdiff.bak’ with recovery
Restore will be performed with the fallowing
combinations:-
Restore
(full backup only,
Full
backup with differential backup,
Full
with transactional log backup,
Full
backup and differential backup and transactional log backup)
Backup & Restore:
Backup
& Restore is one of the routine DBA task in day to day activity.
The
below are the scenarios we get these backup & Restore tasks/tickets.
Often
developers or testers need to get the latest data from production database, in
this case developers or testers raise a ticket or send and E-Mail to take a
backup of the latest production database and restore in test db server or
development server.
Example E-Mail
Hi
DBA,
Please
take a fresh backup of Sales database on PROD2005 server and restore on
TEST2005 server.
Once
the db is restored please send an E-Mail with the update.
Regards
Testing
Team
Hi
DBA,
Please
refresh the test sales db with production sales db.
Regards
Testing
Team.
Refresh
is equal to restore in real time environment.
Common problems you face while restoring a database
from one server to another server.
1. Data
and log files are in different paths like on production server data files were
placed M: and N: disk drive and test database data and log files are placed on
O: and P: disk drives, in this case you have to use move option in restore
command.
2. Permission
issues, sometimes after the restore users are not able to connect the database
because some users get orphaned.
3. Production
db users are different than testing db users, after restoring the production db
on testing database testing db users are lost there access to testing db to
avoid this problem we should script out all the testing db users before restore
operation so that you can add all these users after the restore.
4. Space
issues – We face space issues in the following scenario Prod db size is 100 GB
and we do not have 100 GB free space on testing db server in this case our
restore will fail with the error could not allocate new page.
Best practices
1. Always
script out the users before restoring a database then execute the same script
after the restore.
2. Always
check the orphaned users after the restore so that no one will come to you
saying that they can’t access the database.
3. Always
check the database size – Always we face issues while moving the big files from
one server to another server. For example you have to move the 500 GB database
from SERVERA to SERVERB it may take more than 12 hrs and after 12 hours while
restoring you found that backup file is corrupted while transferring the backup
file from SERVERA to SERVERB – to avoid these issues always use FTP software to
transfer the .bak files so that there will be less chances of corruption.
Typically
huge databases backup will be taken using third party backup tools like Quest -
Lite Speed and RedGate, BMC SQL Back Track etc... There are lot of third party
backup tools in the market. Third party backup tools may differ from company to
company. Widely used tool is lite speed.
The
below scenario is common problem while moving the huge databases like 100’s of
GB.
Example
Your
prod db is 500 GB
You
got a request from developer or tester to take fresh copy of prod db backup and
restore on developer server or test server.
Your
maintenance window for the prod database is 1 AM to 4 AM in this case you have
just 3 hours maintenance window in this case you can’t finish 500 GB backup in
3 hours. Sometimes third party backup also may take more than 3 hours.
How
to come out of the above problem.
All
the production databases should have backup scenario like every Sunday night
FULL backup and every day night between Mon to Sat night differential backup.
Here
what you need to do is restore last Sunday FULL back up with NORECOVERY on test
server before the maintenance window starts
then take fresh differential backup then restore final backup with
RECOVERY.
By
using above scenario, fresh differential backup differential backup may finish
in 30 min and you can transfer in another 30 min and restore in another 20 min
all together you can finish the restore of 500 GB db task in less than 1.20 hr.
Here
if you take the FULL backup it will take more than 3 hours and transferring the
FULL backup from one server to another server will take more than 4 hours in
this scenario you can’t complete the task with in 3 hours.
STEPS:
1. Understand
and analyze the request like from which server to which server and at what
time, db size ect… if you are not clear ask your co-worker or your lead and
requester and clarify.
2. Take a
fresh backup of production db/ use the latest full backup which was taken by
your SQL Server Agent job as part of backup scenario.
Note:
If your db size is between 2 GB to 10 GB may be you can go for fresh backup
because db size is not so big and you can transfer with in a time.
3. Move it
to the Target server.
4. Script
out the target database users
5. Restore
the database.
6. Run the
script which was taken at step 3.
7. Check
for the orphaned users and validate the database by running some small select
statements.
8. Send an
E-Mail to the requester saying the restore is done and you need to close the
remedy ticket.
Real Time backup and Restore Lab
Hi
DBA,
Please
take a fresh backup of Sales database on PROD2005 server and restore on
TEST2005 server.
Once
the db is restored please send an E-Mail with the update.
No comments:
Post a Comment