backing up SQL Server 2000 database to named pipe

would appreciate if you could comment on how to backup sql server 2000
database to named pipe
Thank you for your help beforehand,
Miron.I believe you must create a logical dump device of the type "Named Pipe" and
then issue the backup to that device. See sp_addumpdevice in BOL for more
details.
Andrew J. Kelly SQL MVP
"miron" <nospam_berlin.miron@.verizon.net> wrote in message
news:%23tu$B3JOFHA.904@.tk2msftngp13.phx.gbl...
> would appreciate if you could comment on how to backup sql server 2000
> database to named pipe
> Thank you for your help beforehand,
> Miron.
>|||I am short on time. It would be nice to have a working script for database
pubs and a named pipe \\\pipe\mypipe
Thanks,
Miron.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%235J$lIKOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I believe you must create a logical dump device of the type "Named Pipe"
and
> then issue the backup to that device. See sp_addumpdevice in BOL for more
> details.
> --
> Andrew J. Kelly SQL MVP
>
> "miron" <nospam_berlin.miron@.verizon.net> wrote in message
> news:%23tu$B3JOFHA.904@.tk2msftngp13.phx.gbl...
>|||Try the following script to create the device and then backup the PUBS
database to the newly created device:
EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
- Peter Ward
WARDY IT Solutions
"miron" wrote:

> I am short on time. It would be nice to have a working script for database
> pubs and a named pipe \\\pipe\mypipe
> Thanks,
> Miron.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%235J$lIKOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> and
>
>|||Peter, thank you for the script. While using lines I run into issues, some
did resolve, others still need assistance. Here is step by step
1.) create the dump device using the first line of the script
EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
2.) executed
BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
and SQL Server immediately returned an error message:
Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device off-line.
See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
BACKUP DATABASE is terminating abnormally.
3.) modified the dump device as following:
exec sp_dropdevice 'PUBS_PIPE_DUMP'
go
EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\.\pipe\mypipe'
go
4.) tried to execute again
BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
this time osql.exe cursor blinked for a few minutes. Then SQL Server
returned the same error message...
Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device off-line.
See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
BACKUP DATABASE is terminating abnormally.
5.) just to see how much time it takes to backup pubs I run script
1> BACKUP DATABASE pubs TO disk = 'C:\pubs_20050404003334827.bkp'
2> go
Here is SQL Server message returned when completed backup:
Processed 144 pages for database 'pubs', file 'pubs' on file 1.
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
BACKUP DATABASE successfully processed 145 pages in 1.021 seconds (1.157
MB/sec).
and it happened in a mere second. So, it does look like I am close to
succeeding at baking SQL Server 2000 database to a named pipe, yet something
is not letting backup to complete in reasonable time. I guess what really
needed is a working script, that would allow to store backup and then
restore from it using named pipe. I do greatly appreciate any further
assistance.
Miron.
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:5ACAA03C-AC69-47AD-A638-F971557B2D5F@.microsoft.com...
> Try the following script to create the device and then backup the PUBS
> database to the newly created device:
> EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
> BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
>
> - Peter Ward
> WARDY IT Solutions
>
> "miron" wrote:
>
database
Pipe"
more
2000|||"miron" <nospam_berlin.miron@.verizon.net> wrote in message
news:%23NM7zvOOFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Peter, thank you for the script. While using lines I run into issues, some
> did resolve, others still need assistance. Here is step by step
> 1.) create the dump device using the first line of the script
> EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
> 2.) executed
> BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
> and SQL Server immediately returned an error message:
> Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
> Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device
> off-line.
> See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
> BACKUP DATABASE is terminating abnormally.
> 3.) modified the dump device as following:
> exec sp_dropdevice 'PUBS_PIPE_DUMP'
> go
> EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\.\pipe\mypipe'
> go
> 4.) tried to execute again
> BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
> this time osql.exe cursor blinked for a few minutes. Then SQL Server
> returned the same error message...
> Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
> Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device
> off-line.
> See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
> BACKUP DATABASE is terminating abnormally.
> 5.) just to see how much time it takes to backup pubs I run script
> 1> BACKUP DATABASE pubs TO disk = 'C:\pubs_20050404003334827.bkp'
> 2> go
> Here is SQL Server message returned when completed backup:
> Processed 144 pages for database 'pubs', file 'pubs' on file 1.
> Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
> BACKUP DATABASE successfully processed 145 pages in 1.021 seconds (1.157
> MB/sec).
> and it happened in a mere second. So, it does look like I am close to
> succeeding at baking SQL Server 2000 database to a named pipe, yet
> something
> is not letting backup to complete in reasonable time. I guess what really
> needed is a working script, that would allow to store backup and then
> restore from it using named pipe. I do greatly appreciate any further
> assistance.
You need to have something reading or writing data in or out of the named
pipe, within a reasonable amount of time, or the backup/restore will fail.
Simple example using lzop (google for it) to compress backups on-the-fly.
start cmd /C isql /E /Q"backup database <db_name> to pipe = '\\.\pipe\bck'"
sleep 3
lzop -v -o<db_name>.lzo < \\.\pipe\bck
The sleep command just allows a bit of time for the backup to get started
and the pipe to be setup.
To restore the data :-
start cmd /C isql /E /Q"restore database <db_name> from pipe
='\\.\pipe\bck'"
sleep 3
lzop -d -n -v -c <db_name>.lzo > \\.\pipe\bck
You can also use this to make quick copies of databases :-
start isql -E -Q"backup database <db_name> to pipe = '\\.\pipe\bck_o' with
stats = 10"
start isql -E -Q"restore database <db_name_new> from pipe = '\\.\pipe\bck_i'
stats = 10"
sleep 2
cat \\.\pipe\bck_o > \\.\pipe\bck_i
(The restore command will need 'with move' parameters, they are not shown
for clarity)
(copy didn't work when I first tried it, so I used cat, copy /b may work)
Ian.|||Actually you can do it directly
backup database prod to pipe='\\myserver\mypipe'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"miron" <nospam_berlin.miron@.verizon.net> wrote in message
news:%23tu$B3JOFHA.904@.tk2msftngp13.phx.gbl...
> would appreciate if you could comment on how to backup sql server 2000
> database to named pipe
> Thank you for your help beforehand,
> Miron.
>