Rade_ (5/20/2013)

---

If you end up upgrading server B you could create a SSIS package that automates this process for you.

It would contain a couple of tasks and I will try do describe them for you.

Not sure if you know anything about SSIS but it might give you an idea on an easy solution.

Scenario:

- Server A dumps full database backups to disk every night.

Tasks included in SSIS package.

1. File Watcher task.

This is a free downloadable task that is not included in BIDS.

This task will watch for your backup file (that you define) in server A backup directory and when the backup of your database is completed, it will continue to the next task. (The value of this task is that it waits for file to complete instead of triggering on file created)

Basically you start this task on server B before the backups start on server A and it will wait for the backup of the database you want on server A to complete. This way you can use existing backups on server A and you do not need to configure server B to run at a specific time to get the backup file, just a schedule package to run hour prior backup on server A and let it run for x amount of hours.

2. File system task.

When backup of your database is done and task 1 detected it, next task is just to copy the file from Server A to server B.

Since this package will be scheduled by SQL agent on Server B, the agent account needs read rights on Server A backup location to be able to copy the file.

3. Execute SQL task.

When task 2 is finished the next step is to restore the database.

You can just do a manual restore of the database and before you press ok just script it out and

use the generated code in this task.

Before the actual restore begins you should add some code where all transactions are rolled back on server B and the database is set to single user mode so that the restore does not fail.

4. Execute SQL task.

You could add an extra task if you need to add new users to your database in Server B if they do not exist in server A.

It might look like a lot of work but once you do this, you can reuse the package the next time you need similar tasks done.