Automatic Backup Script
-
I am new to SQL vServer, trying to do somebody else's job. My requirement is an automatic daily backup, kepp a copy of backup for last 7 days. I found the following script on SQLTeam.com, but there was a lot debate about this script. I am not sure if it is safe to use. I am sure lots of SQL experts are browsing through site and let us see if anyone have any suggestions:
Your suggestions may be useful to many others. Thanks in advance.
here is the script:
*******************************************************
if exists (select * from sysobjects where id = object_id(N'[dbo].[spBackupAllDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spBackupAllDatabases]
go
Create Procedure spBackupAllDatabases
@Path varchar(128) ,
@Type varchar(4) -- Full / Log
as
/*
Backup file format
<dbname>_Full_yyyymmdd_hhmmss.bak
<dbname>_Log_yyyymmdd_hhmmss.bak
exec spBackupAllDatabases 'c:\SQLBackups\', 'Full'
*/
/*
drop table DatabaseBackup
Create table DatabaseBackup
(
Name varchar(128) primary key nonclustered ,
BackupFlagFull varchar(1) not null check (BackupFlagFull in ('Y','N')) ,
BackupFlagLog varchar(1) not null check (BackupFlagLog in ('Y','N')) ,
RetentionPeriodFull datetime not null ,
RetentionPeriodLog datetime not null
 
*/
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 datetime null
 
insert #DBName
(Name)
select name
from master..sysdatabases
-- Include any new databases in the backup
insert DatabaseBackup
(
Name ,
BackupFlagFull ,
BackupFlagLog ,
RetentionPeriodFull ,
RetentionPeriodLog
 
select #DBName.Name ,
'Y' ,
'N' ,
'7 jan 1900' , -- default one week
'1 jan 1900'
from #DBName
left outer join DatabaseBackup
on DatabaseBackup.Name = #DBName.Name
where DatabaseBackup.Name is null
and lower(#DBName.Name) <> 'tempdb'
-- Remove any non-existant databases
delete DatabaseBackup
where not exists
(
select *
from #DBName
where #DBName.Name = DatabaseBackup.Name
 
delete #DBName
create table #ExistingBackups
(
Name varchar(128) ,
ID int identity (1,1)
 
-- loop through databases
declare @Name varchar(128) ,
@RetentionPeriod datetime ,
@LastBackupToKeep varchar(8) ,
@ID int ,
@MaxID int
insert #DBName
(Name, RetentionPeriod)
select Name, case when @Type = 'Full' then RetentionPeriodFull else RetentionPeriodLog end
from DatabaseBackup
where (@Type = 'Full' and BackupFlagFull = 'Y')
or (@Type = 'Log' and BackupFlagLog = '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
-- Delete old backups
delete #ExistingBackups
select @sql = 'dir /B ' + @Path
select @sql = @sql + @Name + '_' + @Type + '*.*'
insert #ExistingBackups exec master..xp_cmdshell @sql
if exists (select * from #ExistingBackups where Name like '%File Not Found%')
delete #ExistingBackups
select @LastBackupToKeep = convert(varchar(8),getdate() - @RetentionPeriod,112)
delete #ExistingBackups where Name > @Name + '_' + @Type + '_' + @LastBackupToKeep
declare @eID int ,
@eMaxID int ,
@eName varchar(128)
-- loop round all the out of date backups
select @eID = 0 ,
@eMaxID = coalesce(max(ID), 0)
from #ExistingBackups
while @eID < @eMaxID
begin
select @eID = min(ID) from #ExistingBackups where ID > @eID
select @eName = Name from #ExistingBackups where ID = @eID
select @sql = 'del ' + @Path + @eName
exec master..xp_cmdshell @sql
end
delete #ExistingBackups
-- now do the backup
select @sql = @Path + @Name + '_' + @Type + '_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','') + '.bak'
if @Type = 'Full'
backup database @Name
to disk = @sql
else
backup log @Name
to disk = @sql
end
go
*******************************************************
-
I posted this question in the 'BACKUP' section then i reliazed that T-SQL is the better place to post my question. Apologize for any inconvenience.
-
Just one question ... "Why not use SQL Maintenance Plans ?" ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
My requirement is to take tha backup and keep the 7copies for last 7 days. We have to move each backup file to the network file-server automatically.
Can this be accomplished with SQL Maint. Plan?
-
Everything but the file move is handled. You can add an additional Job step to perform this task to accomplish this.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I would appreciate if you can briefly explain, how can i accomplish this?
-
Why dont you directly take the backups on the network path ? Why do you want to take a backup first and then copy to a remote drive.
Steps:
1. Map the network drive to your server from where you want to take a database backup.
2. Use DB Maintainence Planner to create a plan to take a backup of the database on the mapped network drive.
3. You can set the option when do you want to delete the old backups. You can set it as 7 days.
Let me know of your progress. I have been using the same strategy for 5 years now. It works perfectly fine.
--Kishore
-
This isn't advisable.
If you want to put backup files in a location other than the local server I would always suggest backing it up locally and then copying it.
If your database backup normally takes 1 hour to complete, then backing it up across the network could considerably increase that time, which is not ideal. You don't want SQL Server taking a longer time to backup your databases because it means performance is reduced.
Plus, backing it up locally and then copying it, means you have two copies of the same backup file (plus whatever tape or offsite storage you are using).
As someone else suggested, use the maintenance plan, which can delete files older than 7 days and then copy the files to the remote location.
-
1) Set up the database maintenance plan as normal.
2) configure the maintenace plan to delete files older than x days (or hours)
3) Once the maintenance plan is set up, find the job that was created and edit the job.
4) Add a new step (cmdExec step) into this job and type in your copy command. If you use something like robocopy to copy the files then it's easy because robocopy will only copy files that don't already exist in your destination. I'm sure you can do the same with normal copy though.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply