backup script

Question

backup script

sindhigrl

Default port

Points: 1448
More actions
September 20, 2006 at 8:44 am

#174232

I posted this yesterday on the backups discussion...but no one seems to know the answer so maybe someone here would?

I'm a little confused about noinit/init, skip/noskip, format/noformat. I read up on them on BOL and it's still clear as mud! I'm trying to create a backup job that backs a db up to a backup device that retains full backups for two days and think I'm muddling these options up. Can anyone set me straight? This is the script I wrote - I don't think it's what I'm trying to do at all.
BACKUP DATABASE [test]
                TO [backup_test]
                WITH RETAINDAYS =2,
              NOINIT ,  NAME = N'test backup',
                NOSKIP , STATS = 10, NOFORMAT

Thanks!
Thanks!
Anita
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 1

Anita,

I'm not aware that there is any way to "rotate" backup files kept in a backup device, and I'm pretty certain the parameters you listed (Retaindays, etc) will not help.

I keep full backups for one week by creating them to a file space and then manipulating the files, rather than creating and storing them in a single backup device.

Step One of a job creates the backup:

Backup database Test to disk='C:\Backup\Test.bak'

Then Step Two uses the File System Object to manipulate that backup file. The name is changed to include the date, the backup file is copied to a remote location, the original can be deleted to save space. This manipulation is best done in VB script--be sure to select "Active X" command (not the default "T-SQL" command in the dropdown box on the Job step.

The next night a new "Test.bak" backup file is created in that same file space and manipulated to change the name to include the date, etc. Step 3 of the job deletes old backup files from the remote location if they are older than GetDate()-7.

Hope this gives you some ideas,

Elliott

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 2

Anita,

Here is what I use to back up a DB:

-- Create a logical backup device for the full backup.

EXEC sp_dropdevice 'par_pos_1'

EXEC sp_addumpdevice 'disk', 'par_pos_1', 'd:\sqldata\MSSQL\data\par_pos.bak'

-- Back up the full database.

BACKUP DATABASE par_pos TO par_pos_1 WITH INIT

WITH INIT causes the backup to over-write, not append, to the existing backup file. To adapt this code to your needs, you could create multiple backup devices named Monday, Tuesday, Wednesday etc. (each of them would point to a different disk file of course) and then alter my code so it would select the correct device based on day of week. Would that work?

sindhigrl Default port Points: 1448 More actions · Answer 3

Thanks for the replies...I was afraid I wouldn't get it to work. Guess I'll have to try another way. The problem is that managment wants to retain 2 days worth of full backups and being that we have over 100 databases that's a lot of devices and jobs if I need to create two per database. If I were to just create the job without the dump device I would be able to retain backups for 2 days- correct?

Thanks!

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

I didn't realize you had that many db's, lucky you 😉

How are your backups set up now?

sindhigrl Default port Points: 1448 More actions · Answer 5

You don't want to know!! I was brought in 6 months ago on a contract position to set up their maintenance because they have never had a dba. The server admin was handling everything (backing up to tape once every day or two, and no maintenance jobs whatsoever) and then they had some disasters that resulted in data loss (which is how I got here). So basically I've been setting everything up for them and we just completed a large SAN upgrade so now we can backup to disk instead of tape. As well as putting tlog backups in place. Getting the server admin to understand the backing up to disk instead of directly to tape is a big effort still...he's still fighting me tooth and nail!!

Thanks!

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 6

Anita,

Why haven't u considered SQL Maintenance plan. There u can specify the number of days that u want to retain the backup.

Thanks

Sreejith

sindhigrl Default port Points: 1448 More actions · Answer 7

I'm not really crazy about the maintenance plans because of the error messages you receive. Maybe there is another way besides the way I set one up, but when something goes wrong it seems the messages are very cryptic and hard to understand.

Thanks!

sindhigrl Default port Points: 1448 More actions · Answer 8

I'm still having some trouble with my backups. I know I can't keep multiple days using a backup device, but shouldn't I be able to script it? I'm using this script, but backups aren't being deleted:

BACKUP DATABASE [test_db]

TO disk= 'L:\backups\test_db.bak'

WITH RETAINDAYS =2,

NOINIT ,

NAME = N'test_db backup',

NOSKIP , STATS = 10

I'd rather not use a maintenance plan, but I know when you set up a plan you can specify to delete copies older than x days. You should be able to script the same- shouldn’t you?

Thanks!

CK Bhatia Say Hey Kid Points: 679 More actions · Answer 9

I have used the following script in the past to delete speicifc old backups. You would obviously need to change the dates, and ensure that you have the required permissions on the backup drive.

/*

This script will physically delete old backup files (both Database & tran Log) for the specified date range

If you need to remove only database backups - add in the following line to the where clause in the cursor

and s.type = 'D'

*/

declare @sqlstr varchar(512)

declare c1 cursor for

select 'del ' + f.physical_device_name

from msdb.dbo.backupset s with (nolock), msdb.dbo.backupmediafamily f with (nolock)

where s.backup_finish_date between '07/03/2006' and '07/14/2006'

and s.media_set_id = f.media_set_id

open c1

fetch next from c1 into @sqlstr

while @@fetch_status = 0

begin

exec master.dbo.xp_cmdshell @sqlstr, no_output

fetch next from c1 into @sqlstr

end

close c1

deallocate c1

I feel the need - the need for speed

CK Bhatia