Backing up to a logical device

[jeepxo]

I want to create some redundancy in my database backups.

Right now we backup the DB to the default location, then we use a tape backup everynight to backup the drives.

What I would like to do though is backup the DB to a network share as well. I can find how to define a logical device, but my only options are local drives on the SQL server.

Is there a way to define a logical device as a network share, then use the sql maintenance plan to backup to that network share instead of to a local drive?

"Every day is like a precious gift, you have to make it count" James Birrell 1994-2002

 

[Corran007]

yes on logical, but you will have to write your own backup routine for it. i do not know of any way to have the maintainenance plan use a device.

two ways for the logical. map a drive and then use em to make it. manually write out the create device script (this is the prefered way) see books online for more info

here is an example of a remote backup script

BACKUP DATABASE [dbName] TO DISK = N'\\server1\D\MSSQL7\BACKUP\Backup.bak' WITH INIT , NOUNLOAD , NAME = N'dbName backup', SKIP , STATS = 10, NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='dbName'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='dbName')
RESTORE VERIFYONLY FROM DISK = N'\\server1\D\MSSQL7\BACKUP\Backup.bak' WITH FILE = @i

 

[MaffewW]

Corran, I may be wrong but I'm pretty certain SQL Server cant backup to mapped drives

Matt

Brighton, UK

 

[ecobb]

As long as you have permissions, SQL Server doesn't have any problems backing up to mapped drives. I have 3 seperate backup operations that run every night, each to remote locations/mapped drives.

 

[MaffewW]

cheers ecobb

I know that you can backup to UNC paths \\server\share though I've just tried a backup to a mapped network drive R on my machine (mapped to a server) and I get a message about device being off line or not available and from what I have read you cant do so. maybe you can in SQL2k (we're on 7 mostly) or perhaps you need to add it as a dump device or something

Matt

Brighton, UK

 

[ecobb]

You're right MaffewW, I looked at it and I was backing up to a \\server\share path instead of an actual mapped drive. I don't know why I was thinking it was a mapped drive.

Sorry.

 

[Corran007]

Hmm, thats funny i know ive done it before. O i just reread. yes you have to add it as a backup device. I believe that works. I dont know anymore really. Try creating the mapped drive as whoever your sqlagent is run as. Then use em to map the drive to a backup device. That i believe should work. Try it out if you need it

 

[tlbroadbent]

SQL Server and SQL Agent run as services and not within the context of a Windows seaaion. Drives mapped by any user logged into Windows will not be visible to SQL Server or SQL Agent. The mapping is part of the user's profile that is loaded when a user logs on to a Windows session. Services do not work with user profiles. You must use a UNC path instead of a mapped drive letter.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm