can we take backup at two sparate disk at one time .

Question

can we take backup at two sparate disk at one time .

Ivan Mohapatra

SSCarpal Tunnel

Points: 4452
More actions
April 7, 2011 at 12:40 pm

#248507

hi,
how can we take backup at 2 sparate disk at one time ?
can any one proivde me the code or the process by which it can be made it possible.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

Ivan Mohapatra (4/7/2011)
hi,
how can we take backup at 2 sparate disk at one time ?
can any one proivde me the code or the process by which it can be made it possible.

You're not entirely clear on what it is you require exactly.

Do you want to stripe to multiple files on different disks or do you want to mirror the backup files to a different location?

Examples are shown below

Stripe to multiple files

BACKUP DATABASE MYDB TO

DISK = 'K:\MSSQL\BACKUP\MYDB-1.BAK',

DISK = 'L:\MSSQL\BACKUP\MYDB-2.BAK',

DISK = 'M:\MSSQL\BACKUP\MYDB-3.BAK'

WITH INIT, STATS = 10

[/CODE]

Mirror to another location

BACKUP DATABASE MYDB TO

DISK = 'K:\MSSQL\BACKUP\MYDB-1.BAK'

MIRROR TO DISK = '\\DRSERVER\BACKUPSHARE\MYDB-1.BAK'

WITH FORMAT, STATS = 10, INIT

All together

BACKUP DATABASE MYDB TO

DISK = 'K:\MSSQL\BACKUP\MYDB-1.BAK',

DISK = 'L:\MSSQL\BACKUP\MYDB-2.BAK',

DISK = 'M:\MSSQL\BACKUP\MYDB-3.BAK'

MIRROR TO

DISK = 'DRSERVER\BACKUPSHARE\MYDB-1.BAK',

DISK = 'DRSERVER\BACKUPSHARE\MYDB-2.BAK',

DISK = 'DRSERVER\BACKUPSHARE\MYDB-3.BAK',

WITH FORMAT, INIT, STATS = 10

[/CODE]

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

M&M SSC-Insane Points: 21700 More actions · Answer 2

While striping to multiple file locations, on what basis would they be striped.

Eg. if total backup size is 100 GB, and if we create 10 stripes, each stripe would be 10 GB right?

M&M

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

mohammed moinudheen (4/8/2011)
While striping to multiple file locations, on what basis would they be striped.
Eg. if total backup size is 100 GB, and if we create 10 stripes, each stripe would be 10 GB right?

SQL Server 2008 uses round robin to write to the files distributing data and I\O. It is best to use disks of comparable performance when backing up to files on different disk sets

The ratio is indeed individual file size = total backup size / number of files

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

What happens if you have a super fast disk array with 1 bad apple in the bunch?

Ivan Mohapatra SSCarpal Tunnel Points: 4452 More actions · Answer 5

i mean

Stripe to multiple files

BACKUP DATABASE MYDB TO

DISK = 'K:\MSSQL\BACKUP\MYDB-1.BAK',

DISK = 'L:\MSSQL\BACKUP\MYDB-2.BAK',

DISK = 'M:\MSSQL\BACKUP\MYDB-3.BAK'

WITH INIT, STATS = 10

thanks a lot .

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Ninja's_RGR'us (4/8/2011)
What happens if you have a super fast disk array with 1 bad apple in the bunch?

array performance would be degraded admittedly

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

M&M SSC-Insane Points: 21700 More actions · Answer 7

mohammed moinudheen (4/8/2011)
While striping to multiple file locations, on what basis would they be striped.
Eg. if total backup size is 100 GB, and if we create 10 stripes, each stripe would be 10 GB right?

Thanks Perry

M&M

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 8

Hi;

As perry said its a very good solution of the topic,

But, If you are using MIRERR to option the Media must be same tyoe,

tape and tape or HDD and HDD.

Ali
MCTS SQL Server2k8

Ivan Mohapatra SSCarpal Tunnel Points: 4452 More actions · Answer 9

if i am taking backup from a remote system by using sql server Authentication login id and password

it is throughing error.why soo ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Are you going to tell us the error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ivan Mohapatra SSCarpal Tunnel Points: 4452 More actions · Answer 11

the error shows like this.

my t-sql code is

BACKUP DATABASE AdventureWorks

TO DISK = 'D:\Ivan\AdventureWorks.bak'

GO

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device 'D:\test_k.bak'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Please don't post the same question in several places.

Replies to this please to http://www.sqlservercentral.com/Forums/Topic1115242-1550-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ivan Mohapatra SSCarpal Tunnel Points: 4452 More actions · Answer 13

i am sorry plz reply the answer in as gila told.

thanks in advance