Posts Tagged ‘SQL Server Backup’

SQL Server – Mirror Backup

Posted: November 7, 2016 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , ,
0

As a DBA it’s a TOP PRIORITY to make sure that all database backups should be useful in case of any Disaster and should know how to recover database from a Disaster.
In Real time, with every SQL Server environment, we regularly take database backup on various available backup devices. With SQL Server 2005 onwards, there is an option of Mirror Backup with enterprise edition. Using Mirroring a backup media set increases backup reliability by reducing the impact of backup-device failure. These type of failure sometime becomes very serious because backups are the last heartbeats against data loss. In current era, database sizes are increasing drastically, and as a resultant the failure of a backup device or media will make a backup non restorable. Mirroring backup media increases the reliability of backups by providing redundancy.

As per below given example, here I am taking backup at two locations,


Point to be Note here is the number of media families and mirrors is defined when the media set is created by a BACKUP DATABASE statement that specifies WITH FORMAT keyword.

Keep reading
J
!.

The Fastest way to BACKUP SQL Server Database

Posted: April 21, 2015 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , ,
10

Now days, as it’s very common and normal trends, The DB size as 100~150…. n GBs are being considered the normal, and even many databases are crossing the terabyte range. In general practice commonly a DBA or sometime Developer starts backups in one process that take a long time and Database is continuing to grow, this back time will increase accordingly, to reduce the backup time, it’s always suggested take backup in Disk instead of tape in off hours. It’s my personal opinion always take backup to San Disk and then archive it to Tape. Disk capacity is matter because it’s directly proportional to costing/expense and to reduce backup size we can use compression. As we know taking a Full backup is very intensive I/O process as every data page in the database must be read and then written to another file. It’s my personal observation, having multiple physical disks, we can achieve higher I/O rates and complete the backup process more quickly with in addition to writing to multiple files, it is also helpful to write to different physical disks to alleviate the I/O bottleneck.

Here are steps, How to take Backup on multiple disks / multiple files using SSMS.

Step 1) Right Click on Database à Task à Back Up

It will shows screen as

Step 2) Click on Add from Destination section and set there multiple physical disk with multiple files by clicking on ADD button again and again.

And finally suppose we have set 2 disk with 2 files it will show like as below

Step 3) Click on OK, Backup will start with 2 physical disks.

The sample script for this process will be as below,

BACKUP DATABASE [VirendraTest] TO DISK = N’C:\VirendraTestOnCdrive.bak’,
DISK = N’E:\VirendraTestOnEdrive.bak’ WITH NOFORMAT, NOINIT,
NAME = N’VirendraTest-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 1
GO

 

Please suggest, your finding.

All Database Backup – SQL Express

Posted: December 30, 2013 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , , ,
0

A user who is using SQL Server express edition, came to me and ask how to take All Database Backup at a time. Here is a simple script to take all database backup at a specified location.

DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) ––– path for backup files
DECLARE @fileName VARCHAR(256) –– filename for backup
DECLARE @fileDate VARCHAR(20) –– used for file name
SET @path = ‘D:\DBBACKUP\’
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name  TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor