Post reply

backup script

  • 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!

  • September 20, 2006 at 4:53 pm

    #661597

    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 

  • September 20, 2006 at 6:56 pm

    #661610

    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?

  • September 21, 2006 at 10:02 am

    #661808

    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!

    Thanks!

  • September 21, 2006 at 11:00 am

    #661817

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

    How are your backups set up now?

     

  • September 21, 2006 at 11:15 am

    #661820

    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!

  • September 21, 2006 at 11:44 am

    #661825

    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

  • September 21, 2006 at 1:40 pm

    #661850

    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!

    Thanks!

  • October 2, 2006 at 8:55 am

    #663646

    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!

     

    Thanks!

  • October 25, 2006 at 11:04 am

    #667928

    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

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

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