Sql 2000 Server Differential back script help

Question

Sql 2000 Server Differential back script help

Franco Carchedi

SSC Veteran

Points: 255
More actions
October 10, 2007 at 8:36 am

#198884

Hi all
I have been given the job of setting up our scripts for our Sql server 2000.
we do a full backup every night at the moment but disk space is running low doing this, what we wanted to do was do differential backups in the week and a full backup on the weekend.
I have been reading about this on the web, and I am now confused.
can anyone help me create a differential back script, and how to schedule it please the other problem is the server is a 24/7 service and the database never goes offline.
we are running windows 2003 sp1, Sql server 2000 sp4. all the backups are on the local disks and then backed up to tape, we are changing this to use another server for the backups to sit on.
all i have found is this,
BACKUP DATABASE Changepoint TO DISK = 'C:\Temp\DatabaseBackups\Changepoint_Diff_1.bak' WITH DIFFERENTIAL
GO
how do i turn this into a script and how do I automate it so that the backup run every evening.
I have run this manually and it worked fine, but when i tried to restore it errors and tells me the database is in use can anyone expalin this further to me or point me in the right direction please.
Thank you.
Frank

Viewing 15 posts - 1 through 15 (of 17 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Frank

To run the backup automatically, schedule it as a job, or use a database maintenance plan. You can read about those things in Books Online.

The reason your restore failed wasn't because the backup was bad. You need to make sure that nobody is using a database before you can restore it. You can use sp_who2 to see who is using the database.

John

Franco Carchedi SSC Veteran Points: 255 More actions · Answer 2

thanks for that info,

how can I include this in the script something to kick users out when the backup is about to begin ?

thanks again.

Frank

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Franco,

You don't have to kick users out of the database before the backup, just before restoring from a backup. A backup can be done while the database is in use.

Another point: You must restore a full backup with NO RECOVERY prior to restoring the differential backup.

Greg

Franco Carchedi SSC Veteran Points: 255 More actions · Answer 4

I'm new to all this, so just trying to learn all of this.

Thanks for the info guys thats great.

Frank

Franco Carchedi SSC Veteran Points: 255 More actions · Answer 5

Hi Greg.

do you know how I can include this in a script to check and lock user out for the restore to work successfuly and then allow the users to access the database once it is running.

Thanks

Frank

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

Do this before the restore:

ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Do this after the restore:

ALTER DATABASE yourdb SET MULTI_USER

Greg

Franco Carchedi SSC Veteran Points: 255 More actions · Answer 7

Hi

Thanks Greg.

I guess if I wanted to schedule this as a job i would need to create it in the master database ?

this is what i have done so far in the script

should I have it with norecovery or recovery ?

ALTER DATABASE Changepoint SET SINGLE_USER WITH ROLLBACK IMMEDIATE

restore database Changepoint from disk = 'd:\temp\changepoint_diff1.bak' WITH RECOVERY

GO

ALTER DATABASE changepoint SET MULTI_USER

thanks again for the help I really need it, don't want to get this wrong.

So I guess to restore a differential, I would need to restore the latest full backup and then all differentials to the day that I need to recover back to is this correct ?

Thank you.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Frank

You would create the job using either the GUI or the sp_add_job stored procedure. Jobs are actually stored in the msdb database.

To restore a differential you need to restore the full backup with no recovery and your last differential backup with recovery (since the latter contains all page modifications made since the full backup). Therefore your script wouldn't work as it is at the moment because you haven't restored the full backup.

John

Franco Carchedi SSC Veteran Points: 255 More actions · Answer 9

Thanks John,

any chance you could show me an example script of what you have said,

also could I have to seperate jobs to do the diferentials and the full ?

Thanks alot for the help

Frank.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Frank

There are sample scripts for RESTORE DATABASE in Books Online, so I won't repeat them here unless there's anything specific you're having a problem with.

I would advise restoring the full and differential backups in the same job, and probably even in the same job step. There's no reason to keep them separate since your database will be offline until the statement with WITH RECOVERY has finished.

John

Franco Carchedi SSC Veteran Points: 255 More actions · Answer 11

Thanks I'l take a look at BOL.

also If I create the scripts and keep the naming the same of the differential backups the scripts never need to be changed unless the file names change,

also how would i get the script to stop appending a backup to the diferentials backups say after 1 week of doing them ?

just worried they will keep growing if I can't get them to delete after 5 days and then re-create them, or keep the last two days and always delete anything older ?

Thanks John,

Sorry to keep on.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 12

Franco,

If you're writing the differential backups to a single file so the file contains a week's worth of backups, just have the differential that occurs next after the weekly full backup use WITH INIT to overwrite the entire file. Then, the backups will be appended to the file until the initializing backup wipes them out. I assume your tape copy occurs somewhere in there too.

Greg

Franco Carchedi SSC Veteran Points: 255 More actions · Answer 13

Thanks Greg,

Our tape backups occur everyday, weekends full and weekdays incrementials.

I am still confused on how to write this, I have managed to setup a differential backup but can not restore from it ?

keep getting a message about having norecovery or with standby when i try to restore from it what does that error message mean ?

Thanks again,

Frank

Franco Carchedi SSC Veteran Points: 255 More actions · Answer 14

Hi

Here is my script for backing up can anyone help me change it to do a restore please.

BACKUP DATABASE [Changepoint] TO DISK = N'd:\temp\changepoint_diff1.bak' WITH NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Changepoint backup', SKIP , STATS = 10, NOFORMAT DECLARE @i INT

select @i = position from msdb..backupset where database_name='Changepoint'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='Changepoint')

RESTORE VERIFYONLY FROM DISK = N'd:\temp\changepoint_diff1.bak' WITH FILE = @i

thanks Frank

:hehe: thank god it's friday.