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

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 = "/dba/backup/sybbackup/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 /dba/backup/sybbackup/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

Comments

  1. technobytz says:

    Is this is possible for postgres DB ?

    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 *

*
*

Facebook login by WP-FB-AutoConnect