Scheduling Complete and Differential backups

I'm new to the SQL 7 scene but would like a few things clearing up with regards to scheduling backups.

Our backup time of databases has recently changed. I've scheduled in the new database backup using Enterprise Manager with the new time.

I expected the backup to take place at the new time, which it does, but when I came to restoring the database I've noticed that the old backup time is still being carried out. How do I cancel or get rid of the old backup time?

Secondly, If I do a complete backup of the database using Enterprise Manager, can I deattach the transactional log and delete the transactional log before reattaching in order to free up MG?

Thirdly, is carrying out a complete backup of the database via Enterprise Manager enough to ensure a full backup of the database should anything go wrong?

I would like help on these matters please as I need these clearing up. thanking you in advance!!!It sounds like you might have two scheduled jobs running one for the old time and one for the new time if so find the old job and delete it.

If you need to restore your database to a point in time you need the transaction log. A full backup backs up the database and the T-log together so you will only be able to restore to the last time you did the full backup.

If the transaction log is getting to large you can truncate the log which is sort of the same thing as deleting but doesn't actually get rid of the .ldf file. If you truncate the log without first backing it up you lose all those transactions from the last time the log was backed up!

If you need to restore to a point in time you need the T-log. Say you do a full backup every night and a T-log backup every hour. We will say that SQL fails on Tuesday at 1:45pm. To restore you would backup the current T-log because the next scheduled T-log backup will not occur until 2pm. and it contains the active portion of the log and you need to recover it by backing it up. You then would apply the full backup and all T-log backups in sequence up to the point of failure.

If you have SQL installed then you should have Books Online installed. You should find it in the menu. They have exhaustive information that talks about backup strategies. I suggest you take some time to read up on it it's well worth the time.

Best Regards|||[QUOTE][SIZE=1]Originally posted by mkal
It sounds like you might have two scheduled jobs running one for the old time and one for the new time if so find the old job and delete it.

Thats the thing, I'm pretty sure I deleted the old Database_bk file and created a new one in the 'MSSQL\Backup' folder but still the old backup time is there when doing a restore. I'll have another go.

As for the tranasactional backup, we currently carry out a Complete backup of a weekend and a differential backup every night which is scheduled using the Enterprise Manager but there is no option to backup the Transaction log. How is this done?

You'll have to bear with me on this one as I'm still a newbie and require a lot of patience.

Thanks v.much for the reply|||I'm still getting two lots of complete backups to restore as well as two lots of differential backups to restore having changed the times of when the backups are scheduled using Enterprise Manager.

Is there anyway I can get it so that there is only one backup scheduled?|||Deleting the backup file is not the same as deleting the job. In Enterprise Manager expand out the server you create the backups for. Click on the Management folder and then SQL Server Agent and then click on jobs. In the right hand pane you should see a list of jobs. There should be the one for your backup and possibly others. To explore what the job is doing right click on it and choose properties. You then can take a look at what's going on.

To free up space on the drive where the T-log is you can run a script see below, just replace the info between the <> (but don't include the <>) with the name of the database, name of the log file and the size expressed as an integer you want the file to become.

The following is from BOL:

If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

Backup log <DatabaseName> with truncate_only
GO
DBCC SHRINKFILE (<LogFile_Name, <targetsize>)
GO

Creating a full backup backs up the data and the T-log, so you can recover everything up to the day and time you performed the backup.

It doesn't back up the database structure i.e. tables, constraints, PK/FK. You should script your database to make sure that in the event of a major crash you can recreate the database and reload it from the backups.

To script your database use Enterprise Manager. Right click on the database you wish to script, choose 'All Tasks' and then Generate SQL Script.

Best Regards
Mike|||Cheers Mike,

When I went into the Management\SQLServerAgent\Jobs folder and sure enough I seen a whole load of jobs that had been duplicated. I have gotten rid of the ones that are now redundant and have left the jobs that run at a later time (the ones I need) in there. I'm quite sure that this will work but I will need to check in the morning after the databases have been backed up.

A big thank you mate for your help and persevering with me.|||One more thing about log backups. For this to take place you must have the recovery model of the database set to full and there must be at least one full database backup must exist.

To check the recoverty model right click on the database and choose properties. Go the options tab and where it says 'Recovery model' choose full.

Now when you right click on the database and choose 'All Tasks' and the 'Database Backup' you will see a radio button to perform a log backup.

Glad I could help.

Scheduled jobs overlapping

Greetings,
I have Full database backup job scheduled at 02:00 every day,
Differential backup every 2 hours from 00:00:00 to 23:59:59 and Log
backup every 15 min from 00:00:00 to 23:59:59. Is there a way to control
order of execution, for example: Full backup starts before Differential
backup and Differential backup starts before transaction log backup.
Does Schedule_id plays any role in processing order? Any advise
appreciated.

Aleksandar Andjelkovic

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!<Aleksandar Andjelkovic> wrote in message
news:40b6fc5a$0$208$75868355@.news.frii.net...
> Greetings,
> I have Full database backup job scheduled at 02:00 every day,
> Differential backup every 2 hours from 00:00:00 to 23:59:59 and Log
> backup every 15 min from 00:00:00 to 23:59:59. Is there a way to control
> order of execution, for example: Full backup starts before Differential
> backup and Differential backup starts before transaction log backup.
> Does Schedule_id plays any role in processing order? Any advise
> appreciated.
>
> Aleksandar Andjelkovic
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You could look at having multiple steps in the same job, or using
sp_help_job to check the status of other jobs before continuing, but the
easiest solution might be to use multiple schedules for each job:

Job1: Full - 02h00
Job2: Differential (Schedule 1) - 00h00
Job2: Differential (Schedule 2) - 04h00 to 23h59, every two hours
Job3: Log (Schedule 1) - 00h15 to 23h59, every hour
Job3: Log (Schedule 2) - 00h30 to 23h59, every hour
Job3: Log (Schedule 3) - 00h45 to 23h59, every hour
Job3: Log (Schedule 4) - 01h00 to 23h59, every 2 hours

You don't need a differential backup at 02h00 because you have the full one
at that time anyway. The multiple log schedules will overlap to backup at
15, 30 and 45 minutes past the hour every hour, and backup on each hour
alternates between differential and log - there's probably no point in
doing both at the same time.

Personally, this seems rather complicated to me, and I would only use
differential once or at most twice a day, with a full backup daily or
weekly, but I don't know your environment or requirements, so the schedule
you've described may well be the best one for you.

Simon