Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup all DBs of a SQL-Server Instance Expand / Collapse
Author
Message
Posted Tuesday, April 01, 2008 1:49 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item Backup all DBs of a SQL-Server Instance
Post #477451
Posted Thursday, May 22, 2008 2:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 2:51 AM
Points: 6, Visits: 43
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
Posted Sunday, July 24, 2011 10:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse