August 7, 2012 at 12:56 am
Sir,
I Want to backup sql server file in remote hard disk directly.But sql server takes only internal drives as backup location.
Someone help me to solve this problem
August 7, 2012 at 1:11 am
You can, but the recommendation is that you don't.
See 'Backing Up to a File on a Network Share' within http://msdn.microsoft.com/en-us/library/ms179313.aspx#NetworkShare.
You would be better off backing up to a local drive and then moving the backup file.
August 8, 2012 at 3:59 pm
I agree. If you backup to a remote drive, backups will fail if the drive has connections or hardware issues.
August 8, 2012 at 4:05 pm
Going to add a third agreement to backup local, move to network.
August 8, 2012 at 7:54 pm
rajprabuit (8/7/2012)
Sir,I Want to backup sql server file in remote hard disk directly.But sql server takes only internal drives as backup location.
Someone help me to solve this problem
DO you mean like some $80 external hard drive you bought at Best Buy? Or something a little better like a READYNAS or even much better SAN?
What version of SQL server are you using and do you use any 3rd-party backup compression software?
The reason I ask is most software compression tools add in network resiliency (to avoid lost packets/network interruptions, etc)
I don't see any reason not to back up a database to an external location (providing it's a sound/hardware-wise storage location), in fact, I back up hundreds of them (up to compressed 200GB in size) all the time and rarely have issues.
I do get the odd blip...but I get notified and then just rerun the job - in the vent the kind of location you are talking about it a "Best Buy" type of solution, I'd agree with everyone else 😉
BACKUP DATABASE [F1Settings] TO DISK = N'\Networkstorage\backupfolder\servername\MyBackup.bak' WITH NOFORMAT, NOINIT,
NAME = N'MyDBBackup-Full', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DECLARE @backupSetId AS int
SELECT
@backupSetId = position
FROM
msdb..backupset
WHERE
database_name = N'MyDBBackup'
AND backup_set_id = (SELECT
MAX(backup_set_id)
FROM
msdb..backupset
WHERE
database_name = N'MyDBBackup')
IF @backupSetId IS NULL
BEGIN
RAISERROR(N'Verify failed. Backup information for database ''MyDBBackup'' not found.', 16, 1)
END
RESTORE VERIFYONLY FROM DISK = N'\Networkstorage\backupfolder\servername\MyBackup.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 9, 2012 at 1:57 pm
SQL Server will happily take UNC's; I've seen backups to SANs, to SNAP drives on a SAN, to local disks (correctly to spindles other than the spindles holding the database and/or logs, so the loss of one array/set of spindles doesn't lose everything), to the same disks as hold the data and logs (bad: lose one array, lose everything), etc.
Personally, I'm quite happy to backup to a highly available network share on a highly robust network, as long as the throughput's present for good performance not only for the backup, but for the network as a whole, and for the target share's other users (if any). If the network and the target are very reliable, this immediately gets your backup off the database server or potentially off the main SAN, so a single crash (or potentially fire) doesn't kill data + backups.
If you're going to try backups over a network, then test your network first - run some of these and see if they work on heavy use days.
For those environments which are not as robust, then the specter of backups failing due to network issues rears its ugly head, and that's where "backup local, then copy off" is the only reasonable answer - it's just a little more complex to handle, and you have to consider how you handle each backup after the previous N copies failed (especially if you want to offer PITR from the copied backups). Robocopy's "mirror" mode may serve well here. Further, you need to increase your capacity planning to have more local drive space (though it can be much, much slower - arrays of 7200RPM SATA drives should have quite good pure-sequential performance).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply