Restore latest backup from network

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Restore latest backup from network

Author

Topic

psychotic_savage
Starting Member

25 Posts

Posted - 2010-12-01 : 03:47:22

Scenario: I need to restore the latest backups to a server from a network location. Problem is the backup is appended with a time stamp and that is always changing. Is it possible to restore only the latest file in the directory that meet certain criteria. And to take that further I need to restore multiple db's from the location but only the most recent of each. ie.

Files in folder:
Database1_Backup_yyyy_mm_dd_<unique number>
Database1_Backup_yyyy_mm_dd(+1)_<unique number>
Database1_Backup_yyyy_mm_dd(+2)_<unique number>
Database2_Backup_yyyy_mm_dd_<unique number>
Database2_Backup_yyyy_mm_dd(+1)_<unique number>
Database2_Backup_yyyy_mm_dd(+2)_<unique number>

Files I want to restore:
Database1_Backup_yyyy_mm_dd_<unique number>
Database2_Backup_yyyy_mm_dd_<unique number>

Any ideas on how to do this via T-SQL?
Thanks

Kristen
Test

22859 Posts

Posted - 2010-12-01 : 04:16:18

"Is it possible to restore only the latest file in the directory that meet certain criteria"

Information about the backup history is stored in MSDB system database. If you can query that you can get path / filename information and use that as the basis for building a dynamic SQL Restore command.

otherwise you are stuck with using the limited String & Gum in the Command window, or PowerShell perhaps?

psychotic_savage
Starting Member

25 Posts

Posted - 2010-12-10 : 05:06:52

Hi Just thought I'd drop this in here in case somebody wants to use it :)
I have created this set of scripts to copy the latest backups in a folder and restore them to a different server.
The script then calls a job to perform an integrity check on all attached DB's on that server.
This comprises of 2 bat files and 2 SQL query files. The SQL_Integ_Proc.bat will call all the other scripts.
I have included notifications to get sent to an operator in the integrity check part.

SQL_Integ_Proc.bat
------------------
goto getdate
:work
set origdir=%CD%
pushd \\<Server to get backups from>\<Backup Location> && (
forfiles /D %Date% /m "*.bak" /c "cmd.exe /c %origdir%\SQL_Integ_Procone.bat @path"
) & popd

##Replicate the Below for all servers you want to pull backups from and remove ##
##set origdir=%CD%
##pushd \\<Server to get backups from>\<Backup Location> && (
##forfiles /D %Date% /m "*.bak" /c "cmd.exe /c %origdir%\SQL_Integ_Procone.bat @path"
##) & popd

sqlcmd -U <Username> -P <Password> -S VSP-SQL05 -i %origdir%\SQL_Integ_Run_Job.sql
goto end

:getdate
@echo off
set days=1
rem Calculating Dates yyyy/mm/dd Format
setlocal
echo %date% > ~Date.txt
set WDays=WedThuFriSatSunMonTue
for /f "tokens=1-4 delims=/ " %%a in (~Date.txt) do (
set dd=%%c
set mm=%%b
set yy=%%a
)
if %dd% LSS 10 set dd=%dd:~1%
if %mm% LSS 10 set mm=%mm:~1%

set mo=%mm%
set yr=%yy%
if %mm% LSS 3 (
set /a mo=%mo%+12
set /a yr=%yr%-1
)
set /a mo=%mo%+1
set /a a=%yr%/100
set /a b=2-%a%+%a%/4
set /a jd=%yr%*36525/100+%mo%*306001/10000+%dd%+%b%-694084

if %dd% LSS 10 set dd=0%dd%
if %mm% LSS 10 set mm=0%mm%

