SQL Server Backup

By this procedure we will be creating a scheduled backup for SQL Database. Before starting this procedure verify that SQL Server and SQL Server Agent Services for this instance are running.

1.     Logon to SQL Server Management Studio with administrator rights.

2.     In Object Explorer, click + near server and expand the subitems. Click + near Management to expand subitems.

3.     Right click on your Maintenance Plans and select Maintenance Plan Wizard.

4.     Click Next on the Starting Page.

5.     On Select a Target Server Page set the Name of the job as Backup – <databasename>.

6.     Verify the Servername is set to your local server and Use Windows Authentication is selected. Click Next to continue.

7.     On Select Maintenance Tasks Page, select Back Up Database (Full) and to continue click Next.

8.     On Select Maintenance Tasks Page, click Next.

9.     On Define Back Up Database (Full) Task Page, to select your database click to dropdown button on Databases Field.

10.  Select your database on the selection page and click OK to go back to Maintenance Plan Wizard.

 11.  Set Backup Component as Database, Destination as Disk. Select Back up database across one or more files and click Add.

12.  On Select Backup Destination Page Set the filename as <DataDrive>:Program FilesMicrosoft SQL ServerMSSQL.#MSSQLBackup<databasename>.bak and OK to go back to Maintenance Plan Wizard.

 13.  Select If backup files exist option as Overwrite and Click Next to continue.

 14.  On Select Plan Properties Page Click Change.

15.  Give a name to your Schedule and select a desired schedule for your Backup Job on non-working hours. Click OK to go back to Maintenance Plan Wizard.

 16.  On Select Plan Properties Page check your schedule and click Next to continue.

17.  On Select Report Options Page check Write a report to a text file and set the folder as <DataDrive>:Program FilesMicrosoft SQL ServerMSSQL.#MSSQLLOG and click Next to continue.

18.  On Complete the Wizard Page verify the choices you have made and click Finish to set the job.

19.  On Maintenance Plan Wizard Progress Page check that all actions are completed with success and click close to exit.

20.  After Setting up the schedule for backup, inform you backup administrators to take the backup of the folder <DataDrive>:Program FilesMicrosoft SQL ServerMSSQL.#MSSQLBackup parallel to your backup schedule. The file backup job has to be scheduled sometime later than the SQL backup (e.g. 1 hour) to be sure that the SQL backup is finished.