Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

609 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