CREATE PROC sp_backup_user_dbs3
AS
SET nocount ON
DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @cmd SYSNAME  -- stores the dynamically created DOS command
DECLARE @Result INT   -- stores the result of the dir DOS command
DECLARE @RowCnt INT   -- stores @@ROWCOUNT
DECLARE @DBName SYSNAME
DECLARE @filename                 VARCHAR(200) -- stores the path and file name of the BAK file
DECLARE @loglogical               VARCHAR(1000)
DECLARE @datalogical              VARCHAR(1000)
DECLARE @restoreData              VARCHAR(255)
DECLARE @restoreLog               VARCHAR(255)
DECLARE @backupFile               VARCHAR(255)
DECLARE @physicalNameData         VARCHAR(255)
DECLARE @physicalNameLog          VARCHAR(255)
DECLARE @physicalNameDataStripped VARCHAR(255)
DECLARE @physicalNameLogStripped  VARCHAR(255)
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL           VARCHAR(1000)
DECLARE @restoreToDataDir VARCHAR(255)
DECLARE @restoreToLogDir  VARCHAR(255)
DECLARE @path             VARCHAR(100)
SET @path = 'I:\backupMoveTo14'

--we need to delete all the old backup files from the I:\backupMoveTo14 folder
-- Build the del command
SELECT @cmd = 'del ' + @path + '*.BAK' + ' /Q /F'
--PRINT @cmd
EXEC master..xp_cmdshell @cmd,
NO_OUTPUT

CREATE TABLE #whichdatabase
        (
                dbname SYSNAME NOT NULL
        )

INSERT
INTO    #whichdatabase
        (
                dbname
        )
SELECT [name]
FROM    master.dbo.sysdatabases
WHERE [name] IN ( 'db1', 'db2')
ORDER BY [name]
-- Get the database to be backed up
SELECT  TOP 1 @DBName = dbname
FROM    #whichdatabase
        SET @RowCnt = @@ROWCOUNT
        -- Iterate throught the temp table until no more databases need to be backed up
        WHILE @RowCnt <> 0 BEGIN SELECT @filename = @Path + '\' + @DBName + '.BAK' BEGIN backup log @dbname
WITH truncate_only
END

-- Backup the database
BACKUP database @DBName TO disk = @filename

DELETE
FROM    #whichdatabase
WHERE   dbname = @DBName

-- Get the database to be backed up
SELECT  TOP 1 @DBName = dbname
FROM    #whichdatabase
        SET @RowCnt = @@ROWCOUNT
        -- Let the system rest for 5 seconds before starting on the next backup
        WAITFOR delay '00:00:05'
END

DROP TABLE #whichdatabase
        SET nocount OFF BEGIN
        SET @cmd = ''
        SET @cmd = 'xcopy I:\backupMoveTo14\*.BAK \\RemoteServer\ /C /Y' EXEC master.dbo.xp_cmdshell @cmd
END BEGIN

EXEC [RemoteServer].master..usp_restoreDbsFromDir2
END
RETURN 0 GO

CREATE  PROCEDURE usp_restoreDbsFromDir2

AS

SET NOCOUNT ON


DECLARE @dbname varchar(255)
DECLARE @loglogical varchar(1000)
DECLARE @datalogical varchar(1000)
DECLARE @physicalName varchar(255)
DECLARE @physicalFileName varchar(255)
DECLARE @restoreData varchar(255)
DECLARE @restoreLog varchar(255)
DECLARE @backupDisk nvarchar (255)
DECLARE @physicalNameData varchar(255)
DECLARE @physicalNameLog varchar(255)
DECLARE @physicalNameDataStripped varchar(255)
DECLARE @physicalNameLogStripped nvarchar (255)
DECLARE @rowCnt int             -- @@ROWCOUNT
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL varchar(1000)
DECLARE @spidstr varchar(8000)
DECLARE @cmd sysname
DECLARE @bkpFile nvarchar(1000)
DECLARE @sql nvarchar(4000)
DECLARE @restoreDir varchar(255)
DECLARE @PhysicalDataPath varchar(255)
DECLARE @PhysicalLogPath varchar(255)


SET @restoreDir = 'F:\MSSQL\BACKUP\'

-- Get files sorted by date
SET @cmd = 'dir ' + @restoreDir + '*.BAK /OD'

CREATE TABLE #Dir
	(DirInfo VARCHAR(7000)
	) -- Stores the dir results
