Database

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- Restore a backed up database
-- Given: the filename and path where the backedup database file is located and the name of the database to restore it as
-- The entry will be restored and a row placed into oDirect.dbo.tbl_dbref - which keeps track of the databases
-- The users for the database must also be restored!
ALTER PROCEDURE [dbo].[sp_RestoreDatabase] 

	@dbname char(32), 				-- the database name to restore as
	@filename char(64), @path char(256)		-- the location of the backuped up database file	(on the SQL Server)

AS

set nocount on

declare @sql nvarchar(3000)

execute('sp_ClearDatabaseConnections ' + @dbname)

-- Restore the database
select @sql = 			' RESTORE DATABASE ' + ltrim(rtrim( @dbname )) + ' FROM DISK = ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @filename )) + ''' ' 
select @sql = ltrim(rtrim(@sql)) + 	'    WITH RECOVERY, '
select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + 'TNGoedit_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , '	-- logical file name to physical name
select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + 'TNGoedit_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' '	-- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + ltrim(rtrim(@dbname)) + '_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , '	-- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + ltrim(rtrim(@dbname)) + '_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' '	-- logical file name to physical name
print @sql
execute ( @sql )

-- Was the command successful or was there a problem
if ( (select @@Error) = 0 ) begin
	-- Put an entry into oDirect.dbo.tbl_dbRef
	-- execute ( 'sp_DataSet_Save ''' + @xml + ''' ' ) 	

	-- TODO: restore the users

  select 'Restore Successful' [Result]
end
else begin
   select 'Restore Unsuccessful' [Result]
end


/*	-- Example of a restore
RESTORE FILELISTONLY
   FROM DISK = 'C:\Inetpub\wwwroot\oBooking\Databases\oBookingMasterDB' 

RESTORE DATABASE tst
   FROM DISK = 'C:\Inetpub\wwwroot\oBooking\Databases\oBookingMasterDB' 
   WITH RECOVERY,
   MOVE 'LBS_Data' TO 'C:\Inetpub\wwwroot\oBooking\Databases\tst.mdf',	-- logical file name to physical name
   MOVE 'LBS_Log' TO 'C:\Inetpub\wwwroot\oBooking\Databases\tst_log.ldf'	-- logical file name to physical name
*/

Exception caught in: ExecuteStoredProc: Could not continue scan with NOLOCK due to data movement. RESTORE could not start database 'Testv3'. RESTORE DATABASE is terminating abnormally. Database 'Testv3' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

SQL> SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'MyUser';

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'MyUser';

no rows selected

SQL> SELECT PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'MyUser';

no rows selected