Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Backup and recovery for the accidental DBA Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 2:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
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.


For the kind of databases I work with, that would take hours.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1454729
Posted Monday, May 20, 2013 3:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:52 AM
Points: 51, Visits: 398
Not sure if I replied on your post Jeff.

It was meant for the creator of the thread.
Post #1454765
Posted Monday, May 20, 2013 6:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:17 PM
Points: 53, Visits: 161
raadee (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.







Ah, excellent info-very smart way to go. We'll see when the service pack (or whatever-but my train buddy dba friend said he thought it was a srvice pack) gets applied. They need to research impact. These are the things that happen when you think laying off the dba is a good idea. Anyway...this is great, and i appreciate the detail.
Post #1454782
Posted Wednesday, May 22, 2013 3:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 8:55 AM
Points: 51, Visits: 27
For server level upgrade u can find SQL utilities which do the same quite easily . It will save ur time a lot
Post #1455387
Posted Wednesday, May 22, 2013 5:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:17 PM
Points: 53, Visits: 161
subhajeetsur (5/22/2013)
For server level upgrade u can find SQL utilities which do the same quite easily . It will save ur time a lot


Do you mean a utility which tells you what impact the upgrade will have?
Post #1455760
Posted Wednesday, May 22, 2013 11:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 8:55 AM
Points: 51, Visits: 27
yes if ur upgrading from SQL Server2005 to SQL server 2008 or SQL server 2008R2

Below is some links which can be of help:

http://www.scalabilityexperts.com/tools/downloads.html

http://msdn.microsoft.com/en-us/library/ms144256.aspx

http://msdn.microsoft.com/en-us/library/cc879337.aspx
Post #1455808
Posted Friday, May 24, 2013 5:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:17 PM
Points: 53, Visits: 161
subhajeetsur (5/22/2013)
yes if ur upgrading from SQL Server2005 to SQL server 2008 or SQL server 2008R2

Below is some links which can be of help:

http://www.scalabilityexperts.com/tools/downloads.html

http://msdn.microsoft.com/en-us/library/ms144256.aspx

http://msdn.microsoft.com/en-us/library/cc879337.aspx


Thanks very much!
Post #1456393
Posted Friday, May 24, 2013 3:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
raadee (5/20/2013)
Not sure if I replied on your post Jeff.

It was meant for the creator of the thread.


Ok... if the creator of the thread has databases like mine, that method will take hours.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1456704
Posted Monday, May 27, 2013 4:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:52 AM
Points: 51, Visits: 398
From the first post:
" So...the next step is to create a backup of the db on server b and restore a copy on server a which will run as a scheduled job every night"

Based on that info i suggested the ssis/copy/restore full-diff method.
I do understand that it would not have been the proper solution if the database was very large or the timeframe was restricted.
Post #1457035
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse