Backup all DBs of a SQL-Server Instance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by hangg » Backup all DBs of a SQL-Server Instance

Backup all DBs of a SQL-Server Instance

Rate Topic

Display Mode

Topic Options

Author

Message

Site Owners

Posted Tuesday, April 01, 2008 1:49 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,040, Visits: 1

Comments posted to this topic are about the item Backup all DBs of a SQL-Server Instance

Post #477451

Daan Stam

Posted Thursday, May 22, 2008 2:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 13, 2012 6:36 AM
Points: 6, Visits: 42

This script is missing some features:
Deleting of old backups
Excluding off line db's
Excluding stand by db's

Below the backup scripts for full and transaction backups I use for some years (If I remember correctly these scripts are based on scripts originally made by Tara Kizer).

Daan Stam
Database Administrator
AWVN
The Netherlands

/****** Object: StoredProcedure [dbo].[mySP_BackupFullAllDatabases] Script Date: 05/22/2008 09:55:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Daan Stam
-- Create date: 06-06-2007
-- Description: Backup of databases to individual subdirs
-- Deleting old backups
-- =============================================
CREATE PROCEDURE [dbo].[mySP_BackupFullAllDatabases]
@myBackupDir varchar(255),
@myDaystoKeepFullBackups int,
@myDaystoKeepTransBackups int,
@myRetMsg nvarchar(4000) =Null OUTPUT
AS
BEGIN
--SET NOCOUNT ON;

/** cursor for databases to backup **/
Declare myCursor cursor for SELECT [name] FROM master.dbo.sysdatabases WHERE [name] NOT IN ('tempdb') AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' and [filename] not like '%.ss' order by name
/** variable for loop through databases **/
declare @mydb as varchar(255)
/**variable for backup name **/
declare @myDirName as varchar(255)
declare @myBackupName as varchar(400)

/** Clean up backup directory **/
declare @DeleteDateStr nvarchar(30)
set @DeleteDateStr=Convert(nvarchar(30),getdate()-@myDaystoKeepFullBackups ,1)+' '+Convert(nvarchar(30),getdate()-@myDaystoKeepFullBackups ,8)
EXECUTE master.sys.xp_delete_file 0,@myBackupDir,'bak',@DeleteDateStr,1

set @DeleteDateStr=Convert(nvarchar(30),getdate()-@myDaystoKeepTransBackups ,1)+' '+Convert(nvarchar(30),getdate()-@myDaystoKeepTransBackups ,8)
EXECUTE master.sys.xp_delete_file 0,@myBackupDir,'trn',@DeleteDateStr,1

/**backup all databases**/
set @myRetMsg='Start'

open myCursor
fetch next from myCursor into @mydb
while (@@fetch_status<>-1)
begin
/** create backup dir **/
if len(replace(@mydb,' ',''))>0
begin
begin try
print ' '
print '--' + @mydb
/**create backupdir **/
set @myRetMsg= @myRetMsg + char(13) + char(13) + convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' Start ' + @mydb
set @myDirName =@myBackupDir + '\'+ @mydb
EXECUTE master.dbo.xp_create_subdir @myDirName

/**backup database **/
set @myBackupName= @mydb + '_backup' + convert(varchar(20),getdate(), 112) + replace(convert(varchar(20),getdate(), 108),':','') +'.bak'
set @myDirName=@myDirName +'\'+ @myBackupName
BACKUP DATABASE @mydb TO DISK = @myDirName WITH NOFORMAT, NOINIT, NAME = @myBackupName, SKIP, REWIND, NOUNLOAD, STATS = 10

set @myRetMsg= @myRetMsg + char(13)+ convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' End ' + @mydb
end try
begin catch
set @myRetMsg= @myRetMsg + char(13)+ convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' Error in backup of database ' + @mydb
set @myRetMsg= @myRetMsg + char(13) + @mydb + ' Error Occurred'
set @myRetMsg= @myRetMsg + char(13) + cast(ERROR_NUMBER()as varchar(60))
set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_SEVERITY() as varchar(60))
set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_STATE() as varchar(60))
set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_MESSAGE() as varchar(60))
end catch

/** Verify backup **/
begin try
print ' '
print '--' + @mydb
set @myRetMsg= @myRetMsg + char(13) + convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' Start verify ' + @mydb
RESTORE VERIFYONLY FROM DISK = @myDirName

set @myRetMsg= @myRetMsg + char(13) + convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' End verify ' + @mydb
end try
begin catch
set @myRetMsg= @myRetMsg + char(13) + convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) +' Error in verify backup' + @mydb
set @myRetMsg= @myRetMsg + char(13) + @mydb + ' Error Occurred'
set @myRetMsg= @myRetMsg + char(13) + cast(ERROR_NUMBER()as varchar(60))
set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_SEVERITY() as varchar(60))
set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_STATE() as varchar(60))
set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_MESSAGE() as varchar(60))
end catch
end
/** next database **/
fetch next from myCursor into @mydb
-- Let the system rest for 5 seconds before starting on the next backup
waitfor delay '00:00:05'
end
deallocate myCursor

END

/****** Object: StoredProcedure [dbo].[mySP_BackupTransAllDatabases] Script Date: 05/22/2008 10:00:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Daan Stam
-- Create date: 06-06-2007
-- Description: Backup Transactions of databases to individual subdirs
--
-- =============================================
CREATE PROCEDURE [dbo].[mySP_BackupTransAllDatabases]
@myBackupDir varchar(255),
@myRetMsg varchar(400) =Null OUTPUT
AS
BEGIN
--SET NOCOUNT ON;

/** cursor for databases to backup **/
Declare myCursor cursor for select [name] from master..sysdatabases where [name]<>'' and [name]<>'model' and [name]<>'tempdb' AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' and DATABASEPROPERTYEX([name], 'RECOVERY') = 'FULL' and [filename] not like '%.ss' order by [name]
/** variable for loop through databases **/
declare @mydb as varchar(255)
/**variable for backup name **/
declare @myDirName as varchar(255)
declare @myBackupName as varchar(400)

/**backup transactions all databases**/
open myCursor
fetch next from myCursor into @mydb
while (@@fetch_status<>-1)
begin
/** create backup dir **/
set @myDirName =@myBackupDir + '\'+ @mydb
EXECUTE master.dbo.xp_create_subdir @myDirName
/**backup database **/
set @myBackupName= @mydb + '_backup' + convert(varchar(20),getdate(), 112) + replace(convert(varchar(20),getdate(), 108),':','') +'.trn'
set @myDirName=@myDirName + '\' + @myBackupName
BACKUP LOG @mydb TO DISK = @myDirName WITH NOFORMAT, NOINIT, NAME = @myBackupName, SKIP, REWIND, NOUNLOAD, STATS = 10
fetch next from myCursor into @mydb
-- Let the system rest for 5 seconds before starting on the next backup
waitfor delay '00:00:05'
end
deallocate myCursor

set @myRetMsg=@@rowcount
return 0
END

Post #505023

AlexGreen

Posted Sunday, July 24, 2011 10:20 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 8:56 AM
Points: 17, Visits: 40

Why spend time in a script to backup one or several databases when there are so many tools available.

SQL Backup and FTP lets you backup your databases, schedule backup jobs, and sends you detailed reports in your email. You can even save your backups in remote servers via FTP.

Post #1147220

« Prev Topic | Next Topic »

Permissions