...
Follow the steps below to create a SQL Server Agent job that restores a database from a backup file. The restore operation will create a new database if required - if it already exists it will be overwritten.
- In Microsoft SQL Server Management Studio, right-click on the "SQL Server Agent" node and choose "New → Job..."
- The "New Job" dialog will be displayed. In The "General" section enter details such as name and description.
- Go to the "Steps" section, and click on the "New..." button to add a step to the new job.
- In the "New Job Step" window that appears, enter step name, and the command text.
The command text is a T-SQL command that performs a restore from the file at "D:\SQLBackups\JiwaDemo.Bak" to a database named "JiwaDemo_QA". If a database named "JiwaDemo_QA" does not exist it is created, if it does exist, it is overwritten. The command text is given here for copy-pasting:RESTORE DATABASE JiwaDemo_QA
FROM DISK = N'D:\SQLBackups\JiwaDemo.Bak'
WITH MOVE 'JiwaDemo' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\JiwaDemo_QA.mdf',
MOVE 'JiwaDemo_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\JiwaDemo_QA_log.ldf';
GO - After clicking the "OK" button to close the "New Job Step" window from step 4, the "New Job" window, "Steps" section will now look like this:
Now proceed straight to the "Notifications" tab. We will not be executing this job on a schedule - rather we will call it directly from our backup job created in the first part of this how-to guide. For emailing to work, Database Mail must be configured on the server (see the how-to article on Configuring SQL Server Database Mail for more information). Further, one or more SQL Server "Operators" must be configured, such that they can be selected to receive the email (see the how-to article on Adding SQL Operators for more information). Remember that a SQL Operator may not represent a single person, but a group of people - as such the email address defined for the SQL Operator would be a mailing group rather than an individuals email address. That way, many people in the organisation will be alerted when the job succeeds or fails.
Info title Notify Upon Success The SQL Operator is notified when the job fails and also when succeeds. This is important. If the operator is only notified upon failure, then the absence of an email would indicate that the backup job completed OK - but this could be a false positive. If the email subsystem failed for whatever reason, the operator would not receive the email indicating that the restore job failed, and would therefore assume that everything was running OK when in fact it wasn't. Being notified that the the restore job succeeded indicates unequivocally that the restore job did in fact complete without error.
- After completing the "Notifications" section in step 9 above, click the "OK" button to complete the new job definition. The job will now appear under the "Jobs" node of the "SQL Server Agent" node of the SQL Server, thus:
- Now it is time to go back and edit the "Backup JiwaDemo Database" job - the "Backup JiwaDemo Database" job will have a step added to it to call the newly created "Restore JiwaDemo Database" job. Right-click on the "Backup JiwaDemo Database" entry under the "SQL Server Agent" node of the SQL Server and choose "Properties":
- The Job Properties window should now appear:
- Click on the "Steps" entry on the left to access the list of steps that make up the "Backup JiwaDemo Database" job, then select step 1 and click on the "Edit" button to edit the step.
- This will bring up the "Job Step Properties" window - click on the "Advanced" section on the left:
- Change the "On success action:" from "Quite the job reporting success" to "Go to the next step".
- Click on the "OK" button to return to the "Steps" section of the job. Click on the "New..." button to add a new step:
In the "New Job Step" window that appears, enter step name, and the command text.
The command text calls a job called "Restore JiwaDemo Database". Here is the text for copy-pasting into your command text box:
EXEC msdb.dbo.sp_start_job @job_name = 'Restore JiwaDemo Database';
GO- Click the "OK" button to close the "New Job Step" window. There are now 2 steps defined for the "Backup JiwaDemo Database" - the first step performs the actual backup, and the second step calls the "Restore JiwaDemo Database" job.
- After completing step 12 above, click the "OK" button to complete the "Backup JiwaDemo Database" job modification. A prompt will appear noting that the last step will have it's "On Success" action changed from "Goto Next Step" to "Quit with Success" - click "Yes" to accept this change.
- We can test by right-clicking on the "Backup JiwaDemo Database" entry under the "SQL Server Agent" node of the SQL Server and choosing "Start Job at Step...":
- Ensure that step 1 is highlighted, and then click the "Start" button on the "Start Job" dialog window:
- A progress dialog will appear. Double check that everything executed OK by looking at the date time stamp of the backup file in "D:\SQLBackups\JiwaDemo.Bak" (or whatever path you used in step 4 of creating the backup job). To double check that the database has been restored successfully, look at the restore history for the target database name (defined in step 4 of the creating the restore job) by running the t-sql script given below:
WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
[d].[create_date] ,
[d].[compatibility_level] ,
[d].[collation_name] ,
r.*,
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1
Related articles
| Filter by label (Content by label) | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...