Backup

[Sejal]

Hello,

I've been using the following script to backup my databases to the tape drive

declare d cursor for select name from sysdatabases where name<>&#39;tempdb&#39; order by crdate, name

declare @db varchar(255), @next_db varchar(255)

open d
fetch next from d into @db
dump database @db to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
fetch next from d into @db
while (@@FETCH_STATUS<>-1) begin
fetch next from d into @next_db
if (@@FETCH_STATUS=-1) break
dump database @db to TAPE_BACKUP with nounload, stats=10, noinit, noskip
select @db=@next_db
end
dump database @db to TAPE_BACKUP with unload, stats=10, noinit, noskip
deallocate d

For couple of days it worked good but then at certain database it gets hung
and there is no way I could kill the process I&#39;ve to stop and start the SQL
server. For the first time I thought may be its something wrong with tape drive but today morning again it stuck to model database and I couldn&#39;t kill the process. Justnow I cann&#39;t stop and start the server how could I kill the process. And can anyone guide me with other script which would help with backing up all database on tape drive.

Thanks.

Sejal

[Sundar]

Have you tried backing up the offending database just by itself? It may be you are having problems with that backup rather than your script.


------------
Sejal at 4/2/01 10:27:37 AM

Hello,

I&#39;ve been using the following script to backup my databases to the tape drive

declare d cursor for select name from sysdatabases where name<>&#39;tempdb&#39; order by crdate, name

declare @db varchar(255), @next_db varchar(255)

open d
fetch next from d into @db
dump database @db to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
fetch next from d into @db
while (@@FETCH_STATUS<>-1) begin
fetch next from d into @next_db
if (@@FETCH_STATUS=-1) break
dump database @db to TAPE_BACKUP with nounload, stats=10, noinit, noskip
select @db=@next_db
end
dump database @db to TAPE_BACKUP with unload, stats=10, noinit, noskip
deallocate d

For couple of days it worked good but then at certain database it gets hung
and there is no way I could kill the process I&#39;ve to stop and start the SQL
server. For the first time I thought may be its something wrong with tape drive but today morning again it stuck to model database and I couldn&#39;t kill the process. Justnow I cann&#39;t stop and start the server how could I kill the process. And can anyone guide me with other script which would help with backing up all database on tape drive.

Thanks.

Sejal

[MAK]

1. I believe its 6.5 sql server.
2. Add @@error to the stored procedure to have better control.
3. Find out the actual error from NT system log and try to fix it.
4. Try to kill the job/task.
5. Try to run the procedure in query analyser/ISQL with @@error, u will know what exactly going on.

-MAK

------------
Sejal at 4/2/01 10:27:37 AM

Hello,

I&#39;ve been using the following script to backup my databases to the tape drive

declare d cursor for select name from sysdatabases where name<>&#39;tempdb&#39; order by crdate, name

declare @db varchar(255), @next_db varchar(255)

open d
fetch next from d into @db
dump database @db to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
fetch next from d into @db
while (@@FETCH_STATUS<>-1) begin
fetch next from d into @next_db
if (@@FETCH_STATUS=-1) break
dump database @db to TAPE_BACKUP with nounload, stats=10, noinit, noskip
select @db=@next_db
end
dump database @db to TAPE_BACKUP with unload, stats=10, noinit, noskip
deallocate d

For couple of days it worked good but then at certain database it gets hung
and there is no way I could kill the process I&#39;ve to stop and start the SQL
server. For the first time I thought may be its something wrong with tape drive but today morning again it stuck to model database and I couldn&#39;t kill the process. Justnow I cann&#39;t stop and start the server how could I kill the process. And can anyone guide me with other script which would help with backing up all database on tape drive.

Thanks.

Sejal

[Sejal]

I&#39;ve tried to kill the job/task but it doesnot get killed
Its sql server 7.0
The NT system Log specifies that tape device has bad block
and i&#39;ve tried to backup model database and all other database
several times on file without cursor it works good, I guess
cursor is giving problem.

Sejal


------------
MAK at 4/2/01 10:49:14 AM

1. I believe its 6.5 sql server.
2. Add @@error to the stored procedure to have better control.
3. Find out the actual error from NT system log and try to fix it.
4. Try to kill the job/task.
5. Try to run the procedure in query analyser/ISQL with @@error, u will know what exactly going on.

-MAK

------------
Sejal at 4/2/01 10:27:37 AM

Hello,

I&#39;ve been using the following script to backup my databases to the tape drive

declare d cursor for select name from sysdatabases where name<>&#39;tempdb&#39; order by crdate, name

declare @db varchar(255), @next_db varchar(255)

open d
fetch next from d into @db
dump database @db to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
fetch next from d into @db
while (@@FETCH_STATUS<>-1) begin
fetch next from d into @next_db
if (@@FETCH_STATUS=-1) break
dump database @db to TAPE_BACKUP with nounload, stats=10, noinit, noskip
select @db=@next_db
end
dump database @db to TAPE_BACKUP with unload, stats=10, noinit, noskip
deallocate d

For couple of days it worked good but then at certain database it gets hung
and there is no way I could kill the process I&#39;ve to stop and start the SQL
server. For the first time I thought may be its something wrong with tape drive but today morning again it stuck to model database and I couldn&#39;t kill the process. Justnow I cann&#39;t stop and start the server how could I kill the process. And can anyone guide me with other script which would help with backing up all database on tape drive.

Thanks.

Sejal

[Sundar]

Sejal,

The script below does what you require without using cursors :

declare @MyCounter int
declare @DB varchar (255)

set nocount on

select name into tempdb..DB2Copy from sysdatabases
where name not in (&#39;tempdb&#39

select @MyCounter = @@rowcount

set rowcount 1
while @MyCounter != 0
begin

select @DB = name from tempdb..DB2Copy
dump database @DB to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
delete tempdb..DB2Copy
select @MyCounter = @MyCounter - 1
end

set rowcount 0
drop table tempdb..DB2Copy



------------
Sejal at 4/2/01 10:27:37 AM

Hello,

I&#39;ve been using the following script to backup my databases to the tape drive

declare d cursor for select name from sysdatabases where name<>&#39;tempdb&#39; order by crdate, name

declare @db varchar(255), @next_db varchar(255)

open d
fetch next from d into @db
dump database @db to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
fetch next from d into @db
while (@@FETCH_STATUS<>-1) begin
fetch next from d into @next_db
if (@@FETCH_STATUS=-1) break
dump database @db to TAPE_BACKUP with nounload, stats=10, noinit, noskip
select @db=@next_db
end
dump database @db to TAPE_BACKUP with unload, stats=10, noinit, noskip
deallocate d

For couple of days it worked good but then at certain database it gets hung
and there is no way I could kill the process I&#39;ve to stop and start the SQL
server. For the first time I thought may be its something wrong with tape drive but today morning again it stuck to model database and I couldn&#39;t kill the process. Justnow I cann&#39;t stop and start the server how could I kill the process. And can anyone guide me with other script which would help with backing up all database on tape drive.

Thanks.

Sejal

[Sejal]

Thanks a Lot Sundar.

------------
Sundar at 4/2/01 12:07:23 PM

Sejal,

The script below does what you require without using cursors :

declare @MyCounter int
declare @DB varchar (255)

set nocount on

select name into tempdb..DB2Copy from sysdatabases
where name not in (&#39;tempdb&#39

select @MyCounter = @@rowcount

set rowcount 1
while @MyCounter != 0
begin

select @DB = name from tempdb..DB2Copy
dump database @DB to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
delete tempdb..DB2Copy
select @MyCounter = @MyCounter - 1
end

set rowcount 0
drop table tempdb..DB2Copy



------------
Sejal at 4/2/01 10:27:37 AM

Hello,

I&#39;ve been using the following script to backup my databases to the tape drive

declare d cursor for select name from sysdatabases where name<>&#39;tempdb&#39; order by crdate, name

declare @db varchar(255), @next_db varchar(255)

open d
fetch next from d into @db
dump database @db to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
fetch next from d into @db
while (@@FETCH_STATUS<>-1) begin
fetch next from d into @next_db
if (@@FETCH_STATUS=-1) break
dump database @db to TAPE_BACKUP with nounload, stats=10, noinit, noskip
select @db=@next_db
end
dump database @db to TAPE_BACKUP with unload, stats=10, noinit, noskip
deallocate d

For couple of days it worked good but then at certain database it gets hung
and there is no way I could kill the process I&#39;ve to stop and start the SQL
server. For the first time I thought may be its something wrong with tape drive but today morning again it stuck to model database and I couldn&#39;t kill the process. Justnow I cann&#39;t stop and start the server how could I kill the process. And can anyone guide me with other script which would help with backing up all database on tape drive.

Thanks.

Sejal

[Sundar]

I never actually tested the actual dumping of the database but it should work. Let me know if you hit any problems. If it works cleanly I may incorporate this into our EOD&#39;s.

Thanks.


------------
Sejal at 4/2/01 12:11:19 PM


Thanks a Lot Sundar.

------------
Sundar at 4/2/01 12:07:23 PM

Sejal,

The script below does what you require without using cursors :

declare @MyCounter int
declare @DB varchar (255)

set nocount on

select name into tempdb..DB2Copy from sysdatabases
where name not in (&#39;tempdb&#39

select @MyCounter = @@rowcount

set rowcount 1
while @MyCounter != 0
begin

select @DB = name from tempdb..DB2Copy
dump database @DB to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
delete tempdb..DB2Copy
select @MyCounter = @MyCounter - 1
end

set rowcount 0
drop table tempdb..DB2Copy



------------
Sejal at 4/2/01 10:27:37 AM

Hello,

I&#39;ve been using the following script to backup my databases to the tape drive

declare d cursor for select name from sysdatabases where name<>&#39;tempdb&#39; order by crdate, name

declare @db varchar(255), @next_db varchar(255)

open d
fetch next from d into @db
dump database @db to TAPE_BACKUP volume=&#39;SS0161&#39; with nounload, stats=10, init, noskip
fetch next from d into @db
while (@@FETCH_STATUS<>-1) begin
fetch next from d into @next_db
if (@@FETCH_STATUS=-1) break
dump database @db to TAPE_BACKUP with nounload, stats=10, noinit, noskip
select @db=@next_db
end
dump database @db to TAPE_BACKUP with unload, stats=10, noinit, noskip
deallocate d

For couple of days it worked good but then at certain database it gets hung
and there is no way I could kill the process I&#39;ve to stop and start the SQL
server. For the first time I thought may be its something wrong with tape drive but today morning again it stuck to model database and I couldn&#39;t kill the process. Justnow I cann&#39;t stop and start the server how could I kill the process. And can anyone guide me with other script which would help with backing up all database on tape drive.

Thanks.

Sejal