backup all databases

Last Post 03 Jan 2008 09:17 AM by sqladmin. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqladmin
New Member
New Member

--
15 May 2007 11:18 AM
here's a quick script to backup all databases in one go.

even though these kinds of scripts aren't difficult to produce
i figure why not post it any way.

just run this in query analyzer, or management studio.
remember to change the output to 'text' or CTRL+T
before you run it.

----------------------------------------

use master
go
set nocount on
select 'backup database [' + name + '] to disk = ''\\MyServer\MyShare\' + name +'.bak'' with init, name = ''' + name + ' backup'',
skip, stats = 10, noformat' + char(10) + 'go' + char(10) from sysdatabases where name not like 'tempdb' order by name asc

----------------------------------------

take the output of this script, and run it
again.

hope it's useful.
sqladmin
New Member
New Member

--
04 Sep 2007 05:41 AM
here is the same using Litespeed.

-----------------------------------
use master
go
set nocount on
select 'exec master.dbo.xp_backup_database @database = ''' + name + ''', @filename = ''D:\MyPath\' + name + ' LiteSpeed_Full.BKP'', @init = 1, @logging = 0, @with = ''SKIP''' + char(10) + 'go' + char(10) from sysdatabases
where name like 'MyWildCard%'

-----------------------------------
sqladmin
New Member
New Member

--
02 Nov 2007 06:47 PM
using sp_msforeachdb

sp_msforeachdb 'BACKUP DATABASE ? TO DISK = ''\\MyShare\Backup\?.bak'' WITH FORMAT'

sqladmin
New Member
New Member

--
03 Jan 2008 08:33 AM
create a backup job for every database (using cursors of course)

these are native backups to e:\backup\ drive, using a .txt ouput file to d:\backupreport

----------------------------------------------

declare @mydb varchar(60)

declare GetDBname cursor read_only
for
select name from sysdatabases

open GetDBname

fetch next from GetDBname
into @mydb
declare @desc varchar(50)
while @@fetch_status = 0
begin

set @desc = 'FULL BACKUP ' + @MYDB

exec msdb.dbo.sp_add_job @job_name= @desc,
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=@desc,
@category_name='[uncategorized (local)]',
@owner_login_name='sa'

fetch next from GetDBname
into @mydb
end
close GetDBname
deallocate GetDBname
go
/**************************************************/
declare @mydb varchar(60)

declare GetDBname cursor read_only
for
select name from sysdatabases

open GetDBname

fetch next from GetDBname
into @mydb
declare @desc varchar(50)
declare @command varchar (200)
declare @outputfile varchar (50)
while @@fetch_status = 0
begin

set @desc = 'FULL BACKUP ' + @MYDB
set @command = 'BACKUP DATABASE [' + @mydb + '] TO DISK = N''E:\Backup\' + @mydb + '.bak'' WITH INIT , NOUNLOAD , NAME = N''' + @mydb + ' Backup'', NOSKIP , STATS = 10, NOFORMAT'
set @outputfile = 'D:\BackupReport\' + @mydb + '.txt'

EXEC msdb.dbo.sp_add_jobstep @job_name = @desc, @step_name=N'Full Backup',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=@command,
@database_name=@mydb,
@output_file_name=@outputfile,
@flags=2

fetch next from GetDBname
into @mydb
end
close GetDBname
deallocate GetDBname
go
/**************************************************/
declare @mydb varchar (60)
declare GetDBname cursor read_only
for
select name from sysdatabases

open GetDBname
fetch next from GetDBname
into @mydb
declare @desc varchar(50)

while @@fetch_status = 0
begin

set @desc = 'FULL BACKUP ' + @mydb

EXEC msdb.dbo.sp_add_jobschedule @job_name = @desc, @name=N'Backup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20050419,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959

EXEC msdb.dbo.sp_add_jobserver @job_name = @desc, @server_name = N'(local)'

fetch next from GetDBname
into @mydb
end
close GetDBname
deallocate GetDBname
go




----------------------------------------------
sqladmin
New Member
New Member

--
03 Jan 2008 09:17 AM
by the way... the jobs are all created in a disabled state. here's a script to
enable them all.

remember to set the output to text not grid by hitting CTRL-T
-----------------------------
set nocount on
use msdb
go

select 'sp_update_job @job_name = ''' + name + ''', @enabled = ''1''' + char(10) + 'go' + char(10) from sysjobs
where name like 'full backup%'
-----------------------------

take the output, and run it back against the msdb.

hope this is useful.

SQLUSA
New Member
New Member

--
04 Jan 2008 05:09 AM
sp_msforeachdb is the simplest solution.

