restoring DB from Zip Bak file - automatically

I am using SqlBackUpAndFtp to backup the db to google drive
here is what it looks like on the other server

The backup server is one that isn't always on, but it is set to get the latest DB via google drive so i have a backup of it

I would now like to restore the DB to the backup server
But I would like this done automatically
As you can see, 2 are complete backups, and the rest are logs

I would be fine with just restoring the full backups.

Is this possible? to automatically look at this DIR and restore the latest DB?
Is there a program that will do this, or does anyone have a script or something?
Who is Participating?
DcpKingConnect With a Mentor Commented:
I have a job that does this sort of thing every day! I have to pull down the database backup of our cloud-based sales system from an FTP site and restore it, deleting the previous version first.

Here's the code I use for actually getting the database back from a .bak to an mdf file:

ALTER procedure [dbo].[DBA_RestoreNewDatabase]
	@strBakFile			varchar(256)		=	'G:\ssis\Restores\Input\Sales.bak',
	@strDBFilePrefix	nvarchar(16)		=	N'OM',
	@strDataPath		nvarchar(128)		=	N'F:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\DATA\',
	@strLogPath			nvarchar(128)		=	N'G:\MSSQL\LOGS\'
Mike Irwin
May 10, 2013
20130922	mai	altered error handling in getting the FILELIST listing to reflect failure better

Example command:
exec control.dbo.OMA_RestoreNewDatabase
	set nocount on
	declare @strBackupFileName	varchar(128) = 'G:\ssis\Restores\Input\Sales.bak'
	declare @strDateString				nvarchar(8),
			@strMessage					varchar(120),
			@strError					varchar(120),
			@strWholeErrorMessage		varchar(2000),
			@intLenLeft					int,
			@strCmd						varchar(1000)
	set @strDateString =	cast(year(getdate()) as nvarchar(4)) + 
							right(('0' + cast(month(getdate()) as nvarchar(2))), 2) + 
							right(('0' + cast(day(getdate()) as nvarchar(2))), 2) 
	--	----------------------------------------------------
	--	Here's storage for the catalog in the backup file
	--	----------------------------------------------------
	declare @taTable table (LogicalName varchar(128),
							[PhysicalName] varchar(128), [Type] varchar, 
							[FileGroupName] varchar(128), 
							[Size] varchar(128), 
							[MaxSize] varchar(128), 
		--	----------------------------------------------------
		--	This code finds the logical names of the data and log files, 
		--	fills the table with the catalog, and pulls out the two bits we need
		--	----------------------------------------------------
	declare @strLogicalNameData nvarchar(128), 
			@strLogicalNameLog nvarchar(128)
	--	----------------------------------------------------
	--	Get the list of files from the database backup file
	--	----------------------------------------------------
	begin try
		set @strCmd = '	RESTORE FILELISTONLY FROM DISK=''' + @strBackupFileName + '''   '
		insert into @taTable
	end try
	--	----------------------------------------------------
	--	----------------------------------------------------
	begin catch
		set @strWholeErrorMessage = ERROR_MESSAGE()
		exec [dbo].[DBA_WriteToReportLog] 'DBA_RestoreNewDatabase Failed', @strCmd, 1
		set @intLenLeft = len(@strWholeErrorMessage)
		if @intLenLeft < 120
			set @strError = 'Error: ' + substring(@strWholeErrorMessage, 1, 120)
			exec [dbo].[DBA_WriteToReportLog] 'DBA_RestoreNewDatabase Failed (Check event log)', @strError, 1
		RAISERROR('Database Restore Failed 1', 21, 1) WITH LOG
	end catch
	--	----------------------------------------------------
	set @strLogicalNameData = (SELECT LogicalName FROM @taTable WHERE Type='D')
	set @strLogicalNameLog = (SELECT LogicalName FROM @taTable WHERE Type='L')
	--	----------------------------------------------------
	--	Now create/set up variables with sources and destinations
	--	Note that the destinations are "Sales_" plus today's date-string. Every restore is different.
	--	----------------------------------------------------
	--		Set up the destinations - they're constant except the data parts of the names
	--	----------------------------------------------------
	declare @strDataFile  nvarchar(128) = @strDataPath + @strDBFilePrefix + '_' + @strDateString + N'.mdf'
	declare @strLogFile  nvarchar(128) = @strLogPath + @strDBFilePrefix + '_' + @strDateString + N'_log.ldf'
	--	----------------------------------------------------
	--		Now do the restoration - the database logical file name is always the same
	--	----------------------------------------------------
	set @strMessage = 'Restore of Sales succeeded'
	set @strCmd = ' RESTORE DATABASE [Sales_New] '
	set @strCmd = @strCmd + ' FROM  DISK = @strBackupFileName '
	set @strCmd = @strCmd + ' WITH  FILE = 1,  '
	set @strCmd = @strCmd + ' MOVE ' + @strLogicalNameData + '	TO ' + @strDataFile + ',   '
	set @strCmd = @strCmd + ' MOVE ' + @strLogicalNameLog + '	TO ' + @strLogFile + ',   '
	set @strCmd = @strCmd + ' NOUNLOAD,  STATS = 5 '
	begin try
		FROM  DISK = @strBackupFileName
		WITH  FILE = 1,  
		MOVE @strLogicalNameData	TO @strDataFile,  
		MOVE @strLogicalNameLog	    TO @strLogFile,  
	end try
	begin catch
		set @strError = 'Error: ' + ERROR_MESSAGE()
		set @strMessage = 'Restore of Sales Failed: ' + @strError
		RAISERROR('Database Restore Failed 2', 21, 1) WITH LOG
	end catch
	--	----------------------------------------------------
	--	----------------------------------------------------
	exec [dbo].[DBA_WriteToReportLog] 'DBA_RestoreNewDatabase', @strMessage, 1
	--	----------------------------------------------------

Open in new window

The SP is inside an SSIS job that
waits for the Zip file to arrive on the FTP site (a Python program that watches the site for the file to finish arriving),
runs a program to download the Zip file,
unzips the Zip file to get the BAK file,
restores the BAK file to Sales_new
uses DBCC to check that the incoming database is good
adds new indexes, etc
drops unwanted tables, etc.
Renames the existing version to Sales_old,
renames the new version to be the same as what just was the existing version!
adds users to the restored database

and several other things!

It was the work of a day or so to write almost all of it originally, and it's been honed since. Please ask about anything - it certainly shouldn't be difficult to do what you want with a combination of SSIS, 3rd party programs like WinSCP and 7Zip, and some Python programming.


Aaron TomoskyTechnology ConsultantCommented:
yes a batch file can do this, I don't know if my skills can take this to completion but I can get you started.

Basically dir will get you the right file, figure out a way to unzip it, then sqlcmd will do the restore. You may have to delete the current one and then restore or something like that if you run into issues restoring.

I quickly referenced some batch files I had and a few google searches and got you most of the way there. If you can't figure out the middle unzipping part, let me know.

make a something.bat and put this in (change c:\ to the location and YourDBName to whatever that is):
cd /d c:\
for /f "tokens=*" %%a in ('dir /b /od ') do set newest=%%a
--unzip somehow using %newest%
--get the filename of the unzipped version and set to newestunzipped
SQLCMD -E -S touch -Q "RESTORE DATABASE YourDBName FROM DISK='%newestunzipped%'"
websssAuthor Commented:
awesome thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.