Teoman Sahin
asked on
automatic backup and restore job for a database
I need to schedule a daily task that restores a database from a backup everyday ..how to create a job for this task in sql server 2008R2?
ASKER
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\M SSQL\Backu p\CDCTest. bak'
and Then Click OK
You can also schedule it as per the requirement.
Hope this will helpful for you
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\M
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?
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?
ASKER
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\M SSQL\Backu p\CDCTest. bak'
this will create a backup and from here the same can be restored to the required server
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\M
this will create a backup and from here the same can be restored to the required server
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just replace the @db variable value with your database name and @location with backup location.
set @db='Test'
set @location='D:\Backup\'
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:
This is the simplest way I can think of.
Good luck!
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
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'
NOTE:Set in the MOVE clause the correct logical names and new location.
ASKER
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 placeDo 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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you restricting only a solution based on backup/restore or you are available to accept other solution?