Adding a new backup device and local drive RRS feed

  • Question

  • I have a SQL 2005 cluster running in vms on VMware ESX 3i.

    When I attempt to add a "New Backup Device" and browse to a drive that is not the default "Backup" location (R:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup in this case), I am unable to see any drives except "R:" in the "Locate Database Files" window. I'm trying to backup to a drive which is a cluster resource that is online and which shows up under My Computer on the node it is currently owned by and can be browsed to, etc. There are other disks configured as cluster resources but the only disk visible in "Locate Database Files" is the drive specified as the SQL Data drive during installation. 

    I am able to successfully backup to the R: drive. but I want to use the T: drive as my Backup device/location.
    The ACL looks the same on the T: drive as it is on the R: drive so I don't see a security problem
    If I manually enter the path to the T: drive in the File box in the new backup device dialog then a backup device is set up under Server Objects -> Backup Devices in Mgmt Studio and I can select that backup device as the destination backup device but the backup fails.

    Shouldn't the other local drives be visible in the "Locate Database Files" window?

    Thursday, February 12, 2009 10:33 PM

All replies

  • Hi

    You can create a backup device as follows:

    EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'mybackupdevice', @physicalname = N'\\msreekar\s.bak'

    Take a back up as follows:

    BACKUP DATABASE [test] TO [mybackupdevice] WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10


    Make sure you have the necessary permissions to write intot the path given in the creation of the backup device.

    Refer http://msdn.microsoft.com/en-us/library/ms188409.aspx for more information.


    Friday, February 13, 2009 11:35 AM