Backup to ZIP Drive

[Mark H.]

Is their any way to use a zip drive to backup SQL 7? When I say backup to
Device or File SQL only sees local hard drives.

[Tibor Karaszi]

Mark,

You can probably add a backup device pointing to the zip drive, but you
probably have to add is using TSQL.

Check out sp_addumpdevice.

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.

Mark H. <Ma...@bwacc.org> wrote in message
news:uINaEIAT$GA.62@cppssbbsa04...

[Neil Pike]

Mark,

> Is their any way to use a zip drive to backup SQL 7? When I say backup to
> Device or File SQL only sees local hard drives.

Q. Why can't I backup/restore my SQL Server database to JAZ/ZIP drive from SQL
EM?
(v1.0 1999.12.12)

A. The reason is that the SQL Enterprise Manager only lists drives that NT
tells it are permanently attached. JAZ drives, ZIP drives, writeable CD-Roms
etc. are all removeable media and so they aren't listed.

However, as long as there is formatted media in the drive, then all the
MSSQLSERVER service does is issue standard NT i/o calls so is happy to write to
it. You just need to use the underlying TSQL commands - issued from ISQLW etc.
- to do the job. i.e. BACKUP DATABASE <xyz> TO DISK = 'J:\.....'

Neil Pike MVP/MCSE. Protech Computing Ltd
(Please reply only to newsgroups)
SQL FAQ (386 entries) see
sqlfaq.zip in lib 7 (SQL Public) @ http://go.compuserve.com/sqlserver
or www.ntfaq.com/sql.html (+ ntfaq download)
or http://www.sql-server.co.uk

[stef ten bras]

Maybe this is an option to you.

It is not a backup, but a complete copy of your database.

First bring the database off-line. with sp_detach
Make a copy the file(s) which contain the database to the zip drive
using normal NT procedures.
Bring the database online again.

This is a fairly usefull method for small databases, with the added
advantage that you can use the zip drive as a transportation medium
to other servers (, with the same settings).
Or you can make duplicates of the database in this way.

If your database has to stay online, this is not the solution.

Example code for detach and attach :

------------------------------------- 1 instructions for detaching :
EXEC sp_detach_db 'your_database', 'true'

------------------------------------- 2 Use file copy to get the file(s) to
ZIP

------------------------------------- 3 instructions for attaching (one
file only) :
Exec sp_attach_single_file_db @dbname = 'your_database',
@physname = 'c:\mssql7\data\your_database_data.MDF'

------------------------------------- 4 instructions for attaching (multy
files) :
EXEC sp_attach_db @dbname = N'your_database',
@filename1 = N'c:\mssql7\data\your_database_data.mdf',
@filename2 = N'c:\mssql7\data\your_database_log.ldf'

Mark H. wrote in message ...