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

Sybase ASE: A simple way to let users restore a database using a stored procedure

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

use sybsystemprocs
go

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

CREATE PROCEDURE dbo.sp_load_userdb
@dbName varchar(100) = NULL, 
@DAYofMonth smallint = NULL
AS 
BEGIN
	if (@dbName is NULL or not exists select 1 from (master..sysdatabases where name = @dbName))
	begin
		print "Please specify a database and day of month to restore from."
		print "sp_load_userdb d1folio1, 15"
	end
	else
	if (@dbName in ('master', 'tempdb', 'tempdb2', 'tempdb3', 'sybsystemprocs', 'sybsystemdb'))
	begin
		print "sp_load_userdb only works with user databases."
	end 
	else
	if (@DAYofMonth is NULL or @DAYofMonth < 1 or @DAYofMonth > 31)
	begin
		DECLARE @cmd varchar(255)

		print "Dump file not found!"
		select @cmd = "find /some_dir/user_backups -name " + @dbName + "_*" + " -type f -exec basename {} \;"
		print @cmd
		exec xp_cmdshell @cmd
	end
	else
	begin
		if (db_name() = "master")
		begin
			DECLARE @dbNamePath varchar(255)

			exec kill_user_connections @dbName
			select @dbNamePath = "/some_dir/user_backups/" + @dbName + "_" + convert(varchar(10), @DAYofMonth) + ".dmp"
			load database @dbName from @dbNamePath
			online database @dbName
		end 
		else
			print "Please run sp_load_userdb from the master database."
	end 
END
go
exec sp_load_userdb jf_test, 5
-----------------------------------------------------------------------
There is no user connections in database jf_test

------------------------------------------------------------------------------------
Please log out if you currently connected to database jf_test
Backup Server session id is: 68. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'jf_test131560B97B' section number 1 mounted on disk file '/somedir/jf_test_5.dmp'

(1 row affected)
Backup Server: 4.188.1.1: Database jf_test: 25480 kilobytes (4%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 53770 kilobytes (10%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 98316 kilobytes (18%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 141328 kilobytes (27%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 184848 kilobytes (35%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 227860 kilobytes (43%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 271380 kilobytes (52%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 314392 kilobytes (60%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 357912 kilobytes (69%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 387100 kilobytes (74%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 430620 kilobytes (83%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 473120 kilobytes (91%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 516640 kilobytes (99%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 518178 kilobytes (100%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 518186 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database jf_test).
Caution:  You have set up this database to include space on disk 36 for both data and the transaction log.  This can make recovery impossible if that disk fails.
Started estimating recovery log boundaries for database 'jf_test'.
Database 'jf_test', checkpoint=(224579, 10), first=(224579, 10), last=(224579, 11).
Completed estimating recovery log boundaries for database 'jf_test'.
Started ANALYSIS pass for database 'jf_test'.
Completed ANALYSIS pass for database 'jf_test'.
Started REDO pass for database 'jf_test'. The total number of log records to process is 2.
Completed REDO pass for database 'jf_test'.
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
Started estimating recovery log boundaries for database 'jf_test'.
Database 'jf_test', checkpoint=(224579, 10), first=(224579, 10), last=(224579, 11).
Completed estimating recovery log boundaries for database 'jf_test'.
Started ANALYSIS pass for database 'jf_test'.
Completed ANALYSIS pass for database 'jf_test'.
Recovery of database 'jf_test' will undo incomplete nested top actions.
Database 'jf_test' is now online.
(return status = 0)
exec sp_load_userdb
Please specify a database and day of month to restore from.
sp_load_userdb d1folio1, 15
(return status = 0)
Share Button

Leave a Reply

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

*
*

Facebook login by WP-FB-AutoConnect