December 24, 2008 at 6:55 am
So far i was using maintenance plans for backups. I looking for a method where i can automate all the existing db's and also future databases.
Here is my criteria
1. Databases starting with "Dev_" shud be backed up incrementally every day and a FULL bkup every friday.
2. Databses starting with "Rev_" shud be backed up FULL every friday.
3. seperate plan for system databases which can be backedup every day in FULL
FYI.. I use litespeed for backups.
December 24, 2008 at 7:41 am
Does this link help? http://www.sqlservercentral.com/scripts/Backup+/+Restore/31640/
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 24, 2008 at 10:11 pm
U can schedule following script as per ur requirement.
--Full backup database stared from dev_
Declare @date varchar(50)
DECLARE @sql varchar(8000)
set @date = convert(varchar,getdate(),112)
DECLARE @BAK_PATH VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR
select name from sys.databases where name like 'DEV_%'
OPEN c_bak
FETCH NEXT FROM c_bak INTO @BAK_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'backup database '+@BAK_PATH+' to disk = ''E:\QA_Backup\Full\'+@BAK_PATH+'_backup_'+@date+'.bak'''
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @BAK_PATH
END
CLOSE C_BAK
DEALLOCATE C_BAK
------------------------------------------------
--Incremental backup database stared from dev_
Declare @date varchar(50)
DECLARE @sql varchar(8000)
set @date = convert(varchar,getdate(),112)
DECLARE @BAK_PATH VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR
select name from sys.databases where name like 'DEV_%'
OPEN c_bak
FETCH NEXT FROM c_bak INTO @BAK_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'backup database '+@BAK_PATH+' to disk = ''E:\QA_Backup\Full\'+@BAK_PATH+'_backup_'+@date+'.bak with deferentail '''
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @BAK_PATH
END
CLOSE C_BAK
DEALLOCATE C_BAK
-----------------------------------------------------------
--Full backup database stared from REV_
Declare @date varchar(50)
DECLARE @sql varchar(8000)
set @date = convert(varchar,getdate(),112)
DECLARE @BAK_PATH VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR
select name from sys.databases where name like 'REV_%'
OPEN c_bak
FETCH NEXT FROM c_bak INTO @BAK_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'backup database '+@BAK_PATH+' to disk = ''E:\QA_Backup\Full\'+@BAK_PATH+'_backup_'+@date+'.bak'''
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @BAK_PATH
END
CLOSE C_BAK
DEALLOCATE C_BAK
December 24, 2008 at 10:14 pm
--For Full backup of system databases:
Declare @date varchar(50)
DECLARE @sql varchar(8000)
set @date = convert(varchar,getdate(),112)
DECLARE @BAK_PATH VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR
select name from sys.databases where name in ('master','msdb','model')
OPEN c_bak
FETCH NEXT FROM c_bak INTO @BAK_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'backup database '+@BAK_PATH+' to disk = ''E:\QA_Backup\Full\'+@BAK_PATH+'_backup_'+@date+'.bak'''
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @BAK_PATH
END
CLOSE C_BAK
DEALLOCATE C_BAK
January 2, 2009 at 6:40 am
Paresh
What if some of the backups fail and how wud i re run job from that point of failure so that i dont need to run whole job from begining.
January 2, 2009 at 7:20 am
Mike Levan (1/2/2009)
PareshWhat if some of the backups fail and how wud i re run job from that point of failure so that i dont need to run whole job from begining.
Do you have one thousand databases in your server?
If the answer is No, why don't you just schedule a job for each one of them?
Scheduling individual jobs would provide you with more flexibility, an alert if the job fails and the ability to re-run jobs as needed.
For future databases just add an item to your deployment check list: Set Up Backup Strategy.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 2, 2009 at 8:31 am
There are ample scripts available on this site on backups. Just go through them and modify them to suit your requirement. But make sure u've a good backup policy in place.
Also i'd suggest you backup your production database frequently than your system databases which should include full and tran log backups and (optionally) differential log backups.
January 2, 2009 at 8:40 am
Mike Levan (12/24/2008)
...FYI.. I use litespeed for backups.
Does Litespeed not have something built into it to help you set that up?
The Redneck DBA
January 7, 2009 at 11:39 am
Are there any scripts out with automated backups for VLDB which has partitions based on each year.
WE have Db's with partioned file groups for each year and I dont want to backup FULL db each time but instead want to back up the file for the current year which will be an automated job for all the db's in the server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply