Create batch file for backup 1

[password99]

I am trying to create a process to schedule automatic backups. We are using MSDE 2000 so I was thinking of creating a batch file that will accept filename as argument and will be attached to windows scheduler to run at scheduled interval.

Is there a better way of scheduling backup?

 

[mrdenny]

Yes, using SQL Server Agent to backup the database. Without enterprise manager you'll need to use sp_add_job to create a job. There's tons of info in BOL about how to use sp_add_job and the other procedures that go with it.

The T/SQL you can use to backup the database would be something like this.

declare @Date varchar(10)
declare @FileName varchar(100)
set @date = convert(varchar(10), getdate(), 112)

set @FileName = 'd:\MSSQL\MSSQL\Backup\Database_Name' + @date + '.bak'
backup database {Database_Name} to disk=@filename

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)