July 20, 2005 at 10:18 am
I'm trying to implement cheap 'log shipping'; we've only got Standard, upgrading to Enterprise is prohibitively expensive unfortunately.
To do this I've set up a Maintenance Plan that runs Optimisations, Integrity checks and then a Complete Backup at night and Transaction Logs every 15 minutes during the day, and also removes old files. The backup path is on our Development box. So now all I need to do is schedule a restore of the complete DB - I'm not too worried about the Transaction Logs as 1 days loss is acceptable. The trouble I have is that the Maintenance plan appends the datetime to the file names so I can't give the RESTORE job the actual filename, it wont accept wildcards or the LIKE keyword.
So how do I schedule my restore?
Here's the T-SQL:
RESTORE DATABASE LIVE FROM DISK='\\Server2\d$\SQL1 Data Backup\LIVE.bak'
WITH MOVE 'LIVE' TO 'D:\Database Data\Backup.mdf', REPLACE, STATS=2, NORECOVERY
The filename for the backup would be something like LIVE_db_200520052200.bak but changes each day
TIA
July 21, 2005 at 6:54 am
Within a new SQL Agent Job, create the actual backup statement naming the backup, nothing should be appended to it then.
July 21, 2005 at 6:59 am
Hi Steven,
Thanks for the reply.
I've created a backup statement, outside of the MP, but the problem I have with that solution is that it doesn't delete the old backup files and space is an issue. Any ideas how to delete files older than a day in that?
July 21, 2005 at 12:01 pm
I have a stored proceedure for deleting old backups. You will have to adjust the naming convention to work with maintenance plan standards. You might also be able to use some of the code for running your restores as well.
Steve
create procedure usp_Del_Backup
@vcDbName varchar(128),
@iKeep integer = 2
as
------------------------------------------------------
-- Delete old Backups for a Database
--
-- Parameters:
-- vcDbName Database Name
-- iKeep Number of Full Backups to Keep
------------------------------------------------------
Declare @vcStmt varchar(500)
DECLARE @vcFileName varchar(100)
DECLARE @vcDir varchar(255)
DECLARE @iCnt integer
DECLARE @iLen integer
DECLARE @cDateTime char(12)
DECLARE @errCode integer
-- Get the Directory for the Backups from master
-- (If Data and Backup in same directory structure this works)
select @vcDir = left(filename,charindex('\Data\',filename))
from master.dbo.sysfiles where [filename] like '%.MDF %'
SET @vcDir = @vcDir + 'BACKUP\' + @vcDbName
-- Get list of Files from Backup directory and put into
-- a Temporary table
SET @vcStmt = 'master.dbo.xp_cmdshell "dir ""'+ @vcDir + '"" /b"'
CREATE TABLE #temp_table
(fil_nam varchar(100) NULL)
INSERT #temp_table (fil_nam)
exec (@vcStmt)
SELECT @errCode = @@error
IF @errCode <> 0
return (@errCode)
-- Find the oldest Full Backup that you want to Keep.
-- The filenames are DbName_Full_YyyyMmDdHhMm.FUL
-- Sorting by Descending order gets most recent ones first
DECLARE File_cur CURSOR FOR
SELECT fil_nam FROM #temp_table
WHERE fil_nam like '%.FUL'
ORDER BY fil_nam DESC
SELECT @errCode = @@error
IF @errCode <> 0
return (@errCode)
SET @iCnt = 0
OPEN File_cur
SELECT @errCode = @@error
IF @errCode <> 0
begin
DEALLOCATE File_cur
return (@errCode)
end
FETCH NEXT FROM File_cur INTO @vcFileName
SELECT @errCode = @@error
IF @errCode <> 0
begin
CLOSE File_cur
DEALLOCATE File_cur
return (@errCode)
end
WHILE @@fetch_status <> -1 and @iCnt < @iKeep
BEGIN
SET @iCnt = @iCnt + 1
if @iCnt < @iKeep
FETCH NEXT FROM File_cur INTO @vcFileName
SELECT @errCode = @@error
IF @errCode <> 0
begin
CLOSE File_cur
DEALLOCATE File_cur
return (@errCode)
end
END
CLOSE File_cur
DEALLOCATE File_cur
-- Extract the YyyyMmDdHhMm from the oldest
-- Full Backupfile you want to Keep
-- The filename is DbName_Full_YyyyMmDdHhMm.FUL
SET @iLen = len(@vcDbName) + 7
SET @cDateTime = substring(@vcFileName,@iLen,12)
-- Delete Files with an older YyyyMmDdHhMm
-- The filenames are DbName_Type_YyyyMmDdHhMm.TYP
DECLARE File_cur CURSOR FOR
SELECT fil_nam FROM #temp_table
WHERE substring(fil_nam,@iLen, 12) < @cDateTime
SELECT @errCode = @@error
IF @errCode <> 0
return (@errCode)
OPEN File_cur
SELECT @errCode = @@error
IF @errCode <> 0
begin
DEALLOCATE File_cur
return (@errCode)
end
FETCH NEXT FROM File_cur INTO @vcFileName
SELECT @errCode = @@error
IF @errCode <> 0
begin
CLOSE File_cur
DEALLOCATE File_cur
return (@errCode)
end
WHILE @@fetch_status <> -1
BEGIN
SET @vcStmt = 'DEL "' + @vcDir + '\' + @vcFileName + '"'
EXEC @errCode = master..xp_cmdshell @vcStmt
IF @errCode <> 0
begin
CLOSE File_cur
DEALLOCATE File_cur
return (@errCode)
end
FETCH NEXT FROM File_cur INTO @vcFileName
SELECT @errCode = @@error
IF @errCode <> 0
begin
CLOSE File_cur
DEALLOCATE File_cur
return (@errCode)
end
END
CLOSE File_cur
DEALLOCATE File_cur
return (@errCode)
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply