Friday, May 18, 2012

Schedule automatic backup in SQL Server

To schedule a database backup operation by using SQL Server Management Studio in SQL Server, follow below steps:
  • Start SQL Server Management Studio.
  • In the Connect to Server dialog box, click the appropriate values in the Server type list, in the Server name list, and in the Authentication list.
  • Click Connect.
  • In Object Explorer, expand Databases.
  • Right-click the database that you want to back up, click Tasks, and then click Back Up.
  • In the Back Up Database – DatabaseName dialog box, type the name of the backup set in the Name box, and then click Add under Destination.
  • In the Select Backup Destination dialog box, type a path and a file name in the Destinations on disk box, and then click OK.
  • In the Script list, click Script Action to Job.
  • In the New Job dialog box, click Steps under Select a page, and then click Edit if you want to change the job parameters.
  • Note In the Job Step Properties – 1 dialog box, you can see the backup command.
  • Under Select a page, click Schedules, and then click New.
  • In the New Job Schedule dialog box, type the job name in the Name box, specify the job schedule, and then click OK.
  • Note: If you want to configure alerts or notifications, you can click Alerts or Notifications under Select a page.
  • Click OK two times.
You receive the following message:
The backup of database ‘DatabaseName’ completed successfully.
Note: To verify the backup job, expand SQL Server Agent, and then expand Jobs. When you do this, the SQL Server Agent service must be running.

No comments:

Post a Comment