set /a Pos=(%jd%%%7)*3
set WDay=!WDays:~%Pos%,3!
set /a jd=%jd%-1
set /a yy=%jd%*100/36525
set /a dd=%jd%-%yy%*36525/100
set /a mm=%dd%*10/306
set /a dd=%dd%-(%mm%*306+5)/10
if %dd%==0 (
set dd=31
set /a mm=%mm%-1
if %mm%==0 set dd=29
)
set /a mm=%mm%+3
if %mm% GTR 12 (
set /a mm=%mm%-12
set /a yy=%yy%+1
)
set /a yy=%yy%+1900
if %dd% LSS 10 set dd=0%dd%
if %mm% LSS 10 set mm=0%mm%
rem echo Today=%WDay% %1
set /a Pos=(%jd%%%7)*3
set WDay=!WDays:~%Pos%,3!
set CDate=%WDay% %dd%/%mm%/%yy%
set NDate=%yy%/%mm%/%dd%
del ~Date.txt
setlocal
@echo on
goto work

:end
--------------------------------------------------------------------------------------------------------------

SQL_Integ_Procone.bat
---------------------
@echo "---------------------------"
@echo Processing %1
@echo do file copy
Copy %1 D:\Data\Backup\SQL_Integ\TempDB.bak
sqlcmd -v source=%1 -U <Username> -P <Password> -S VSP-SQL05 -i %origdir%\SQL_Integ_Process.sql
Del D:\Data\Backup\SQL_Integ\tempdb.bak
@echo do file delete
--------------------------------------------------------------------------------------------------------------

SQL_Integ_Process.sql
---------------------
Print 'source = $(Source)'

declare @DBName as nvarchar(100)
declare @DBName1 as nvarchar(100)
Declare @Location as nvarchar(100)

Set @Location = '$(Source)'
set @DBName1 = LEFT(@Location, LEN(@Location) - 30)
set @DBName = RIGHT(@DBName1, LEN(@DBName1) - 13)

Print 'Location = ' + @Location
Print 'DBName1 = ' + @DBName1
Print 'DBName = ' + @DBName

Restore Database @DBName
From disk = 'D:\Data\Backup\SQL_Integ\tempdb.bak'
WITH Recovery

Set @DBName = ''
Set @DBName1 = ''
set @Location = ''
--------------------------------------------------------------------------------------------------------------

SQL_Integ_Run_Job.sql
---------------------
exec msdb.dbo.sp_start_job 'Integrity_Check'
--------------------------------------------------------------------------------------------------------------

Create a Job called 'Integrity_Check'
-------------------------------------
-- Declarations.
DECLARE @db as sysname;

DECLARE @command nvarchar(1000);
DECLARE @DropCommand nvarchar(1000);

DECLARE @errormessage as nvarchar(4000);
DECLARE @notification as nvarchar(4000);

-- Initiate the compounding variables.
set @errormessage = ''
SET @notification = ''

-- Declare the cursor for the list of Databases to be used.
DECLARE DatabaseList CURSOR FAST_FORWARD FOR
SELECT [name] FROM sys.databases
WHERE [name] NOT IN ('master','model','msdb','tempdb')
AND state_desc = 'ONLINE'
ORDER BY [name]

-- Open DatabaseList Cursor.
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @db
WHILE (@@fetch_status = 0)
BEGIN
PRINT N' ';
PRINT N'Database: ' + @db;

Begin try
SET @command = N'USE ' + @db + N'; DBCC CheckDB';
EXEC (@command);
PRINT N'Executed: ' + @command;
SET @notification = @notification + @db + ' completed / ';
Set @DropCommand = N'Drop Database ' + @db;
EXEC (@DropCommand)
end try
BEGIN CATCH
SELECT @errormessage = @errormessage +cast(ERROR_MESSAGE() as nvarchar(1000)) +' on ' +@db
END CATCH

-- Declare the cursor for the list of fragmented indexes to be processed.
FETCH NEXT FROM DatabaseList INTO @db
END
-- Close and deallocate the DatabaseList cursor.
CLOSE DatabaseList;
DEALLOCATE DatabaseList;

-- Populate and send the notification Email Success\failure.
set @notification = 'There where no Issuses. The following where Database Integrities where checked: ' + @notification
If @errormessage = ''
EXECUTE msdb.dbo.sp_notify_operator @name=N'SQLOperator',@subject=N'Integrity Check completed successfully',@body = @notification
else
EXECUTE msdb.dbo.sp_notify_operator @name=N'SQLOperator',@subject=N'Integrity Check failed',@body = @errormessage
-- End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources