Skip to main content

Database Backups

When you back up a database, determine whether to overwrite or append to a backup file.
The SQL Server default is to append (NOINIT) backups to a file. If you use the NOINIT
option, SQL Server appends a backup to an existing backup file or backup set.

If you use the INIT option, SQL Server overwrites any existing data on the backup media
set, but it retains the header information. If the first file of the backup set on the device has an ANSI-standard label, SQL Server determines whether the previous backup set can be overwritten. 


Use the FORMAT option to overwrite the contents of a backup file and split up the
backup set.


--Backup device

USE master
EXEC sp_addumpdevice 'disk', 'AdventureWorksBack','D:\MyBackupDir\AdventureWorksBack.bak'

--to see the media contents of the backup device

RESTORE filelistonly from AdventureWorksBack
RESTORE HEADERONLY from AdventureWorksBack

--full backup

--Full backup on backup device

BACKUP DATABASE AdventureWorks TO AdventureWorksBack

--a full database backup to the AdventureWorksBack file and overwrites any previous backups --on that file

BACKUP DATABASE AdventureWorks TO AdventureWorksBack WITH INIT

--appends a full database backup to the AdventureWorksBack file.Any previous backup files --are left intact.

BACKUP DATABASE AdventureWorks TO AdventureWorksBack WITH NOINIT

--creates a backup disk file and performs a full database backup to that file.

BACKUP DATABASE AdventureWorks TO
DISK = 'D:\Temp\MyTempBackup.bak'


--Transcation LOg Backup

--CREATING another backup device for t-log backups

USE master
EXEC sp_addumpdevice 'disk', 'AWBackLog',
'D:\Backup\AWBackLog.bak'

--backing up the t-log into device

BACKUP LOG AdventureWorks TO AWBackLog

--backing up the t-log into disk

BACKUP LOG AdventureWorks TO
DISK = 'D:\Temp\MyTempBackup.bak'


--Performing a Tail-log Backup

/*
If a database’s data files are unavailable, but the log file is undamaged, you can perform a tail-log backup to capture database activity since the last backup and use it to restore the database to the point of failure.
*/

BACKUP LOG AdventureWorks
TO DISK = 'C:\Backup\AWTail.bak'
WITH NORECOVERY, NO_TRUNCATE


--Differential Backups

--CREATING another backup device for tDifferential backups

USE master
EXEC sp_addumpdevice 'disk', 'AWBackdiff',
'D:\Backup\AWBackdiff.bak'


--to disk files

BACKUP DATABASE AdventureWorks TO
DISK = 'D:\MyData\MyDiffBackup.bak'
WITH DIFFERENTIAL

--to device

BACKUP DATABASE AdventureWorks TO AWBackdiff
WITH DIFFERENTIAL


--File or Filegroup Backups

BACKUP DATABASE PhoneOrders
FILE = Orders2 TO OrderBackup2
BACKUP LOG PhoneOrders to OrderBackupLog

/*

Partial Backups

Similar to full back up, it takes the backup of primary

To create a partial backup, include the READ_WRITE_FILEGROUPS option in your
BACKUP statement. Using the READ_WRITE_FILEGROUPS option causes SQL
Server to back up the primary filegroup and all read/write filegroups. You can also
include read-only files in a partial backup by listing them explicitly in the BACKUP
command.

*/

BACKUP DATABASE AdventureWorks READ_WRITE_FILEGROUPS TO AWBackup

--Ensuring Backup Integrity

--Backup Mirroring

/*
New in sql server 2005

SQL Server supports mirroring of backup media, increasing the reliability of backups by
providing redundancy.

Mirroring a media set increases backup reliability by reducing the
impact of backup-device malfunctions.

These malfunctions are particularly serious because backups are the last line of defense against data loss.

Mirroring applies to both disk and tape.

All backup devices for a single backup or restore operation must be of the same type—disk or tape. Within these broader classes, you must use similar devices that have the same properties, such as drives with the same model number from the same manufacturer.
Insufficiently similar devices generate an error message (3212).
*/

BACKUP DATABASE AdventureWorks
TO BackupOrginal
MIRROR TO BackupMirror
WITH
FORMAT

--Backup Checksum

BACKUP DATABASE [AdventureWorks] TO AWBackup WITH CHECKSUM

--RESTORE VERIFYONLY

/*

Checks performed by RESTORE VERIFYONLY include:
• Ensuring that the backup set is complete and all volumes are readable.
• Ensuring the integrity of some header fields of database pages, such as the page ID
(as if it were about to write the data).
• Ensuring the validity of the checksum (if present on the media).
• Ensuring that there is sufficient space on destination devices.

*/

RESTORE VERIFYONLY FROM AWBackup

Labels:

Comments

Post a Comment

Popular posts from this blog

Digging into a long running job

Revisiting the blogger to finally start again blogging about the SQL Server after a long long time.  This post is on analyzing and digging into what is causing a job significant time to complete. This will be a basic level of approach and I wish, I could have done better. But, its was enough in my case to get to a conclusion, with the knowledge I equip.   Issue : A job that's scheduled to run every three hours, was running for more than 48hours. Average job run time, 1.5hrs.  Analysis : First and foremost thing to do, is to find out on what session id is the job running on the instance. You may use the below query to fetch the session id of the job from the job name. declare @Var1 varchar(36) select @Var1=substring(REPLACE(CAST(job_id AS VARCHAR(36)),'-',''),17,16) from sysjobs where name =' JOB_NAME ' select * from sys.sysprocesses where program_name like '%'+@Var1+'%' You may get a single row or multiple rows with ...
Post a Comment
Read more

Deferred prepare could not be completed

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.
4 comments
Read more

Incorrect SET Options with Filtered Indexes

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error: The following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed. The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF. Reason: The following are the SET Options that are needed while operation on a table with a filtered index. Required SET Options for Filtered Indexes The SET options in the Required Value column are required whenever any of the following conditions occur: Create a filtered index. INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a...
Post a Comment
Read more