With the course of time, you might need to backup all the databases within
a server instance automatically and schedule to backup these database monthly
wise or according to your specific time set.
Also, what I want to say is that ,I cannot stop myself to write this
another blog of mine.
writing a blog and sharing some knowledge through the blog makes us really
feels happy and also somewhat we also become addicted to it. :)
This may be simply for one but for someone it will be helpful for those who
don't know how to do it.
May be there are a lot of alternative methods to perform this task and many
But I have researched a lot regarding to backup all the SQL database as
well the SSAS catalogs database backup with in a particular server instance.
Here ,I am going to show how to obtain the SQL database backup
automatically along with schedule time set.
Firstly, I have created a SSIS package where I used simply “Back up
Database Task “ from toolbox and click right then clicking in edit tab, we can set the connection as show in figure1
Then we can set the
Backup Type as full or other type after that you can select the databases to
backup as show in figure2 below where we can set required databases to backup.
After that you can set the other different properties like
1.Backup set Expire
3.Append or overwrite if backup exist
4.for everyday backup options
5.Backup location directory currently I set to default location.
6.allow compression or not.
You can figure out by
looking the figure3 below.
Now all you need to create a job agent to schedule the backup task and
run SSIS package. In order to do this you can check out the link by Brian Knight
or simply go to SSMS and start Sql Server Agent and right click and
create new job where you have to setup some properties as shown in figure4
Here you can schedule the backup
going into the schedule tab and then just check the job working or not by right
clicking it and Start job at step then
you can check the all the backup database within the default location as : C:\Program
Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup
Hope you get some ideas to obtain
the schedule Sql databases backup automatically ,also I am thinking to post into
my another blog for “ Automate and schedule all SSAS catalogs database backup with
in a server instance by using Job Agent” or similar approach which have taken
most of my research time and I am happy to obtain the result successfully.
Hope, this may be simple but I think it may work out for someone .