October 27, 2009 at 7:10 am
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('model','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
I am doing native full backup with the above code through a job but if database size is 90GB i expect the bkpsize to be same as database size as it is native backup but is increasing by 90GB after every full backup.
I assume when this is executed it has to delete the existign backup and do fresh copy of backup but it i think it is not deleting the existing one.
October 27, 2009 at 7:18 am
Tara-1044200 (10/27/2009)
I assume when this is executed it has to delete the existign backup and do fresh copy of backup but it i think it is not deleting the existing one.
No, it's not deleting the existing one because you're not telling it to delete the existing one. If you want, when you backup to a file, to delete existing backups in the file, use the WITH INIT option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2009 at 7:32 am
Ohh Thank you so much.
October 27, 2009 at 12:59 pm
Before using the with init option to initialize an existing file - you really need to make sure that backup has been copied to offline storage.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2009 at 1:03 pm
Jeffrey Williams-493691 (10/27/2009)
Before using the with init option to initialize an existing file - you really need to make sure that backup has been copied to offline storage.
Indeed. In fact, it's usually recommended to always backup to a new file, to avoid the chance of accidentally overwriting a backup that you need.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2009 at 2:29 pm
You could use a maintenance plan, write each backup to a separate file as suggested, then use the cleanup task to delete the previous backups older than x days.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply