Mapped Drive backups

Question

Mapped Drive backups

bryan99y

SSCarpal Tunnel

Points: 4672
More actions
November 5, 2002 at 8:41 pm

#64206

Can a SQL Server 2000 database be backup up to a mapped network drive?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114602 More actions · Answer 1

Is the backup to a local drive then copy to a remote drive not an option? This would ensure a failure on the network doesn't prevent your backup from running.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 2

good point.

but is it possible to back up to a mapped network drive?

network share?

timwilliams Ten Centuries Points: 1007 More actions · Answer 3

Yes

Map a drive to the location you want to back up to

Create a backup device with the file path to the location (you can browse to the location)

I just wish I could do this in SQL 7.0 !

Tim

Antares686 SSC Guru Points: 125444 More actions · Answer 4

I do believe thou it has to be within the same domain (you will have to make sure the account peforming the backup has proper rights to the share) or on a share that has public write access. Otherwise you will get a login failure.

Edited by - antares686 on 11/06/2002 03:54:12 AM

Greg Larsen SSC-Insane Points: 20966 More actions · Answer 5

You can backup a database to a network share in SQL Server 7.0. Just like you said, create a backup device that points to a UNC name, then backup the database to that backup device. Yes that account creating the backup will need access to that network share.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 6

bryan99y

SSCarpal Tunnel

Points: 4672

November 6, 2002 at 8:02 am

#440476

Is this the case for SQL Server 2000 also?

Greg Larsen SSC-Insane Points: 20966 More actions · Answer 7

Yes you can also create network backups using a BACKUP devices that point to UNC names in SQL Server 2000.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

sa24 SSCrazy Eights Points: 8027 More actions · Answer 8

Yes, you can do that.

But it will take a long time. I had 100 gigs of database and backed up the database over the network and it took over 5 hours for the backup to be completed.

Antares686 SSC Guru Points: 125444 More actions · Answer 9

That also depends on your infrastructure and network topology. If you have to cros a number of router segments and have other traffic on the line it will take longer. 1000/100/10 mb network speeds process data at different rates. And of course "Size Does Matter".

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 10

I believe to do a backup to a maped drive you have to set that drive on the profile of the user account SQL Server is configured with. If you have configured it correctly then use xp_fixeddrives to confirm it appears.

UNCs paths are much easier.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

timwilliams Ten Centuries Points: 1007 More actions · Answer 11

Greg - Thanks...

I had always assumed that because you could not browse to the drive when creating the device in SQL7, that you could not use a network share.

Typing in the full UNC path worked a treat...

Tim

nasdaq SSC Journeyman Points: 88 More actions · Answer 12

Not sure if you need to do this if the servers are domain members.

using SQL2k sp3.

Create an account on the sql machine Call it SqlServer, make sure it has "Run as service" rights.

Create an account on the machine that your backing up to with same name, and password.

Use UNC paths i.e. "\\backupbox\backupdirectoryshare", to the backup location when your setting up the maintanence plan.

Test the permissions by running the plan.

You could even get fancy and create a scheduled DTS job, that if the network was unreachable that it would back it up to a local drive, add a mail account too, and send an email to notify you.

I can't imagine, even in a Domain/Forest that the LocalSystem Account on the sql server would have permission to access network resources.

hisherts SSC Enthusiast Points: 101 More actions · Answer 13

Just to clarify a point that Antares686 made, you can backup to a network share in a different domain as long as the user account that SQL Server is running under has permissions either in both domains or has explicit permissions on the share.