Backup to mapped drive?

[mutley1]

Hi All,

We have 2 servers connected via a crossover cable and are trying to back up a database over the cable.

Server 1 houses the database that needs backing up to server 2. I have mapped a drive to server 2 using the IP (as required) because there is no name resolution between the 2. The destination folder is shared out and that is the direct mapping on server 1.

The account running SQL has full security permissions etc. and I have logged on to the server as the SQL domain account to set up a backup device.

Problem - The drive letter I have mapped does not show as an available drive for devices (or even when trying it manually in EM). I know I have done this before (all those years ago).

Anyone got any ideas why the drive won't display?

Cheers,

M.

 

[Jamfool]

you would need to use the UNC path.

BACKUP DATABASE [MyDb] TO
DISK = N'\\MyComputer\stuff\temp\backup.bak'
WITH NOFORMAT, NOINIT, NAME = N'Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

 

[mutley1]

Thanks Jamfool,

I've got it running using the UNC earlier today but for the life of me can't remember how I got a device set up in the past. Will try messing with ideas later.

Cheers,

M.

 

[mrdenny]

You can setup a backup device to the unc path, just not through the gui.

Backup devices are not required for backing up databases. The BACKUP DATABASE command provided above will work nicly for backing up.

It is recommended to never use mapped drives with SQL Server as you never know if the mapped drive will be available the next time it's needed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mutley1]

Hi Denny,

Thanks for that - I wholly agree with you on the availability issue, it is just we are now desperately short on space due to the size of the db and the customer is not going to pay for disks / server due to an upcoming transition, so we are at an "all hands to the pump" stage. No "direct to tape" backup, can't move anythinig else off the drive, can't rebuild for space allocation etc. etc. so needs must as the devil drives.

I already have scripts that create a dump device and do the backup (locally) and got it working with the full UNC this morning.

Thanks, as always, for your sage advice.

Cheers,

M.

 

[mrdenny]

You can create a backup device to a network share, you just can't use the GUI. You have to use the T/SQL to create it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com