SQL remote Backup

[rdfdr78]

Hi There,

I`m backing a 16GB file up every night and have limited space on the server, I want to be able to redirect the backups to a remote server however it seems i can not do this. I want to continue to do a full backup each night.

Help Appreciated

Richard

 

[Catadmin]

You can only backup a SQL Server database to a local drive, or drive the Server thinks is local (like a SAN drive).

The best way to take care of your issue is to backup the databases locally, then move the files to your network shares. You can do the move with a windows batch file or do it with xp_cmdshell and a T-SQL Script. Either way, you'll want to make sure your file actually made it to the network share in question before deleting your local copy.

Also, it wouldn't hurt to test a few of these backups and make sure you can restore from them. There's nothing worse than having a corrupted backup and not knowing it until you need the darned thing.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mutley1]

What we have done in the past works, but is by no means ideal.

1. On the target server, create a share
2. On the SQL Server, map a network drive to the share on the target server, using the "reconnect at logon" option (in case you need to reboot and forget to remap the drive).
3. Create a backup device pointing to the mapped network drive (as it will appear as a drive on your SQL box now).
4. Backup to that device.

As I said, it's not ideal as there are other factors that will play a part, such as network issues may cause backup failure, it takes longer backing up over the network, is it OK for you to produce that amount of traffic during the night etc. etc. etc.

One other method we use when we only have space for 1 backup is to schedule a PHP coded job to copy the current backup to another server, do a restore verify only, and if the verify only comes back OK AND the size of the original .BAK file and the one copied are exactly the same (to the byte on a 50GB file) then delete from the originating server.

HTH,

M.

 

[rdfdr78]

Thanks Guys for helping out. Will try suggstions.

Cheers Richard