Scheduling can be done through the aid of a Maintenance Plan which will be recurrent. Another step which should be recurent is to discard (remove) the much older backups from your backup location.
For starters, log into SQL Server Management Studio and connect to the database. Next go to the Object Explorer window (left side) and make sure that your SQL Server Agent is running, otherwise right click its icon and start it.
Expand the folder named Maintenance and select the Maintenance Plan node. In order to be able to schedule such a plan you will need to have already SYSADMIN database role, otherwise it is possible you won’t see the Maintenance node.
In case you don’t see it, please check permissions and/or ask how you can get access to it. But if you see the Maintenance node in the folder right-click it and select New Maintenance Plan.
Enter the name of the Maintenance Plan you are going to create.
The next page that you will see is the configuration page of your newly created maintenance plan. There are two sections you will need to look over when creating your maintenance plan, they are highlighted below.
The section that is marked at the top right of the image is the one you’re going to use to set up the time for when the plan will execute. Make sure you use a time when the database is least used.
The section in the bottom left of the image is the part where you choose what type of action will occur during the maintenance plan. You can either create your own T-SQL script to do the backup operation with (T-SQL Statement Task) or choose the Back Up Database Task for the standard walkthrough.
Start by clicking on the calendar icon on the top right highlighted section, which will bring up the job schedule screen, where you will set up the time when the job will run, the frequency and other details that can be seen below.
Be sure to set up dates and times that best suit your organization’s requirements. Most organizations do daily backups of their database and they do it when their database is least used. Continue by clicking OK when you’ve set up all the details.
From the bottom left side of the screen choose the Backup Database Task to easily set up the backup process, and drag the element onto the right window as you can see below.
Next up you should double-click on the database task, which will open up a new window where you will be able to set up the configuration related to the database backup.
First you should start by selecting the databases you want to back up from the dropdown list. You can also select the system databases to backup at this point, which you should not forget about or ignore for long if you decide not to select them in this plan.
After selecting the databases you want to backup you can pick if you want the files to expire after a certain number of days or on a specific date.
Also choose where the backup file (.bak file) will be created and what to do in case a backup file with the same name exists (Append or Overwrite).
Take a look over all other available options and after you selected the best combination of options that will meet your requirements click OK.
This completes the backup plan configuration and the backup files will be created at the time specified and in the folder specified by you.
Now, because backups are done frequently, it is a good practice to remove (delete) your older backups after a certain amount time. For this, a cleanup task would be the best way to do it, along with the maintenance plan.
Open Notepad write below query into the notepad and save as .bat, it will create a batch file
ECHO OFF
cls
ECHO ***Vijay Sharma==>>Please Wait We Are Taking SQL backup======>>................
Robocopy "C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
After Saving Batch file Open server task scheduler set the time attach this batch file into the scheduler. You can use Network disk in this query.
Trick:- Same as task scheduler add one more Zip/rar exe file to make a zip/rar of your backup file after that run backup batch file.
FTP Trick:- Create FTP on Server and run this backup batch file drop your zip/rar backup file into FTP folder, you can download your latest backup on ftp server ;-)
No comments:
Post a Comment