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
*/