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
Solved

restoring DB from Zip Bak file - automatically

Posted on 2014-04-08
3
877 Views
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

Capture.JPG
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?
0
Comment
Question by:websss
3 Comments
 
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%'"
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 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\'
)
as
/*
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
*/
Begin
	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), 
							[FileId]varchar(128), 
							[CreateLSN]varchar(128), 
							[DropLSN]varchar(128), 
							[UniqueId]varchar(128), 
							[ReadOnlyLSN]varchar(128), 
							[ReadWriteLSN]varchar(128), 
							[BackupSizeInBytes]varchar(128), 
							[SourceBlockSize]varchar(128), 
							[FileGroupId]varchar(128), 
							[LogGroupGUID]varchar(128), 
							[DifferentialBaseLSN]varchar(128), 
							[DifferentialBaseGUID]varchar(128), 
							[IsReadOnly]varchar(128), 
							[IsPresent]varchar(128), 
							[TDEThumbprint]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
		exec(@strCmd)
	end try
	--	----------------------------------------------------
	--	----------------------------------------------------
	begin catch
		set @strWholeErrorMessage = ERROR_MESSAGE()
		exec [dbo].[DBA_WriteToReportLog] 'DBA_RestoreNewDatabase Failed', @strCmd, 1
		set @intLenLeft = len(@strWholeErrorMessage)
		if @intLenLeft < 120
		begin
			set @strError = 'Error: ' + substring(@strWholeErrorMessage, 1, 120)
			exec [dbo].[DBA_WriteToReportLog] 'DBA_RestoreNewDatabase Failed (Check event log)', @strError, 1
		end
		RAISERROR('Database Restore Failed 1', 21, 1) WITH LOG
		return
	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
		exec(@strCmd)
/*
		RESTORE DATABASE [Sales_New]
		FROM  DISK = @strBackupFileName
		WITH  FILE = 1,  
		MOVE @strLogicalNameData	TO @strDataFile,  
		MOVE @strLogicalNameLog	    TO @strLogFile,  
		NOUNLOAD,  STATS = 5
*/
	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
		return
	end catch
	--	----------------------------------------------------
	--
	--	----------------------------------------------------
	exec [dbo].[DBA_WriteToReportLog] 'DBA_RestoreNewDatabase', @strMessage, 1
	--	----------------------------------------------------
End

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.

hth

Mike
0
 

Author Closing Comment

by:websss
ID: 40041493
awesome thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

807 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