Undocumented though.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
sqladmin
New Member
New Member

--
04 Jan 2008 05:05 PM
yes your right...
sqladmin
New Member
New Member

--
04 Jan 2008 05:05 PM
using LiteSpeed


declare @mydb varchar(60)

declare GetDBname cursor read_only
for
select name from sysdatabases

open GetDBname

fetch next from GetDBname
into @mydb
declare @desc varchar(50)
while @@fetch_status = 0
begin

set @desc = 'LITESPEED FULL BACKUP ' + @MYDB

exec msdb.dbo.sp_add_job @job_name= @desc,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=@desc,
@category_name='[uncategorized (local)]',
@owner_login_name='sa'

fetch next from GetDBname
into @mydb
end
close GetDBname
deallocate GetDBname
go
/**************************************************/
declare @mydb varchar(60)

declare GetDBname cursor read_only
for
select name from sysdatabases

open GetDBname

fetch next from GetDBname
into @mydb
declare @desc varchar(50)
declare @command varchar (200)
declare @outputfile varchar (50)
while @@fetch_status = 0
begin

set @desc = 'LITESPEED FULL BACKUP ' + @MYDB
set @command = 'exec master.dbo.xp_backup_database @database = [' + @mydb + '], @filename = N''E:\Backup\' + @mydb + ' LiteSpeed_Full.BKP'', @backupname = N''' + @mydb + ' Backup'', @init = 1, @with = N''SKIP'', @with = N''STATS = 10'''
set @outputfile = 'D:\BackupReport\' + @mydb + '.txt'

EXEC msdb.dbo.sp_add_jobstep @job_name = @desc, @step_name=N'Full Backup',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=@command,
@database_name=@mydb,
@output_file_name=@outputfile,
@flags=2

fetch next from GetDBname
into @mydb
end
close GetDBname
deallocate GetDBname
go
/**************************************************/
declare @mydb varchar (60)
declare GetDBname cursor read_only
for
select name from sysdatabases

open GetDBname
fetch next from GetDBname
into @mydb
declare @desc varchar(50)

while @@fetch_status = 0
begin

set @desc = 'LITESPEED FULL BACKUP ' + @MYDB

EXEC msdb.dbo.sp_add_jobschedule @job_name = @desc, @name=N'Backup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20050419,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959

EXEC msdb.dbo.sp_add_jobserver @job_name = @desc, @server_name = N'(local)'

fetch next from GetDBname
into @mydb
end
close GetDBname
deallocate GetDBname
go
sqladmin
New Member
New Member

--
08 Jan 2008 04:25 AM
in case you need to change the schedules for bulk amount of jobs try this...

feel free to mod.

---------------------------------------

use msdb
go

set nocount on

select distinct 'exec sp_update_jobschedule @job_name = ''' + sj.name + ''', @name = ''Backup'', @active_start_time = ''001000'''
+ char(10) + 'go' + char(10)



/*
sjh.run_date,
run_status = case run_status
when '1' then 'success'
when '0' then 'fail'
end
*/
from sysjobs sj join sysjobhistory sjh on sj.job_id = sjh.job_id
--where run_date = '20080107' and run_status = '0'
--order by run_date, run_status asc

---------------------------------------

sqladmin
New Member
New Member

--
16 Apr 2008 11:37 AM
automatically create a LiteSpeed backup job for each new database created just
create a trigger on the master table (forbidden in sql 2005) which simply runs the following procedure
upon updated with a new database name.

nothing special, and real simple:

--------------------------------------------------

CREATE procedure [dbo].[sp_createbackupjob]
as

/****** Object: Job [LITESPEED FULL BACKUP @newdb] Script Date: 04/14/2008 14:06:21 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 04/14/2008 14:06:21 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

declare @newdb varchar(50)
set @newdb = (select top 1 [name] from master..sysdatabases order by dbid desc)
declare @desc varchar(100)
set @desc = 'LITESPEED FULL BACKUP ' + @newdb
declare @mycommand varchar(300)
set @mycommand = 'exec master.dbo.xp_backup_database @database = ' + @newdb + ', @filename = N''E:\Backup\' + @newdb + ' LiteSpeed_Full.BKP'', @backupname = N''' + @newdb + ' Backup'', @init = 1, @with = N''SKIP'', @with = N''STATS = 10'''


DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@desc,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=@desc,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Full Backup] Script Date: 04/14/2008 14:06:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full Backup',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@MyCommand,
@database_name=@newdb,
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Backup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20050419,
@active_end_date=99991231,
@active_start_time=10000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

--------------------------------------------------

hope this is useful.
You are not authorized to post a reply.

Acceptable Use Policy