In sql server, we have features called import and export data into text file or sql server instance using import export wizard. This feature is available from SQL Server 2000 on wards that makes DBA life little easy to handle data movement of particular database object like table, stored procedure and view etc.
Let’s go through the practical example to understand things more clearly. Let’s take a scenario, where you want to take the single table backup from sql server as a rollback option. This can be done by sql server import/export wizards for database.
If you don’t want to perform import export wizards again and again then you can create SSIS package in Sql server that will do your entire task in a single click. Also same can be scheduled on periodically to take backup of heavily transaction related tables on production server.
Let’s go through the steps involve in the task.
- Right click on Northwind database, select tasks from the context and click on export data option.
- It will display welcome screen of import export wizard click on next proceed, Here we have sql server as source so selected option is “SQL Native client”.
- Give the SQL Server name or IP address and select one option from Authentication.
- We can use either window authentication or SQL Server authentication, Window authentication takes windows credential to access Sql server. We are going with same credential for Sql server.
- If you select SQL Server authentication then you need to give the login and password to access server objects.
- Click on database drop down list, if your credentials are correct then it will give you the list of SQL Server database in the drop down. Click on next
- Here you have to choose destination path where you want to save your data. It can be flat file, excel files or other database from different server and repeat step 6.
- Click on Next
- Here you will see two options.
Copy data from one or more tables or views: – using this option you can copy the existing data from existing tables or views in the source database.
Write a query to specify the data to transfer: – This option will allow you to write an SQL query to manipulate or restrict the source data for the copy operation.
We are going with the default option of SQL Server
- Select the table or views which you want to export from source database to destination and click on next
- Here you will see the options like execute package immediately and save SSIS package either in SQL Server database or file system.
- If select SQL Server as option then it will save your in MSDB system database. To check the sentries you can fire the below statement
SQL Server 2005 packages are stored in the sysdtspackages90 table
SSIS packages get created with .dtsx extension.
select * from sysdtspackages90
sysdtspackagefolders90 –> Folder name
sysdtspackagelog –> DTS error log
sysdtspackages ->holds Information related to SQL server 2000 DTS packages
- If you select file system as your storage option then click on next to provide additional information related to package name and file location, wherever you want to save your DTS package.
- Click on next it will show you the details which provided in previous step, if you want change anything click back option and change it.
- Click on finish to complete the task.
If you have saved the dtsx file, now you can scheduled this using sql server agent by creating jobs. SQL server will run this based on timing assigned to it and generate backup of tables on your local drive.