backing up an sql database onto tape 1

[1nterpol]

im looking to temporarily backup an sql database to tape rather to disk.(if not advisable please say)

the database in question has only recently gone live and is only temporary

when testing this on our test server the backup reports sucessful having skipped 1 file, which is of course the database!

if i stop the sql agent on the test server, the backup runs fine, but obviously i wont be able to do this on the live server.

agent for ms sql server is installed on the backup server and so is remote agent on the remote servers.

Any ideas as to what i'm missing?

i dont have enough disk space to do a disk to disk backup on this database, and because we are doing a large upgrade in the near future we dont want to but more disk space

 

[ptheriault]

Add a dump device.
USE master
EXEC sp_addumpdevice 'tape', 'Customer on Tape', '\\.\tape0'

BACKUP DATABASE 'Customer' TO 'Customer on Tape'

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[onpnt]

If I remember correctly the tape drive much physically be connected to the server also. Meaning if you have something like a external tape library it may not work correctly. This is just from memory in reading some documentation. I've never attempted a tape backup with SQL Server without third party tools like Netbackup

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[1nterpol]

it is an external tape drive, and it is connected to a different server

there is no mention of needing the drive to be internal in the backup exec manual

 

[onpnt]

Tape Backup: When you back up databases directly to a tape, the tape drive must be attached locally to the SQL Server. Backing up to remote tape devices is not supported. However, you can have multiple tape devices attached to a single SQL Server. These tapes can then be moved to another location for off-site storage.

SQL Server administration best practices

I'm finding the section I recall in my Admin books now and will post it back sense that is a MS publication and the line above is not

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[1nterpol]

that would be great thanks

 

[SQLSister]

What we do is backup to disk and then the tape drive backs up the backup file that is already on the disk. If you are trying to use the tape backup vice SQL server backup, it could be that threason it skips the file is that database files that are attched are alawys considered open by the file system and can;t be copied or moved or anything else.

Questions about posting. See faq183-874