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.