SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backing Up/Restore to non-local drive


Backing Up/Restore to non-local drive

Author
Message
YSLGuru
YSLGuru
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4664 Visits: 1667
Thanka to all who replied.

What we've found is that we get our best bang for the time by performing a native back up locally and then moving it across the LAN to the new DB Server and doing a native DB restore as opposed to using Microsoft's DPM2012 (Data Protection manager 2012). The IT Admin is not sure why DPM takes so much longer to do a DB restore then it takes to move a backed up copy of the Db between servers and then restore it but it does. DPM is almost twice as long to use versus native SQL Backup & Restore.

DPM however does have 2 key advatanges that make it the preferrred recovery method for normal day to day use (as oppose dto a one time relocation of the DB between servers) and they are

(1) Space - DPM does not require a copy of the DB (backed up) be placed on the SQL Server locally before restoring the DB.
(2) Real Time Recovery Availability - DPM is always ready to restore the DB where as the native process require we get a backup foirst and then move it, restore it and then dump the copy of the bak file.

Thanks to all who replied.

Kindest Regards,

Just say No to Facebook!
webtomte
webtomte
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 226
I agree with tim.cloud that you should have a backup drive attached to your database system.

I normally set up the following when creating new SQL server
C: for OS ~ 60GB (nothing else on this than primary and shared SQL stuff)
D: allways CD or DVD drive
E: Datafiles (ask or check initial size, annual growth and usage before deciding size and RAID)
F: Logfiles (I choose 1,5 times the size of calculated Datafiles, at least RAID 5)
G: backup (I choose at least 2 times the size of calculated Datafiles)

Considerations has to be made of instances, Analyzing Services and such, depending on how many drives one should have.
There is also on heavily loaded system sometimes a need for a TEMPDB drive.
SQListic
SQListic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1634 Visits: 1115
EXEC xp_cmdshell 'net use v: \\ServerName\foldername'


Here we are assigning V as drive letter to the \\ServerName\foldername'

-----------------------------------
BACKUP DATABASE [XYZ] TO
DISK = N'v\xyz.bak'
WITH stats = 10, format
paul.knibbs
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4324 Visits: 6240
Sqlism (9/6/2012)
EXEC xp_cmdshell 'net use v: \\ServerName\foldername'


Here we are assigning V as drive letter to the \\ServerName\foldername'

-----------------------------------
BACKUP DATABASE [XYZ] TO
DISK = N'v\xyz.bak'
WITH stats = 10, format


What's the point of mapping the drive letter? SQL will back up to the UNC path quite happily provided the permissions at both ends are set up properly (and if they're not properly configured, mapping the drive isn't going to help anyway). Mapping the UNC path as a drive letter doesn't make the network more reliable or faster in any way, so it's still not the best idea unless you're dealing with relatively small databases (a few gigabytes, maybe).
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