Solved

restoring DB from Zip Bak file - automatically

Posted on 2014-04-08
3
826 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 38

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now