Automatic Backup Script
-
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.
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've done something very similar; the difference in mine is that it assumes specific paths, which you would have to change or paramiterize (sp?);
it is adapted from a backup script found right here on sqlservercentral.com.
my example below backs up the file, zips it, and copies it to a network location.
As for the ZIP functionality, it depends on pkzip25.exe being in the PATH or in the mssql bin folder, either way.
pkzip25 was the last FREE command line utility i could find, and it does a fine job. If you can't find it on the web, let me know and I'll throw a copy on a site somewhere.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_BackUpEverything]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_BackUpEverything]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
Purpose: Backup Everything, and then copy it to a network location for redundancy.
Backup-->Zip-->Save To Remote Location
This Script should be scheduled as a daily job in SQLAgent.
This script makes a FULL Backup of EVERY database, including master,msdb, etc
except of course tempdb, because backup and restore is not permitted on that database.
Further names it with names like:
GMEDI-Monday.bak
GMOK-Monday.bak
GMVBAK-Monday.bak
GMVBAZCDBG-Monday.bak
GMVBCT-Monday.bak
GMVBDC-Monday.bak
GMVBLA-Monday.bak
GMVBLABE-Monday.bak
GMVBNY-Monday.bak
GMVBOK-Monday.bak
GMVBWest-Monday.bak
HDSDATALA-Monday.bak
master-Monday.bak
model-Monday.bak
msdb-Monday.bak
phonbook-Monday.bak
pubs-Monday.bak
The result is you have a FULL Backup every day For 7 Days
Future Version my include an incremental backup to be run on half hour intervals for
extra coverage.
*/
CREATE Procedure Proc_BackUpEverything
As
Begin
declare @dbname as varchar(40),
@msgdb as varchar(255),
@dbbkpname as varchar(40),
@DY varchar(15),
@Pdy varchar(150),
@result int
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name <> 'tempdb'
set @DY = (select datename(dw, getdate()) )
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
Print ' Beginning Full Backup Job'
WHILE @@FETCH_STATUS = 0
BEGIN
select @msgdb= 'database backup in progress: ' + @dbname + @DY
PRINT @msgdb
select @dbbkpname='D:\MSSQL7\BACKUP\' + @dbname +'-' + @DY + '.bak'
backup database @dbname to disk=@dbbkpname
--Create Zip File of the Backup
--pkzip -a "C:\MSSQL7\BACKUP\GMVB-Monday.zip" "C:\MSSQL7\BACKUP\GMVB-Monday.bak"
SET @Pdy=(select 'pkzip -a "'+ @dbname +'-' + @DY + '.zip"' +' "' +@dbname +'-' + @DY + '.bak"'
exec @result =xp_cmdshell @Pdy
IF (@result = 0)
PRINT 'Successfully Created Zip File'
ELSE
PRINT 'Failed to Create Zip File. Check Drive mappings and available harddrive space for both the Server and The remote location.'
--Copy the File to Remote Location
SET @Pdy = (select 'COPY "' + @dbbkpname + '" "Y:\' + @dbname +'-' + @DY + '.zip' + '"')
exec @result = XP_CMDSHELL @Pdy
IF (@result = 0)
PRINT 'Successfully Copied Files to Network'
ELSE
PRINT 'Failed to Copy to the network location. Check Drive mappings and available harddrive space for both the Server and The remote location.'
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
Print 'All Backup Jobs Completed'
End
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Why not use Enterprise Manager and just create a maintenance plan for each database? That way you can schedule them when you want, specify the backup location and job options and specify a number of days to keep.
There's some things avoid...like don't schedule transaction log dumps for DB's in simple recovery mode or having it run the integrity checks with the 'Attempt to repair any minor problems' option on if the db can't be placed in single user mode.
I know some will counter not to trust Enterprise Manager to do this, but my experience has been that (at least since 7.0) that it's pretty stable and reliable. If you're new to SQL Server as you say, IMHO you'll be up and running a lot quicker using EM.
Hope this helps
My hovercraft is full of eels.
-
Hey, thanks to both of you. Both the otions looks very good to me. The advantage of the first one is that you just have to schedule the script, no need to worry about scheduleing different databases. It also zips the DB and copies to the another location, in case there is space probelm on the server machine.
The second one is easy to use, specially for beginners. Maintenance plan also help to check the integrity of the backup and repair the minor problems.
Again, I appreciate your time and thoughts.
-
Problem...!!!
the above script is throwing an error at line " exec @result =xp_cmdshell @Pdy"
The error : "Incorrect syntax near the keyword 'exec'."
Any help would be greatly appreciated.
-
the above script is throwing an error at line " exec @result =xp_cmdshell @Pdy"
The error : "Incorrect syntax near the keyword 'exec'."
Any solution would be really appreciated
-
oops;
missing a closing parenthesis on the line above the error line:
SET @Pdy=(select 'pkzip -a "'+ @dbname +'-' + @DY + '.zip"' +' "' +@dbname +'-' + @DY + '.bak"')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I take a different approach to automating maintenance plans. I create several maintenance plans that do only one thing (Full Backup, Log Backup, and ReIndex). I then schedule a job to run a procedure that makes sure each database is in the appropriate plan(s) every evening before the backup plans run. The procedure checks recovery mode and status to determine what should be done, you don't have to update the maintenance plans when a database is created or modified. Normal changes to the plans like schedule changes, backup location, retention period, etc. can be done through the EM wizard instead of having to rewrite the spBackupAllDatabases proc.
If you have customized the maintenance jobs (for example added a zip step to the Full Backup job), the databases will automatically be moved in and out of the correct plans without you having to open them in EM and losing your modifications.
If you have need for addition plans, say a Weekly Full Backup+Daily Differential for larger databases, the procedure below could be extended. You could use database size or other factors to pick a plan, or an external table like my ReindexMaintExclusions that is used to keep very large databases out of the reindex optimization (a separate job checks for individual tables that are fragmented).
CREATE PROCEDURE dbo.CheckDBMaint AS
/* Ensure all Online, Writable, Full or Bulk-logged recovery DBs are in the "Trans Log Backup" plan
Remove all Offline, ReadOnly, or Simple recovery DBs
Ensure all Online, Writeable DBs are in the "Daily Full Backup" plan
Remove all Offline or ReadOnly DBs
Ensure all Online, Writeable DBs are in the "Weekly Reindex" plan unless excluded
Remove all Offline or ReadOnly or excluded DBs
Suppress "Recent Backup" alarms in Spotlight for Offline or ReadOnly DBs
Standby DBs (log-shipped) are treated as offline
*/
-- Find the plan ids for major maint plans
declare @FullBackup uniqueidentifier
declare @LogBackup uniqueidentifier
declare @Optimization uniqueidentifier
select @FullBackup = plan_id from msdb.dbo.sysdbmaintplans where plan_name = 'Daily Full Backup'
select @LogBackup = plan_id from msdb.dbo.sysdbmaintplans where plan_name = 'Trans Log Backup'
select @Optimization = plan_id from msdb.dbo.sysdbmaintplans where plan_name = 'Weekly Reindex'
declare @dbmaint table (
[id] int identity not null primary key clustered,
dbname sysname not null,
IsOnline bit not null default(0),
IsReadOnly bit not null default(0),
IsLogged bit not null default(0),
InFullPlan bit not null default(0),
InLogPlan bit not null default(0),
InOptPlan bit not null default(0))
declare @i smallint, @dbname sysname
declare @IsOnline bit, @IsReadOnly bit, @IsLogged bit, @InFullPlan bit, @InLogPlan bit, @InOptPlan bit
if @FullBackup is null
raiserror ('Maintenance plan "Daily Full Backup" not found"',16,1) WITH LOG
if @LogBackup is null
raiserror ('Maintenance plan "Trans Log Backup" not found"',16,1) WITH LOG
if @Optimization is null
raiserror ('Maintenance plan "Weekly Reindex" not found"',16,1) WITH LOG
-- List all databases (except tempdb), their Simple recovery, offline, or read-only status
-- and their current maint plan membership
insert into @dbmaint (dbname, IsOnline, IsReadOnly, IsLogged, InFullPlan, InLogPlan, InOptPlan)
select rtrim(sdb.name) as dbname,
case when databasepropertyex(sdb.name, 'IsInStandby') = 0
and databasepropertyex(sdb.name, 'Status') = 'ONLINE'
then 1 else 0 end as IsOnline,
case when databasepropertyex(sdb.name, 'Updateability') = 'READ_ONLY'
then 1 else 0 end as IsReadOnly,
case when databasepropertyex(sdb.name, 'Recovery') <> 'SIMPLE'
then 1 else 0 end as IsLogged,
ISNULL(InFullPlan,0), ISNULL(InLogPlan,0), ISNULL(InOptPlan,0)
from master.dbo.sysdatabases sdb
left join (
select database_name,
max(case when plan_id = @FullBackup then 1 else 0 end) as InFullPlan,
max(case when plan_id = @LogBackup then 1 else 0 end) as InLogPlan,
max(case when plan_id = @Optimization then 1 else 0 end) as InOptPlan
from msdb.dbo.sysdbmaintplan_databases
group by database_name
) maint on sdb.name = maint.database_name
where sdb.name <> 'tempdb'
set @i = @@ROWCOUNT
-- For each database found...
while @i > 0 begin
select @dbname = dbname, @IsOnline = IsOnline, @IsReadOnly = IsReadOnly, @IsLogged = IsLogged,
@InFullPlan = InFullPlan, @InLogPlan = InLogPlan, @InOptPlan = InOptPlan, @i = @i - 1
from @dbmaint where [id] = @i
-- Check "Trans Log Backup" - Must be Online, Read-Write, and Full or Bulk-logged recovery
if @LogBackup IS NOT NULL begin
if @IsOnline = 1 and @IsReadOnly = 0 and @IsLogged = 1 begin
-- Add to Trans Log Backup if it's not already a member
if @InLogPlan = 0
EXECUTE sp_executesql N'exec msdb.dbo.sp_add_maintenance_plan_db @plan, @db',
N'@plan uniqueidentifier, @db sysname',
@LogBackup, @dbname
end else begin
-- Remove from Trans Log Backup if it is a member
if @InLogPlan = 1
EXECUTE sp_executesql N'exec msdb.dbo.sp_delete_maintenance_plan_db @plan, @db',
N'@plan uniqueidentifier, @db sysname',
@LogBackup, @dbname
end
end
-- Check "Daily Full Backup" - Must be Online and Read-Write
if @FullBackup IS NOT NULL begin
if @IsOnline = 1 and @IsReadOnly = 0 begin
-- Add to Daily Full Backup if it's not already a member
if @InFullPlan = 0
EXECUTE sp_executesql N'exec msdb.dbo.sp_add_maintenance_plan_db @plan, @db',
N'@plan uniqueidentifier, @db sysname',
@FullBackup, @dbname
end else begin
-- Remove from Daily Full Backup if it is a member
if @InFullPlan = 1
EXECUTE sp_executesql N'exec msdb.dbo.sp_delete_maintenance_plan_db @plan, @db',
N'@plan uniqueidentifier, @db sysname',
@FullBackup, @dbname
end
end
-- Check "Weekly Reindex" - Must be Online and Read-Write, and not in ReindexMaintExclusions
if @Optimization IS NOT NULL begin
if @IsOnline = 1 and @IsReadOnly = 0
AND NOT EXISTS (SELECT NULL FROM DBA.dbo.ReindexMaintExclusions WHERE dbname = @dbname) begin
-- Add to Weekly Reindex if it's not already a member
if @InOptPlan = 0
EXECUTE sp_executesql N'exec msdb.dbo.sp_add_maintenance_plan_db @plan, @db',
N'@plan uniqueidentifier, @db sysname',
@Optimization, @dbname
end else begin
-- Remove from Weekly Reindex if it is a member
if @InOptPlan = 1
EXECUTE sp_executesql N'exec msdb.dbo.sp_delete_maintenance_plan_db @plan, @db',
N'@plan uniqueidentifier, @db sysname',
@Optimization, @dbname
end
end
/* The following section only applies to sites using Spotlight on SQL Server,
a monitoring tool from Quest Software. */
-- Check for Offline or Read-Only DBs
if @IsOnline = 0 or @IsReadOnly = 1 begin
-- Suppress "Recent Backup" alarms in Spotlight
if object_id('dba.dbo.Spot_Constants') is not null
if not exists(select null from dba.dbo.Spot_Constants where ConstantValue = @dbname)
insert into dba.dbo.Spot_Constants (ConstantName, ConstantValue)
values ('SuppressBackupAlarm' + cast(DB_ID(@dbname) as varchar), @dbname)
end else begin
-- Un-Suppress "Recent Backup" alarms in Spotlight
if object_id('dba.dbo.Spot_Constants') is not null
delete from dba.dbo.Spot_Constants where ConstantValue = @dbname
end
end
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply