RSS
Feed
Saturday, July 9, 2011 | 
Backup is one of the most important tasks of DBA. Without
having proper backup, you are running in danger mode always so it is good to
keep more than backup file with you always so in any emergency if one backup
gets corrupt or missing, you can use other one.
In early days of SQL Server, especially before SQL Server
2005, DBA used to take full backup of database and copy it somewhere else via
DOS command XCOPY to make another copy of the same to other disk drive or
somewhere in network but SQL Server 2005+ provides the facility to take backup
with mirror copy so you don’t need to put additional efforts to make copy of
your database backup and move it somewhere else.
Suppose my database name “SQLHub” so I would use following command
to take copy of that database in D drive and mirror copy in E drive.
--database back
up of SQLHub
--full back in D
drive SQLHubBackup folder
--same backup
copy will be copied at E drive SQLHubBackup folder
BACKUP DATABASE SQLHub
TO DISK
= 'D:\SQLHubBackup\SQLHub.bak'
MIRROR
TO DISK
= 'E:\SQLHubBackup\SQLHub.bak'
WITH FORMAT
GO
“With Format” option in backup is not mandatory. If we take
backup of database with backup file name SQLHub.bak on 8th July 2011,
when we again run the same database backup with same backup file name “SQLHub.bak”
on 9th July 2011, SQL Server keeps previous backup of database in
SQLHub.bak file and copy the next so your both backup taken on 8th
and 9th july 2011 will now be there in SQLHub.bak file, if you have NOT used “WITH FORMAT” option.
If you want to take backup on 9th July 2011 and want
to remove database back taken on 8th July 2011, which is there with
the same backup file name “SQLHub.bak”, you have to use “WITH FORMAT” clause.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”
0 comments:
Post a Comment