Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup SQL Server file to remote Hard disk


Backup SQL Server file to remote Hard disk

Author
Message
rajprabuit
rajprabuit
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2014 Visits: 11167
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.

Steve Hall
Linkedin
Blog Site
jerry-621596
jerry-621596
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 644
I agree. If you backup to a remote drive, backups will fail if the drive has connections or hardware issues.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37926
Going to add a third agreement to backup local, move to network.

Cool
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)
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4856 Visits: 7363
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" ;-)
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1027 Visits: 2673
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).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search