SQL Server Backups

[fish]

I need to back up some sql data files ie MDB? which my current software won't as they are open and thus locked.

I used to get round this by turning off sql, then backup then restart. However I have a new Sql database which I can't do this with as it upsets the agents now running.

Anyone know of any free software etc which will do a live SQL backup on a schedule??

Backup software is tapeware.....

I'm of on hols tommorrow so any thoughts much appreciated.

Cheers.

[Don]

SQL Server itself will do a "hot" backup...no need to take the database down.

But you mention MDB files - that's an Access term. SQL Server has .MDF / .LDF files usually (not enforced).

Just use SQL Enterprise manager...or am I talking Greek, fish?

[Don]

Right. Here's what to do...

You are right you cannot just back-up the SQLServer data files to tape - as they are open. But you say you are using a tape backup system - presumably you are also backup a load of files from the filesystem at the same time.

So what you need to do is go into SQL Enterprise Manager and schedule a "hot" backup (just called a backup) not to tape - but to DISK. You create a thing called a DEVICE which is just really a file on disk.

You then do a normal tape backup of the file you created.

A bit of fiddling - but the whole thing can happen without taking the DB down for a second...

[fish]

Sorry probably didn't explain properly..... Yes I do mean MDF and Log files. But I only have MSDE ie the free version with no Enterprise manager etc. Therefore I have no front end software to schedule the backup. I have trialed a manager from Vale Software which can do scheduled backups to DISK but it is a small cost option. The SQL backup module for Tapeware is nearly £350

Any free hot backups to disk software out there?



>> Edited by fish on Wednesday 2nd February 19:10

[UpTheIron]

Take a look here:

www.devx.com/vb2themax/Tip/18621

Looks like the necessary T-SQL to dump a backup to disk which you can then backup to tape.

[Don]

Ahah.

Right its MSDE.

So...

Create a batch file that logs in to osql and performs a backup to disk.

Schedule the batch file with the Windows Scheduler service.

A bit tricky to set up - but free and you need no special tools to do it.

I'm not at work and so don't have the manuals/sample scripts we've got for doing this. If you like, though, I could e-mail you something like what you need for you to fiddle with tomorrow?

Mail me through my profile to remind me and I'll happily send you a sample...

[Don]

UpTheIron said:

Take a look here: www.devx.com/vb2themax/Tip/18621 Looks like the necessary T-SQL to dump a backup to disk which you can then backup to tape.

Very good. Not quite the way I'd have gone about it but it looks like it would do the job.

[fish]

Knowledge base article 241397 seems to have the script...does this look about right? And do I need to do the sheduling can I not just create a batch file with the script in and the schedule that????

[fish]

The text is as follows:

--This Transact-SQL script creates a backup job and calls sp_start_job to run the job.

-- Create job.
-- You may specify an e-mail address, commented below, and/or pager, etc.
-- For more details about this option or others, see SQL Server Books Online.
USE msdb
EXEC sp_add_job @job_name = 'myTestBackupJob',
@enabled = 1,
@description = 'myTestBackupJob',
@owner_login_name = 'sa',
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend =2,
@notify_level_page = 2
-- @notify_email_operator_name = 'email name'
go

-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
@step_name = 'Backup msdb Data',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE msdb TO DISK = ''c:msdb.dat_bak''',
@on_success_action = 3,
@retry_attempts = 5,
@retry_interval = 5
go

-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
@step_name = 'Backup msdb Log',
@subsystem = 'TSQL',
@command = 'BACKUP LOG msdb TO DISK = ''c:msdb.log_bak''',
@on_success_action = 1,
@retry_attempts = 5,
@retry_interval = 5
go

-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @job_name = 'myTestBackupJob', @server_name = N'(local)'

-- Run job. Starts the job immediately.
USE msdb
EXEC sp_start_job @job_name = 'myTestBackupJob'


• From the command line, use the following osql syntax to run the Transact-SQL script: OSQL -Usa -PmyPasword -i myBackupScript.sql -n


If I run the last line from a batch file will that work?
Obviously changing user names database names etc...

[Don]

fish said:

Knowledge base article 241397 seems to have the script...does this look about right? And do I need to do the sheduling can I not just create a batch file with the script in and the schedule that????

Yes you can do that - it was my original suggestion. You just need to make sure that your

BACKUP DATABASE wibble TO DISK='X:MyBackupFile.bkp'

statement is good and in a .sql file you can get osql to run. Then you have a batch file which calls osql properly and just schedule the batch file with windows tools...

Like I say - apart from the scheduling bit I have examples of everything else at the office - I'll e-mail you them tomorrow if you want them...

[Don]

fish said:

• From the command line, use the following osql syntax to run the Transact-SQL script: OSQL -Usa -PmyPasword -i myBackupScript.sql -n If I run the last line from a batch file will that work? Obviously changing user names database names etc...

Yep. Should do....

[fish]

Thanks for all the helps peeps, however tried the code this am am in a hurry to go on Hols and although it created the job etc it wasn't working properly. So I have bought the license only $90 for the Vale Software manager which is good and I've scheduled backups form there.

Thanks again

James