Sybase ASE: A simple way to let a user backup a database using a stored procedure

I wrote this simple method to backup a database using a stored procedure. This allows for a user to restore a database with no knowledge of the dump/load commands. Related to Sybase ASE: A simple way to let a user restore a database using a stored procedure

use sybsystemprocs
go

if exists (select 1 from sybsystemprocs..sysobjects where name = "sp_dump_userdb")
	drop procedure sp_dump_userdb
go

CREATE PROCEDURE dbo.sp_dump_userdb
@dbName varchar(100) IN
AS 
BEGIN
	if (@dbName in ('master', 'tempdb', 'tempdb2', 'tempdb3', 'sybsystemprocs', 'sybsystemdb'))
	begin
		print "sp_load_userdb only works with user databases."
	end 
	else
	if exists (select 1 from master..sysdatabases where name = @dbName)
	begin
		DECLARE @DAYofMonth smallint
		DECLARE @dbNamePath varchar(255)
		DECLARE @out varchar(255)

		select @DAYofMonth = datepart(day, getdate())
		select @dbNamePath = "/backups/user_backups/" + @dbName + "_" + convert(varchar(10), @DAYofMonth) + ".dmp"
		select @out = "Backing up database '" + @dbName + "' to " + @dbNamePath
		print @out

		dump database @dbName to @dbNamePath with init,compression=3

		/*  Remove old dump files (older than 2 weeks) */
		exec xp_cmdshell "find /backups/user_backups -mtime +14 -exec rm {} \;"

		print "Backup complete!"
	end 
	else
	begin
		select "'" + @dbName + "' is an unknown database.  Please verify name."
	end 
END
go
exec sp_dump_userdb jf_test
Backing up database 'jf_test' to /some_dir/jf_test_5.dmp
Backup Server session id is: 95. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /some_dir/jf_test_5.dmp.
(1 row affected)
Backup Server: 6.28.1.1: Dumpfile name 'jf_test131560B97B' section number 1 mounted on disk file '/some_dir/jf_test_5.dmp'
Backup Server: 4.188.1.1: Database jf_test: 600 kilobytes (1%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 1134 kilobytes (1%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 18242 kilobytes (5%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 31492 kilobytes (11%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 38072 kilobytes (19%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 38416 kilobytes (36%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 38930 kilobytes (61%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 45854 kilobytes (75%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 46204 kilobytes (92%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database jf_test: 46390 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database jf_test).
 xp_cmdshell                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(0 rows affected)
Backup complete!
(return status = 0)
Share Button

2 Replies to “Sybase ASE: A simple way to let a user backup a database using a stored procedure”

    1. The stored procedure is made specifically for Sybase’s ASE DBMS. You might be able to do something similar with PostreSQL DBMS by granting execute permission on the pg_start_backup stored procedure.

Leave a Reply

Your email address will not be published. Required fields are marked *