automatic backup and restore job for a database

From the same backup file or from the last backup file?
Are you restricting only a solution based on backup/restore or you are available to accept other solution?

from the last backup based on backup /restore

Hello,

You can simply create the job by right clicking on the Job tab of SQL server Agent.

In the Step -> Click New and in that Give suitable Step NAme
 
Type - > T SQL

Database -> the database you want to restore on

In the command write the T SQL script to restore the db from the file location

RESTORE DATABASE CDCTest
FROM DISK = 'D:\SQLDatabases\SQL2012\MSSQL\Backup\CDCTest.bak'

and Then Click OK

You can also schedule it as per the requirement.

Hope this will helpful for you

You'll need to create the RESTORE command dynamically. Also will you restore in the same SQL Server instance with a different database name or you'll restore to a different SQL Server instance?

@Vikas:
How that can restore from the last backup? The requirement is to restore every day, so every single day should be from a different backup, right Teoman?

for example, there are two databases on the server, Sales and Sales_Test..in the evening,the database Sales will be backuped and restored to Sales_Test on the same server everyevening..

I think if the backup file name is kept same then the given solution will work for restore.

However if you want to do the back up in the same job then add this query to step 1

BACKUP DATABASE CDCTest TO disk = 'E:\SQLDatabases\SQL2012\MSSQL\Backup\CDCTest.bak'

this will create a backup and from here the same can be restored to the required server

Just replace the @db variable value with your database name and @location with backup location.

set @db='Test'
set @location='D:\Backup\'

Backup:
You need to look into the "microsoft SQL maintenance plans." They allow you to schedule a backup of your sql database.  
Restore:

There is no 'Restore DB Task' in the maintenance planbut you can use the Execute T-SQL Statement task. In that task, add a restore script in your T-SQL statement.

example:

RESTORE DATABASE YourDBName FROM DISK = 'D:\DBbackups\YourDB.BAK'
WITH REPLACE
GO

Select allOpen in new window

This is the simplest way I can think of.
Good luck!

If you'll restore to the same instance then you can query the msdb directly for the last backup:

DECLARE @BakFile AS VARCHAR(MAX)

SELECT TOP 1 @BakFile = mf.physical_device_name
FROM msdb.dbo.backupset AS s
	INNER JOIN msdb.dbo.backupmediaset AS ms ON s.media_set_id = ms.media_set_id
	INNER JOIN msdb.dbo.backupmediafamily AS mf ON ms.media_set_id = mf.media_set_id
WHERE s.type = 'D'
	AND s.database_name = N'Sales' 
ORDER BY s.backup_finish_date DESC;

RESTORE DATABASE sales_test 
FROM DISK = @BakFile
WITH RECOVERY,
   MOVE 'Sales_Data' TO 'D:\NewLocation\Sales_test_Data.mdf', 
   MOVE 'Sales_Log' TO 'D:\NewLocation\Sales_test_Log.ldf'

Select allOpen in new window

NOTE:Set in the MOVE clause the correct logical names and new location.

the backup of Sales will be overwrited on the same place so otherwise,there will be lots of backup file on the disk and it will run out of disk space

the backup of Sales will be overwrited on the same place

Do you realize that when you overwrite the last backup file that you can't restore the database to an older date than the last 24 hours?
Backups should be always moved to another location and shouldn't stay in the server or they will be useless.