restoring DB from Zip Bak file - automatically

Posted on 2014-04-08
Medium Priority
Last Modified: 2016-02-10
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?
Question by:websss
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 39987290
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%'"
LVL 16

Accepted Solution

DcpKing earned 2000 total points
ID: 39987298
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.



Author Closing Comment

ID: 40041493
awesome thanks

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question