January 17, 2009 at 8:59 pm
I looking for backup scripts which serves my scenario.
I have very huge databases residing on 4 servers. All databases has multiple files like partion files according to the months. every months data is partioned in a seperate file. I would like to automate my backups where it backs up only latest 2 years of data, that means it comes to 24 files for 24 months.
anyone have scripts already doing such job, plesae share.
thanks
January 17, 2009 at 10:31 pm
Mike Levan (1/17/2009)
I looking for backup scripts which serves my scenario.I have very huge databases residing on 4 servers. All databases has multiple files like partion files according to the months. every months data is partioned in a seperate file. I would like to automate my backups where it backs up only latest 2 years of data, that means it comes to 24 files for 24 months.
anyone have scripts already doing such job, plesae share.
thanks
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
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 18, 2009 at 9:17 am
There are lots of scripts on this site (See the scripts link on the left) that you can moidfy.
January 18, 2009 at 5:20 pm
The script would do the entire data and not backup only 2 years worth of data cause it partitioned.
Its a good question why would you back up the entire database if you have partioned it off.
The questions is would the partitioned data ever be changed or is this set to read only.
Interesting.
January 18, 2009 at 9:00 pm
If your partitioned tables exist in different file/filegroup, you may want to take file/filegroup backups containing last 2 years data.
January 19, 2009 at 7:17 am
Can you do that backup up just a filegroup?
January 19, 2009 at 7:38 pm
Already i have files for each month and when i chek file groups from properties of database I see seperate file for each month. is there a way i can group all files of a year in to a group.
February 9, 2009 at 10:05 am
declare @dbname as varchar(80)
declare @msgdb as varchar(80)
declare @dbbkpname as varchar(80)
declare @datepart as char(1)
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where (name like 'STATE_%' and name not like 'STATE_CO%') or name like 'Budg%' order by name
set @datepart = 7
open rs_cursor
Fetch next from rs_cursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
select @msgdb= 'database backup in progress: ' + @dbname
PRINT @msgdb
select @dbbkpname='\\REPOS1\REV\' + @dbname + @datepart + '.bak'
exec master.dbo.xp_backup_database
@database = @dbname,
@filename = @dbbkpname,
@compressionlevel = 2,
@init = 1,
@threads = 1
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
GO
I am using the above script for backups, now as my databases are all partitioned and would like to back up for only past 2 years, anyways i will have 1 copy of FULL back up once. How would i change the same script to work.
February 11, 2009 at 8:17 am
Is it possible to backup just 24 partitions (2yrs data) of a database every month and do a restore test just for those 24 partitions. Before backing up 24 partitions i will be doing a FULL backup which contains 60 partittions data.
Please let me know tha possiblity and how best is this to impement this way.
THanks
February 14, 2009 at 12:42 pm
Hey can someone update my query accordingly to backup partitioned sql 2005 databases.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply