if exists (select * from sysobjects where id = object_id('dbo.usp_dump_userdb') and sysstat & 0xf = 4)
	drop procedure dbo.usp_dump_userdb
GO

/*
    usp_dump_userdb - dumps all neccesary databases to @devicename
    version May 7, 1998 Wilfred van Dijk
    email: wilfred_van_dijk@nai.com
*/

CREATE	PROCEDURE usp_dump_userdb AS

SET NOCOUNT ON

DECLARE @devicename VARCHAR(35)
DECLARE	@naam VARCHAR(35)
DECLARE	@init INT
DECLARE @retrieved INT

SELECT	@devicename = name from sysdevices where cntrltype = 5
SELECT	@retrieved = @@ROWCOUNT

IF 	@devicename is NULL -- check exists
BEGIN
	RAISERROR('No tapedevice found',16,-1)
	RETURN 1
END

IF @retrieved > 1 -- works only with 1 tapedevice
BEGIN
	RAISERROR('Multiple tapedevices found',16,-1)
	RETURN 1
END

DECLARE Get_UserDB CURSOR FOR
	SELECT	name
	FROM	sysdatabases
	WHERE	name NOT IN ('pubs','tempdb')

SELECT	@init = 1
OPEN	Get_UserDB
FETCH	NEXT FROM Get_UserDB INTO @naam
WHILE	@@FETCH_STATUS = 0
	BEGIN
		IF @init = 1 -- erase tape
		BEGIN
			DUMP DATABASE @naam TO @devicename WITH INIT, NOUNLOAD, RETAINDAYS = 1
			SELECT @init = 0
		END
		ELSE
			DUMP DATABASE @naam TO @devicename WITH NOINIT, NOUNLOAD

		FETCH	NEXT FROM Get_UserDB INTO @naam
	END

CLOSE	Get_UserDB
DEALLOCATE Get_UserDB
SET NOCOUNT OFF

GO