Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup SQL Server file to remote Hard disk Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 12:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 7, 2012 6:48 AM
Points: 14, Visits: 20
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
Post #1341041
Posted Tuesday, August 7, 2012 1:11 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 2:10 AM
Points: 1,470, Visits: 8,345
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.


BrainDonor
Linkedin
Blog Site
Post #1341047
Posted Wednesday, August 8, 2012 3:59 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:58 PM
Points: 367, Visits: 615
I agree. If you backup to a remote drive, backups will fail if the drive has connections or hardware issues.

Post #1342292
Posted Wednesday, August 8, 2012 4:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
Going to add a third agreement to backup local, move to network.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1342295
Posted Wednesday, August 8, 2012 7:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:18 PM
Points: 3,952, Visits: 7,170
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; They'll drag you down to their level and beat you with experience"
Post #1342340
Posted Thursday, August 9, 2012 1:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:57 PM
Points: 880, Visits: 2,433
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).
Post #1342989
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse