Scheduling an automatic backup

thaenu

Arnie Rowland

You need to explore how to use the Windows Scheduler service.

(And that is beyond the scope of this forum.)

OR, You may wish to explore this option:

http://www.codeproject.com/vb/net/SQLAgent.asp

Madhu K Nair

Check out this http://www.mssqltips.com/tip.asp tip=1174

Madhu

thaenu

Thanks for your reply.iam new to the database side,what i need is i want script for automatic database backup and send me mail for every day after the backup is done succesfully or not, in sql server 2005 management studio.did you able to help me pls...........

Arnie Rowland

Unless you are ready to purchase additional third party products, we have provided you links to the best 'free' options available to you. (And they do not provide everything on your 'wish list'.)

Madhu K Nair

another option may be, go for License edition like Standard /Workgroup which can fullfill all the requirement you mentioned... SQL Server express is not supporting all the features available in Licensed Editions, so you may go for license edition of sql server 2005.

Madhu

thaenu

Thank you once again for your quick replies.i want to know, is there any script available for automatic database backup in sql server2005

Madhu K Nair

Create a BackupDatabase.bat file which contains script as follows

1. Open notepad

2. type the follwong sql command

sqlcmd -q"Backup database Yourdatabasename to disk='d:\yourbackupname.bak' with init" -SServerName -Ulogin -Ppassword

3. Save the notepad file as BackupDatabase.bat

then you can schedule it using windows scheduller..

Read about

SQLCMD, Backup etc in BOL

Madhu

Arnie Rowland

thaenu wrote:

Thank you once again for your quick replies.i want to know, is there any script available for automatic database backup in sql server2005

As we have clearly stated, YES -but NOT with SQL Server Express. Only with the 'paid' and licensed editions (Workgroup, Standard, Enterprise.)

With SQL Server Express, you will have to use one of the methods that we provided, or purchase a third party product to automatically backup your databases.

thaenu

Thanks for your help.i create a script for automatic backup but i did not know is it correct or not so pls refer the script shown below and tell about that.

CREATE PROCEDURE nightlybackup
AS

BACKUP DATABASE [yourdb]
TO DISK = 'c:\backups\dbbackup.dat'

BACKUP LOG [yourdb]
TO DISK = 'c:\backups\dblogbackup.dat'

Now create a cmd script; run the below at a command prompt of :

echo osql -d [yourdb] -E -Q "EXEC
dbo.nightlybackup">>c:\backups\nightlybackup.cmd

Now add it as a scheduled task :

schtasks /create /tn nightlybackup /tr c:\backups\nightlybackup.cmd /sc
daily /st 00:05:00

Madhu K Nair

Every thing looks ok... I would say, there is no need to taking Log backup once you are taking full backup. Either remove it or create another batch file and schedule it seperately

Madhu

thaenu

Thank you very much for your quick reply,i had one doubt;if we directly backup the database daily without using script means we create folder daily for date and copy the database backup but now we create script for backup then how can we find out thus the backup is done daily.

thaenu

when i typed this following command:

schtasks/create/tn nightlybackup/tr c:\Program Files(x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\um.bak/sc daily/st 00:00:00

the following error occures:

ERROR:Invalid syntax.

type "SCHTASKS / " for usage.

what can i do for this

Arnie Rowland

You can type "SCHTASKS / " and read the usage instructions for the Windows Scheduler service.

While it may actually take some time to read it rather than just have someone do if for you, it will most likely prove out to be a rewarding exprience.

thaenu

Thank you for your reply.i can do automatic database backup using sqlcmd and schedule it using windows scheduller.now what i want is:i want to receive mail everyday after the backup is done sucessfully or got any error.what is the script for this