Hi guysI'm pretty new to sql2008.I read a lot over internet regarding backup performance and tuning, and was pointed out to a some improvment command.What I would like to do is :backup a db to multiple file (4) with a 1 retain day delay. What I mean is that during 2 days the backup will be store in files, then the 3rd day the oldest backup in those files will be overwritten, then I always keep 2 days backup.Seems clear to you ? (my english is not as good as I want)What I put in place :BACKUP DATABASE [edeal] TO DISK = N'H:\SQL_Backup\Edeal_Bak_3.bak', DISK = N'H:\SQL_Backup\Edeal_Bak_1.bak', DISK = N'H:\SQL_Backup\Edeal_Bak_2.bak',DISK = N'H:\SQL_Backup\Edeal_Bak_4.bak' WITH RETAINDAYS = 2, NAME = N'edeal-Full Database Backup',NOFORMAT, INIT, buffercount=47, maxtransfersize=4194304,BLOCKSIZE = 65536, NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
What is appening : the backup went well during 2 days but fail during the 3rd day with the following error :Executed as user: service. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator.[SQLSTATE 01000] (Message 2528) The medium on device 'H:\SQL_Backup\Edeal_Bak_3.bak' expires on Feb 24 2012 5:12:00:000AM and cannot be overwritten.[SQLSTATE 42000] (Error 4030) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Is what I'm trying to do impossible to put in place ?Should I stop trying to backup to multiple file and get back to "a file per backup" and then play with "cleaning maintenace task" ?I'm a bit lost and really hope someone can point me out to my mistake if any .Thanks a lot
Rolling backup
Posted - 2012-02-24 : 06:07:09