| Author |
Topic |
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
 Posted - 2007-01-09 : 19:09:13
|
| we are currently doing thisFULL BACKUP ALL DATABASESDELETE 1 DAY OLD FULL BACKUPSbut before the full back up ends the drive is full ... as that works out to be around 140GIG what im thinking is there a way to do thisFULL BACKUP specific databaseDELETE 1 DAY OLD FULL BACKUP of that specific datbaseFULL BACKUP specific databaseDELETE 1 DAY OLD FULL BACKUP of that specific datbaseFULL BACKUP specific databaseDELETE 1 DAY OLD FULL BACKUP of that specific datbaseetc...? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-09 : 19:14:30
|
| Always do one database at a time.If you really get tight for space, you can backup to compressed directories, or use a product like Litespeed that creates compressed backup files.CODO ERGO SUM |
 |
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-09 : 19:23:49
|
| yeah might work on that idea , but im writting up a script now that may do what im after wil post if i get it to workthanx |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-09 : 20:42:40
|
| exec sp_msforeachdb'if (''?'') <> ''tempdb'' and (''?'') <> ''msdb'' and (''?'') <> ''model'' and (''?'') <> ''master'' BEGIN declare @oldname varchar(2000), @newname varchar(2000), @backupname varchar(2000), @olddate varchar(2000), @newdate varchar(2000), @directory varchar(2000), @filename varchar(2000) set @filename= ''?'' set @directory = ''c:\backupmina'' -- change directory to suit server set @backupname = @filename+'' - Full Backup'' --create new backup of current database set @newdate = convert(varchar,getdate(),112) set @newname = @directory+@filename+''_backup_''+@newdate+''.bak'' BACKUP DATABASE [ ? ] TO DISK = @newname WITH NOFORMAT, NOINIT, NAME = @backupname, SKIP, NOREWIND, NOUNLOAD, STATS = 10 -- delete old backup of current database set @olddate = convert(varchar,dateadd(hh,-23,getdate()),112) -- delete backup from yesterday set @oldname = @directory+@filename+''_backup_''+@olddate+''.bak'' EXECUTE master.dbo.xp_delete_file 0,@oldname,N''bak'' END' |
|
|
|
madhuotp
Yak Posting Veteran
78 Posts |
Posted - 2007-01-09 : 23:34:37
|
| Create Dumpdevice(backup device) which is pointing to any other Network path ie machine where u have enough space. and use that device in backup script. U r taking backup to C:\ which is very risky. if the machine crashes the sole purpose of backup is gone... so keep a network machine only for dedicated backup also try to duplicate it.In your script u r not verifying the backup is ok or not. u r taking backup and deleting the old one. this is not a fool proof mechanismMadhu |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-09 : 23:45:01
|
| we actually back up to C on each server then have a seperate backup detecated server which takes those files once their created |
|
|
|
madhuotp
Yak Posting Veteran
78 Posts |
Posted - 2007-01-10 : 01:06:07
|
| ok, there may be valid reason for that, but it is not recommended and it will of course reduce the performance. If u ask me, u have to change the script(a) first change should be change of backup path , to any drive other drive or machine(b) verify backup before deleting the old backup. it is not necessary that the backup which u have taken is always in good shape.Madhu |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-10 : 01:17:27
|
| good suggestions i will defenitly do that... any others would be appreciated |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-10 : 04:35:55
|
"exec sp_msforeachdb"We have a table of database names, and a "backup style" for each - "No backup", "Low risk backup" and "High risk backup". Any new database is added to the table (by the job) as a High Risk Backup so we are sure to back any newly created databases up.We also store retention times for each database, and each type of backup (so FULL backup is 3 weeks, Diff backup is 7 days, TLog backup is 2 days etc.)I think you would be much better of using a CURSOR to loop round the databases, rather than creating a SQL String in which all the quotes have to be doubled up and so on. Very hard to debug.You should filter out databases that are Offline etc. and possibly those that are Read OnlyI mention this because if you are building a backup tool it might provide you for some future proofing!"if (''?'') <> ''tempdb'' and (''?'') <> ''msdb'' and (''?'') <> ''model'' and (''?'') <> ''master''"You DO need to backup msdb, model and master ..."set @olddate = convert(varchar,dateadd(hh,-23,getdate()),112) -- delete backup from yesterday"This won't work in all sorts of scenarios:- the backup of all databases on the server takes more than one hour to complete (ours do ...)- it fails to run one dayand so on.I suggest either examining the files on disk and deleting the stale ones, or having a table of Backup Filenames (with the planned "Delete date") and deleting the ones that are stale."taking backup to C:\ which is very risky""to any drive other drive or machine"Not sure why you think that (as a general statement)?My view is:The backups should be to a different physical device on the server. If your databases are on D: and you only have C: and D: in the machine then backup to C: would be better than D: IMHO.if you only have C: then that's going to have to do!(Ideal configuration of a server for SQL is another story, but if its on a machine already and you are stuck with it then ...  )The backups then need to be copied to tape as soon as possibleIdeally they should also be copied to another LAN location immediatelyThe only circumstance where I would backup directly to a LAN location is if disk space was too tight locally, but there would have to be some exceptional reason for NOT installing more disk space locally before I considered that. (SANs and stuff like that might be different, but for bog-standard off-the-shelf small servers that's how I see it)"verify backup before deleting the old backup"I think there is a command to do this directly on the backup file in SQL 2005. In SQL 2000 you would have had to restore to a different server and run a DBCC CHECKDB to be sure the backups were "good"Kristen |
|
|
|
madhuotp
Yak Posting Veteran
78 Posts |
Posted - 2007-01-10 : 06:31:07
|
| Ya.. You have a RESTORE VERIFYONLY option in both SQL 2000 and SQL 2005. But it has different behavior in SQL 2000 and SQL 2005. In 2000 what VERIFYONLY do is, it will check the media header and then returned a success or an error. The entire backup set could be invalid, and every other sector on the media could be bad. But aslong as the media header was intact, it would return a success. So you can’t believe the result in SQL 2000.I would tell in sql 2000 you should not use this command. However, SQL Server2005 now performs the necessary checks, so you should execute this command every time you perform a backup.Madhu |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2007-01-10 : 07:48:39
|
| Buy a new drive. Storage is relatively cheap and one single backup could easily be corrupt.-------Moo. :) |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-10 : 19:58:39
|
| thanx heaps guys i like the info, well we are implementing a new backup solution in a month but in the mean time i will be carrying out this procedurei will verify the backup is valid before deleting the old one....we currently only have C drive...also that method i wrote will not delete items by time it will only delete full backups made the previous day (date is checked not time) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-11 : 03:25:27
|
| "date is checked not time"OK, in that case wouldn't this be more appropriate than subtracting 23 hours? Mind you, you will still have the same problem if your job runs at 11PM and takes more than one hour.set @olddate = convert(varchar,dateadd(Day, -1, getdate()),112)Kristen |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-11 : 17:55:40
|
| thank you that will do just fine, btw if something mensioned on here by me is not implemented,I would like to mension every reply i recive helps build my personal knowledge base, thanx |
|
|
|
|
full back up too large for drive so...