April 24, 2012 at 7:37 am
I currently use a sql server maintenance plan to make my sql server backups (and other maintenance tasks), and I'd like the backups to also go to another location, in case my backup location fails.
As far as I can see, there isn't an option to do this in the maintenance plan, so is the only option to add another database backup task, pointing to the other location?
April 24, 2012 at 7:45 am
you could use tools like robocopy, xcopy, SAN replication (if you use a SAN) to copy files between locations which will save you backing up twice
April 24, 2012 at 7:50 am
Yeah, I currently use a xxcopy script to do this, but as the backups are on a drobo, I have to run the xxcopy script on a seperate machine, so the copy of the backups has to go via this 3rd machine, slowing the process down a lot.....
That's why I was wondering about making the sql server backup do the job.
April 24, 2012 at 7:52 am
why dont you add the script as a step in the job after the backups have completed, that way the sql agent runs the script for you
April 24, 2012 at 7:59 am
Create a SSIS package as it has in-built task to perform the backup and move the files. And schedule the package in SQL Server Agent Job.
---------------------------------------------------
Thanks,
Satheesh.
April 24, 2012 at 7:59 am
...Won't that just make the backup copy go via the sql server - not speeding anything up?
April 24, 2012 at 8:00 am
...also, I don't know how to add batch files to sql server maint plans, how do you do that?
April 24, 2012 at 8:07 am
You are saying that if the backup location fails, you want it to try another location? If so, don't use a maintenance plan, use a script that can handle this.
Jared
CE - Microsoft
April 24, 2012 at 8:08 am
two ways
you will either need to create a new job which has a cmdexec step and pass in the script, then modify the job which is created from the maintenance plan and give it a new step which starts the newly created job
or
just add a new step to the maintenance plan job which runs a cmdexec step
as Satheesh said you could use SSIS with a filesystem task which will copy the files for you
your right, it wont speed anything up but if you backup twice the first backup is worthless as you cant apply any tx logs to it after you have taken the second backup so your first backup is only valid for restoring in the period it takes to backup the database a second time.
other things you might want to look at are weekly full backups along with daily diffs and transaction logs so that your not backing up and copying the full database nightly.
April 24, 2012 at 8:08 am
guy 10462 (4/24/2012)
...also, I don't know how to add batch files to sql server maint plans, how do you do that?
Don't do a batch file... Create a job with an Execute SQL step.
Jared
CE - Microsoft
April 24, 2012 at 1:24 pm
OK - I think I'm adding to confusion, let me try to clear things up....
I'm currently using the SQL server maintenance wizard to do a database backup to a drobo.
I have another drobo that I'd like this backup to go to as well - in case the first drobo fails.
Reading the posts above, it looks like I can add a tsql script as part of the maintenance plan, and doing some googling, it looks like I could use something like...
BACKUP DATABASE Wibble
TO DISK = 'C:\Backups\Wibble.bak'
MIRROR TO DISK = 'D:\Backups\Wibble.bak'
...am I right in assuming this will do what I want?
April 26, 2012 at 1:33 pm
Something in my brain just went 'what?'
We currently keep the last 6 days of backups, so are you saying that the older 5 backups are worthless? - or is it that the backup will only be valid at the point it was taken, i.e. if I do a backup, then 10 minutes later another backup, the 2 backups won't be the same?
If the case is that the 2 backups aren't the same, that isn't such as problem, as hopefully if I ever need to restore, I'll be restoring the latest backup, only needing to go to older backups if the backup data is corrupted.
April 26, 2012 at 1:40 pm
guy 10462 (4/26/2012)
Something in my brain just went 'what?'We currently keep the last 6 days of backups, so are you saying that the older 5 backups are worthless? - or is it that the backup will only be valid at the point it was taken, i.e. if I do a backup, then 10 minutes later another backup, the 2 backups won't be the same?
If the case is that the 2 backups aren't the same, that isn't such as problem, as hopefully if I ever need to restore, I'll be restoring the latest backup, only needing to go to older backups if the backup data is corrupted.
There are several reasons to keep old backups for a specified period of time. i.e. What if a mistake from 3 days ago is not realized until today? In that case, as was the case with a previous company, the mistake that day was worth re-entering all of the data afterward. So we restored the database to 3-days ago and then re-entered all data that was done for those 3 days. This was an accounting application, so you can see the impact.
Logically, I think it also makes sense because a corrupt data page is only recognized when it is read from disk. If you are not doing regular database integrity checks, you may not find this corrupt page of data until several backups later. (This example is purely theoretical given the limited knowledge I have about data corruption, but it logically makes sense to me.)
Jared
CE - Microsoft
April 26, 2012 at 1:53 pm
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'
MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks.bak'
WITH FORMAT
GO
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 1:57 pm
guy 10462 (4/24/2012)
BACKUP DATABASE WibbleTO DISK = 'C:\Backups\Wibble.bak'
MIRROR TO DISK = 'D:\Backups\Wibble.bak'
...am I right in assuming this will do what I want?
Yes it will. It will write the complete backup to the C drive and another complete backup to the D drive, it will do those simultaneously.
Just be aware that if either location becomes unavailable, the backup fails. So if you are backing up to local disk and a network share and the network drops, the backup will fail immediately.
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply