Database Administrations can create database maintenance plans either by using the Maintenance plan wizard or by using SSIS designer. By using the Maintenance plan wizard a very basic maintenance plan can be created for acc the system and user databases. However, to create an enhanced workflow it is advised to create a maintenance plan using SSIS designer
Back up database task different types of database backups to be performed; such as full, differential, or Transactional log backups based on the Recovery Model of the system or user databases. To learn more about the different recovery models in SQL Server refer to the article titled “Database Recovery Models in SQL Server”.
Check Database Integrity Task can be used to check the allocation and structural integrity of all the user and system tables within a database. This task also has an option to check the allocation and structural integrity of all the indexes available within a database. The task internally executes the DBCC CHECK DB Statement.
Execute SQL Server Agent Job Task can be used to run SQL Server Agent Jobs that are created on the SQL Server Instance.
Inclined to build a profession as MSBI Developer? Then here is the blog post on, explore MSBI Training
Execute T-SQL Statement Task can be used to execute some Transact SQL queries against databases. This task is only available when creating Maintenance plans using the SSIS designer.
The Transfer Database Task is used to move a database to another SQL Server instance or create a copy on the same instance (with different database names. This task works in two modes as shown below:
In this mode, the source database is detached from the source server after putting it in a single network location. On the destination server the copies are taken from the network location to the destination server and then finally both databases are attached on the source and destination servers. This mode is faster, but a disadvantage with the mode is that the source database will not available during copy and move operation. Also, the person executing the package with this model must be a sysadmin on both source and destination instances.
In this mode, the task uses SMO to transfer the database objects to the destination server. In this mode, the database is online during the copy and move operation, but it will take longer as it has to copy each object from the database individually. Someone executing the package with this mode must be either sysadmin or database owner of the specified databases.
Drag the Transfer “Database Task” from the Toolbox (which is normally on the left side) to the Control Flow pane. Right-click on the task and select Edit… as shown below.
In the “Transfer Database Task Editor, “ select Databases on the left and now you are ready to configure this task. Source connection is the property to specify the connection for the source SQL server instance, if you have already created a connection manager then one as shown below. This will also need to be done to configure the Destination connection property as well.
Next, you need to specify the values.
Source connection --- the source instance
Destination connection --- the destination instance
Destination Database Name --- name of the new database
Destination Database Files ---Name and location of the database file
Destination Overwrite ---If the database already exists on the destination server it will give you an error, so if you want to overwrite the destination database you can set this property to true.
Action - Whether you want to copy or move a database.
Method - Whether you want the copy and move operation to be offline (in this case you also need to provide the network share name which will be used to transmit the database files).
Source Data baseName - the name of the source database
Source Database Files - name and location of the database files
Reattach source Database -is another property that you can set to TRUE to Reattach the source database after the copy operation.
Once you are done with all these configurations you can hit F5 or click on the play icon to execute the package. Your task will turn yellow during the execution and then either red or green depending on the execution outcome. You can go to the progress tab to see error messages if the execution fails. Although failure is rare, it is possible if your source database is smaller than the size of the model database on the destination server.
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
