Automated Daily Backup
lionheart
Posts: 7
I'm just starting to use the software and want to have daily backups with each backup in it's own folder (or at least different file names). What is the easiest way to do this? I'm assuming there is some command line way to do it, or I was thinking of possibly job in SQL where I create a different one for each day of the week with different names.
Any tips or posts that cover this would be greatly appreciated.
Thanks.
Don
Any tips or posts that cover this would be greatly appreciated.
Thanks.
Don
Comments
master..sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [E:\Temp\Backups\<AUTO>]"'
Or if you only use one backup folder, you can set up the default folder, and simply use
master..sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [<AUTO>]"'
In the next version, you need not set up separate jobs for each database, nor be limited to a single folder as we'll be enhancing the syntax a little to allow you to do things like this:
master..sqlbackup '-sql "BACKUP DATABASES [pubs, northwind, master] TO DISK = [F:\Backups\<DATABASE>\<AUTO>]"'
or this (the inverse)
master..sqlbackup '-sql "BACKUP DATABASES EXCEPT [tempdb, msdb] TO DISK = [F:\Backups\<DATABASE>\<AUTO>]"'
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
I'm very anxious for the new version, as it's been talked about since I started using the product. In particular the automated script abilities mentioned above.
Is there any news on the new version? I'm on 3.2.0, is that the most up-to-date? Is there a place I can check for more current versions?
Thank you for the great product and support.
Knowledge Base Manager
myKB Knowledge Base Software
The UI comes with a check for updates item in the Help menu, like all of Red Gate's products. You can always use this to locate the most current version of the software.
The next version will be released in a few months and contain wizards for log shipping and scheduling backup jobs. The backup scheduling wizard is going to be able to backup a selection of databases or even all of the databases on a server automatically!
INSERT #tmpFileDetails
EXEC master..xp_getfiledetails @vchrBackupLocation
-- Determine whether the file is too old to retain
IF EXISTS
(
SELECT *
FROM #tmpFileDetails
WHERE
DATEDIFF(
day
,CASE WHEN LEN(LastWrittenTime) = 6 THEN
CONVERT(DATETIME,LastWrittenDate + ' '
+ LEFT(LastWrittenTime,2) + ':'
+ LEFT(RIGHT(LastWrittenTime,4),2) + ':'
+ RIGHT(LastWrittenTime,2))
ELSE -- Take into account when the time is not in the desired time formate from xp_getfiledetails function
CONVERT(DATETIME,LastWrittenDate + ' '
+ LEFT('000000',2) + ':'
+ LEFT(RIGHT('000000',4),2) + ':'
+ RIGHT('000000',2))
END
,GETDATE()
) >= @intRetainDays
)
BEGIN
SET @vchrCmdLine = 'del ' + QUOTENAME(@vchrBackupFolder + '\' + @vchrDatabaseName + '\' + @oFileName,'"')
EXEC @intErrorCode = master..xp_cmdshell @vchrCmdLine
END
By default, backup = Y and retention period = 7 days. New databases are automatically added with these defaults (so you don't forget to add new databases to your backup schedule).
The control table can then be manipulated manually, if you desire, to change the defaults. And, it could even be modified to include an encrpyt/decrypt value - the SP below automatically encrypts all databases for me.
You'll have to generate your own password etc in the @sql parameter at the end of the stored procedure, and modify the TO DISK string accordingly.
Create the SP in the master database. The control table will also reside master.
Not sure if this helps, but it shows what you can do ... or what SQLBackup 4.0 will possibly provide going forward
--
-- R.Grey
-- 11-Nov-2005
--
-- Stored Procedure: myspEncBackupDaily
--
-- Procedure to be scheduled via SQL Server Agent as often as required
-- to backup and encrypt all databases using SQL Backup 3.2.0
--
-- Backup files are retained for the period specified in the
-- myspEncBackupDaily table (default 7 days)
--
--
-- Backup file format
--
-- <dbname>_yyyymmdd_hhmmss.sqb
--
-- Usage:
--
-- exec myspEncBackup 'D:\SQLBackup\'
--
CREATE Procedure myspEncBackupDaily
@Path varchar(128)
as
-- create _myspEncBackupDaily table
if not exists (select * from dbo.sysobjects where id = object_id(N'[master].[dbo].[_myspEncBackupDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
Create table [master].[dbo].[_myspEncBackupDaily]
(
Name varchar(128) primary key nonclustered ,
BackupFlag varchar(1) not null check (BackupFlag in ('Y','N')) ,
RetentionPeriod int not null
)
end
set nocount on
declare @sql varchar(1000)
-- Get all database names
create table #DBName
(
ID int identity (1,1) ,
Name varchar(128) not null ,
RetentionPeriod int null
)
insert #DBName
(Name)
select name
from master..sysdatabases
-- Include any new databases in the backup
insert _myspEncBackupDaily
(
Name ,
BackupFlag ,
RetentionPeriod
)
select #DBName.Name ,
'Y' ,
7 -- default 7 days
from #DBName
left outer join _myspEncBackupDaily
on _myspEncBackupDaily.Name = #DBName.Name
where _myspEncBackupDaily.Name is null
and lower(#DBName.Name) not in ('tempdb','master','model','msdb')
-- Remove any non-existant databases
delete _myspEncBackupDaily
where not exists
(
select *
from #DBName
where #DBName.Name = _myspEncBackupDaily.Name
)
delete #DBName
-- loop through databases
declare @Name varchar(128) ,
@RetentionPeriod int ,
@ID int ,
@MaxID int
insert #DBName
(Name, RetentionPeriod)
select Name, RetentionPeriod
from _myspEncBackupDaily
where BackupFlag = 'Y'
select @MaxID = max(ID) ,
@ID = 0
from #DBName
while @ID < @MaxID
begin
-- get next database to backup
select @ID = min(ID) from #DBName where ID > @ID
select @Name = Name ,
@RetentionPeriod = RetentionPeriod
from #DBName
where ID = @ID
declare @eID int ,
@eMaxID int ,
@eName varchar(128)
-- now do the backup
select @sql =
'-SQL "BACKUP DATABASE
TO DISK = ''' + @Path + '\' + @Name + '_' + convert(varchar(10),getdate(),112) + '_' + replace(convert(varchar(10),getdate(),108), ':', '') + '.sqb''
WITH NAME = ''' + @Name + ' ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ''',
DESCRIPTION = ''Backup on ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ' Database: ' + @Name + ''',
INIT,
PASSWORD = ''<ENCRYPTEDPASSWORD>........</ENCRYPTEDPASSWORD>'',
ERASEFILES = ' + convert(varchar(3),@RetentionPeriod) + ',
COMPRESSION = 3" -E'
exec master..sqlbackup @sql
end
GO
UPDATE: Aah ! Looks like Brian sorted it - my firefox (running with the MSIE user agent due to MS ISA Server rubbish) doesn't seem to like the WYSIWYG buttons for code block etc.
:-(
THANK YOU!!! Your post has caused me to get of my ??? and implement this strategy. Before today I was doing it manually daily.
In my tests though, the remove portion isn't working.
I have a value of 2, and it just keeps creating more and more files. If I run the backup sequentially, one right after the other, the file system just keeps building up. Is it because of the variable names changing with the date? Is it because the DateTime on the Files isn't > 2 days old?
Can someone from Red-Gate look at the above script, and see why it's not removing the archives.
Thank you!
Knowledge Base Manager
myKB Knowledge Base Software
It's a day thing. If you've set the retention period to two days, you won't see anything being removed unless it is a backup from > 48 hours previously.
And, considering I only posted this today, you can't have gone over two days yet
My 7 day strategy is working good. The SP is scheduled to be run at 4am, 10am, 4pm and 10pm everyday. Looking at the directory right now (22 Nov 5.30pm), the earliest backup remaining is correctly (15 Nov 10pm) - this should be removed when the (22 Nov 10pm) backup is created as it will be greater than 7*24 hours old.
Thank you.
Knowledge Base Manager
myKB Knowledge Base Software