Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup all DBs of a SQL-Server Instance


Backup all DBs of a SQL-Server Instance

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10095 Visits: 1
Comments posted to this topic are about the item Backup all DBs of a SQL-Server Instance
Daan Stam
Daan Stam
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 44
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
AlexGreen
AlexGreen
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search