CREATE TABLE #BackupFiles
	(BackupDate varchar(10), 
	BackupFileName nvarchar(1000)
	) -- Stores only the data we want from the dir
CREATE TABLE #RestoreFileListOnly
	(
	 LogicalName nvarchar(128),
	 PhysicalName nvarchar(260),
	 Type char(1),
	 FileGroupName nvarchar(128),
	 [Size] numeric(20,0),
	 [MaxSize] numeric(20,0)
	)

INSERT INTO #Dir
EXEC master.dbo.xp_cmdshell @cmd

INSERT INTO #BackupFiles
SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #Dir
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'


	-- Get the newest file
	SELECT TOP 1 @bkpFile = BackupFileName
	FROM #BackupFiles
	ORDER BY BackupDate DESC

	SET @rowCnt = @@ROWCOUNT

	-- Iterate throught the table until no more databases need to be backed up
	WHILE @RowCnt <> 0
	BEGIN

	SET @cmd = @restoreDir + @bkpFile

	INSERT INTO #RestoreFileListOnly
	EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')

	--get the dbname from the bkpFile name
	--SET @strSQL = CHARINDEX('_db_', @bkpFile)
	--SET @dbname = LEFT(@bkpFile, @strSQL - 1)
	
	SET @strSQL = CHARINDEX('.bak', @bkpFile)
	SET @dbname = LEFT(@bkpFile, @strSQL - 1)
	--PRINT @dbname
	--IF @@ROWCOUNT <> 2
	-- RETURN 3
	
	SET @backupDisk = @restoreDir + @bkpFile
	SELECT @datalogical = LogicalName
	FROM #RestoreFileListOnly
	WHERE Type = 'D'
	SELECT @loglogical = LogicalName
	FROM #RestoreFileListOnly
	WHERE Type = 'L'
	SELECT @PhysicalDataPath = PhysicalName
	FROM #RestoreFileListOnly
	WHERE Type = 'D'
	SELECT @PhysicalLogPath = PhysicalName
	FROM #RestoreFileListOnly
	WHERE Type = 'L'

	
	SELECT @strSQL = 'alter database ' + @dbname + ' set offline with rollback immediate'
	--alter database MyDatabase set offline with rollback immediate
	--PRINT @strSQL
	EXEC (@strSQL)	
	 
	SELECT @strSQL = 'DROP database ' + @dbname 
	--alter database MyDatabase set offline with rollback immediate
	--PRINT @strSQL
	EXEC (@strSQL)	
						
	--restore the database
	SELECT @strSQL = ''
	SELECT @strSQL = @strSQL + 'RESTORE DATABASE ' + @dbname  + CHAR(10)
	SELECT @strSQL = @strSQL + 'FROM DISK = ''' + @backupDisk + '''' + CHAR(10)
	SELECT @strSQL = @strSQL + 'WITH' + CHAR(10)
	SELECT @strSQL = @strSQL + CHAR(9) + 'REPLACE'
	SELECT @strSQL = @strSQL + ',' + CHAR(10)
	SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE '''+ @datalogical + ''''+ ' TO '''+ @PhysicalDataPath + ''''
	SELECT @strSQL = @strSQL + ',' + CHAR(10)
	SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE ''' + @loglogical + '''' + ' TO '''+ @PhysicalLogPath + ''''
	--PRINT @strSQL
	EXEC (@strSQL)							
	

	SELECT @strSQL = 'alter database ' + @dbname + ' set online with rollback immediate'
	--alter database MyDatabase set offline with rollback immediate
	--PRINT @strSQL
	EXEC (@strSQL)	
	
	BEGIN
	-- Build the del command
	SELECT @cmd = 'del ' + @restoreDir + '\' + @bkpFile + ' /Q /F'
    --PRINT @cmd          
    -- Delete the file
    EXEC master..xp_cmdshell @cmd, NO_OUTPUT
	
	END
	
--This is supposed to remove the row once done
DELETE FROM #BackupFiles
WHERE @bkpFile = BackupFileName

-- Get the database to be backed up
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC

SET @rowCnt = @@ROWCOUNT

--Wait a couple of seconds before starting the next one
WAITFOR delay '00:00:30'

END 

Drop TABLE #Dir
Drop TABLE #BackupFiles
Drop TABLE #RestoreFileListOnly

SET NOCOUNT OFF

RETURN 0

GO