MOVE DATABASE

				
--**************************************
-- Name: MOVE DATABASE
-- Description:MOVE DATABASE FILES MDF AND LDF !!!!!
-- By: Jose Vicente Rocamora Ballestero
--
-- Assumes:MOVE DATABASE, ITS FOR YOU. 
1) READ_ONLY
2) BACKUP
3) RESTORE IN THE OTHER PATH
4) READ_WRITE
PLEASE VOTE ME !!!! SEE YOU LATER
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1117&lngWId=5--for details.--**************************************

USE [MASTER]
GO
Declare @bbdd varchar(50)
Declare @cadB nvarchar(2000)
Declare @cadR nvarchar(2000)
Declare @cadRAux nvarchar(2000)
Declare @cont int
Declare @cadReadOnly nvarchar(500)
Declare @cadReadWrite nvarchar(500)
Declare @cadEnd nvarchar(4000)
Set @bbdd='[@_PICO_E0003E0006]'
Set @cadB=''
Set @cadR=''
Set @cadRAux=''
Set @cadB= 'BACKUP LOG ' + @bbdd + ' WITH TRUNCATE_ONLY ' + CHAR(13)
set @cadB= @cadB + N'BACKUP DATABASE ' + @bbdd + ' TO DISK=''e:\program files\backup\' + replace(replace(replace(@bbdd,'[',''),']',''),'@_','') + '.BAK'' ' + CHAR(13)
-- PRIMERA PARTE DE LA CADENA DE RESTAURACION !!!!
--------------------------------------------------------
 Set @cadR= 'RESTORE DATABASE ' + @bbdd + CHAR(13)
 Set @cadR= @cadR + 'FROM DISK = ''e:\program files\backup\' + replace(replace(replace(@bbdd,'[',''),']',''),'@_','') + '.BAK'' ' + CHAR(13)
 Set @cadR= @cadR + 'WITH replace, ' + CHAR(13)
--------------------------------------------------------
--PRINT 'e:\program files\backup\' + replace(replace(replace(@bbdd,'[',''),']',''),'@_','') + '.BAK' 
Set @cont=0
 DECLARE @NOMBRELOG VARCHAR(500) 
 DECLARE logicalName CURSOR FOR 
 SELECT cast(F.NAME as VARCHAR(500)) as name FROM SYSALTFILES F LEFT JOIN SYSDATABASES D ON F.DBID=D.DBID WHERE D.NAME=replace(replace(@bbdd,'[',''),']','')
	OPEN logicalName 
	FETCH NEXT FROM logicalName INTO @NOMBRELOG 
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		--PRINT @NOMBRELOG	
	
		Set @cont=@cont+1	
		if (@cont=1) 			
		BEGIN
			Set @cadRAux= @cadRAux + 'MOVE ''' + RTRIM(LTRIM(@NOMBRELOG)) + ''' TO ''e:\program files\Data\' + replace(replace(replace(@bbdd,'[',''),']',''),'@_','') + '.mdf'', ' + CHAR(13)
	--		Set @cadRAux= @cadRAux + 'ALTER DATABASE ' + @BBDD + '	MODIFY FILE (NAME = ''' + RTRIM(LTRIM(@NOMBRELOG)) + ''', FILENAME = ''e:\program files\data\' + replace(replace(replace(@bbdd,'[',''),']',''),'@_','') + '.mdf'') ' + CHAR(13)
		END
		
		if (@cont=2)
		BEGIN
			Set @cadRAux= @cadRAux + 'MOVE ''' + RTRIM(LTRIM(@NOMBRELOG)) + ''' TO ''e:\program files\Data\' + replace(replace(replace(@bbdd,'[',''),']',''),'@_','') + '.ldf'' '
	--		Set @cadRAux= @cadRAux + 'ALTER DATABASE ' + @BBDD + '	MODIFY FILE (NAME = ''' + RTRIM(LTRIM(@NOMBRELOG)) + ''', FILENAME = ''e:\program files\data\' + replace(replace(replace(@bbdd,'[',''),']',''),'@_','') + '.ldf'') ' + CHAR(13)
		END 		
		
		FETCH NEXT FROM logicalName INTO @NOMBRELOG 
	END
 
CLOSE logicalName 
DEALLOCATE logicalName 
Set @cadR=@cadR + @cadRAux
Set @cadReadOnly='ALTER database ' + @bbdd + ' set READ_ONLY ' + CHAR(13)
Set @cadReadWrite='ALTER database ' + @bbdd + ' set READ_WRITE' + CHAR(13)
Set @cadEnd = 'PRINT '' STARTING !!! '' ' + CHAR(13)
Set @cadEnd = @cadEnd + @cadReadOnly + CHAR(13) + CHAR(13)
Set @cadEnd = @cadEnd + 'PRINT '' STEP 1 READ ONLY '' ' + CHAR(13) + CHAR(13)
Set @cadEnd = @cadEnd + @cadB + CHAR(13) + CHAR(13) 
Set @cadEnd = @cadEnd + 'PRINT '' STEP 2 BACKUP '' ' + CHAR(13) + CHAR(13)
Set @cadEnd = @cadEnd + @cadR + CHAR(13) + CHAR(13) + CHAR(13)
Set @cadEnd = @cadEnd + 'PRINT '' STEP 3 RESTORE '' ' + CHAR(13) + CHAR(13)
Set @cadEnd = @cadEnd + @cadReadWrite + CHAR(13) + CHAR(13)
Set @cadEnd = @cadEnd + 'PRINT '' STEP 4 READ WRITE '' ' + CHAR(13) + CHAR(13)
--Set @cadEnd = @cadEnd + 'COMMIT TRANSACTION ' + CHAR(13)
--PRINT @cadEnd
EXEC (@cadEnd)