May 28, 2009 at 2:29 pm
Using Microsoft SQL Server 2005 - 9.00.3042.00 (Build 3790: Service Pack 2)
Was wondering if I can I do a SQL "mirror" db backup (FULL, DIFFERENTIAL, OR Trans_Log) to different locations same time. For example, can I write 2 sets of the SAME transaction logs backup files to File_Server_A & File_Server_B without messing up the just- in-time restore process.
PS - If so, we have the options to use Litespeed or native 2005 SQL backup software.
Thanks in advance.
May 28, 2009 at 2:36 pm
Look at "log shipping" -that's a SQL Server managed process that would do exactly what you are looking for.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 28, 2009 at 2:51 pm
May have answered my our question. The answer is yes with "MIRROR TO" keyword:
BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak' MIRROR TO DISK='F:\MyDatabase.bak'
May 28, 2009 at 2:53 pm
Yes, absolutely. From 2005 SQL allows you to mirror the backup files to 2 (or more) locations. They must both be the same type of device (eg both disk or both tape) and if writing to either one fails the entire backup is aborted.
BACKUP DATABASE <DB Name>
TO DISK = <disk location 1>
MIRROR TO DISK = <disk location 2>
MIRROR TO DISK = <disk location 3>
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
May 28, 2009 at 3:19 pm
GilaMonster (5/28/2009)
.........and if writing to either one fails the entire backup is aborted.
thats an important point in this case as you are backing up across the network, be aware you are increasing the chances of the backup failing.
---------------------------------------------------------------------
May 28, 2009 at 4:12 pm
I am not sure if this can be done with Litespeed, haven't tried it myself. I don't know if that option is available.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2009 at 4:27 pm
Mirrored backup will not help incase network latency is more because entire process will get aborted incase any one backupset fails.. Also it is available only in Enterprise Edition of SQL 2005
From your question, I can understand that you want a copy of backup without affecting the restore chain, So you can use COPY_ONLY option in the BACKUP command.
BOL says:
COPY_ONLY
Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.
Take a look at this video tutorial for more information on these options
http://wtv.watchtechvideos.com/topic124.html
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply