Summary:
1. Backup a database into many files
2. Separate tasks to move files across the network
3. Gain exclusive access to the database prior to restoring
4. Restore database
5. Security
6. Import to SQL Server
7. Proxies
8. Scheduling Job
1.
1. Create a Back Up Database Task and use as many files as you need (64 Max) to transfer across the network .
Create a standard file structure to match. Select option If backup files exist Overwrite to conserve space.
2.
2. File System Task
Configure the destination to use Existing folder, then browse to location
Select ok
To avoid space issues, select Move file
Then configure your Source Connection
Usage type will be Existing file and Browse to the file you are moving
Create an Execute T-SQL Statement task for each file you are moving
3. Gain exclusive access to the database prior to restoring
Execute T-SQL Statement task allows you to script the action you want
I chose these to take the database off line and put back on line ( I chose this method to disconnect any users on the db)
Add the syntax you want executed
4. Restore database
General tab configure your connection
SQL Statement configure the T-SQL Syntax to restore the database
5.
5. Security
Use Execute T-SQL Statement Task to add user accounts to the database , or drop an orphan user
6. Import SSIS Package to SQL Server
Connect to Integration services and right click on the File System folder
Select File System from the package location
Navigate to the package to import
NOTE: depending on your security and business requirements you may need to import the package to the MSDB folder and select he SSIS Package Store. This example is for File System.
The security of the package properties is set to Do not save sensitive data. The other options require additional steps when creating the package.
7. Some environments need a proxy to run SSIS job in SQL Server Agent the following demonstrates setting up a proxy.
Create a new Credential . If you use a network account you need to type in the network password
The account needs to have credentials to perform all the tasks created in the package and have SA Rights to run the Job in SQL Server Agent
Create a Proxy account for SSIS Package Execution
Cre
8. Create a SQL Server Agent Job
In SQL Server Agent you will schedule your job running as SSIS Proxy
This is found in the steps created to run the job.
On the general name the job and click on the Steps option
Change the run as to the new Proxy you created, Package Source to File System and browse to where you imported your package from the previous steps.