June 11, 2009 at 2:23 am
Hi Expertz.......
I have a scheduled weekly backup job.
There is not enough space in the server(s1) that has my sql database to take backup of all the 119 databases at once and then moving to s2.
So i move the backups one by one manually.
Is it possible to take backup directly to shared drive of s2.
"I cant use xp_cmdshell" as its disabled in s1.So need query that doesn't use xp_cmdshell.
Please help to solve the issue....
Tanx 😀
June 11, 2009 at 4:03 am
Try adding the backup drive as a mapped network drive in the server and try it out
[font="Verdana"]Thanks
Chandra Mohan[/font]
June 11, 2009 at 8:48 am
Yes there is, you will have to use backup T/SQL command :
BACKUP DATABASE AdventureWorks
TO DISK='\\\AdventureWorks.bak',
Only using T/SQL You can specify the \\PATH.
Oded
June 11, 2009 at 9:23 am
I would not back up to remote drives directly. If there is any network hiccup, your backup will fail, and that will be the day Mr. Murphy fails one of your disks.
If you have space issues, I'd set a script to make the backup of a db, then when it succeeds, copy it to the remote drive, then delete the local backup. This would require some scripting, but you could build a script in VBScript/Powershell to do this and execute that from a job step.
It seems like a lot of work, but you could script it, search/replace, and set a whole series of jobs up easily. It's not worth you missing backups to save a few hours of work.
June 12, 2009 at 3:06 pm
Steve Jones - Editor (6/11/2009)
I would not back up to remote drives directly. If there is any network hiccup, your backup will fail, and that will be the day Mr. Murphy fails one of your disks.If you have space issues, I'd set a script to make the backup of a db, then when it succeeds, copy it to the remote drive, then delete the local backup. This would require some scripting, but you could build a script in VBScript/Powershell to do this and execute that from a job step.
It seems like a lot of work, but you could script it, search/replace, and set a whole series of jobs up easily. It's not worth you missing backups to save a few hours of work.
You don't need xp_cmdshell or powershell, while I do like powershell myself. 🙂 I do agree with Steve on the network issues...
Step 1 in Agent Job / tsql step
backup database MyDatabase to disk = 'H:\mydatabase.bak'
Step 2 / change to operating system step
if exist h:\mydatabase.bak (move h:\mydatabase.bak \\RemoteServer\Share\mydatabase.bak /y)
etc...
Make sure the Sql Account used to start sql has access to the share so it can write the files appropriately.
June 16, 2009 at 1:00 pm
Plan for any backup tool like veritas or red gate if not we need to use \\path create a folder and share it to s1 . Give full permissions .
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply