• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

SQL Job Schedule - For taking backup automatically -

We are using SQL 2005 Express Edition

I need to create a SQL Job Scheduler for taking my database backup automatically.

Backup file will appear as [Database Name] [Date] [Time] . bak and stored in a specific file path.

I need the backup to be taken on every day - 08:00 PM
0
chokka
Asked:
chokka
  • 4
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
chokkaAuthor Commented:


From the mentioned link - i have downloaded the .exe file / windows service.

Also i used this syntax to generate backup file.

It results 0 size - .bak file.

Some where logic of the syntax is incorrect. Also, i need to schedule email response which i am not able to rreceive as mentioned in that site.


DECLARE @BACKUPNAME VARCHAR(255)

DECLARE @BACKUPFILENAME VARCHAR(255)

SET @BACKUPNAME = 'MEDIS_' + CONVERT (NVARCHAR(20),GETDATE()) + '.bak'
SET @BACKUPFILENAME = N'C:\SQLBACKUPS\' + @BACKUPNAME


BACKUP DATABASE MEDIS TO
DISK = @BACKUPFILENAME WITH NOFORMAT, NOINIT,
NAME = @BACKUPNAME, SKIP,REWIND,NOUNLOAD, STATS = 10

Open in new window

0
 
David ToddSenior DBACommented:
Hi,

The convert needs a date style - suggest that you use 112 for the date and 108 for the time, as in

convert( nvarchar( 8 ), getdate(), 112 ) for the date

replace( convert( nvarchar( 5 ), getdate(), 108 ), ':', '' ) for the time

HTH
  David
set @backupname = N'MEDIS_' + convert( nvarchar( 8 ), getdate(), 112 ) + replace( convert( nvarchar( 5 ), getdate(), 108 ), ':', '' ) + N'.bak'

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
chokkaAuthor Commented:

Hi David,

Your script was helpful.

I was expecting exactly - what i want to name my database backup.

Now, i am using SQL 2005 Express  Edition.

Is there any way for me to implement a SQL Job Schedule Activity by setting Email Response ( Success / Failure )

Thanks,
Chokka



0
 
chokkaAuthor Commented:


From my understanding, I need to stick with Aneesh Comment for SQL Job Scheduler Service and David's Script for implementing in the Scheduler.

Now, i am working on Window's service.
Other than Email Notification, rest all seems to be working fine.
0
 
chokkaAuthor Commented:


Thanks,
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now