SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup and recovery for the accidental DBA


Backup and recovery for the accidental DBA

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87130 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
raadee
raadee
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 476
Not sure if I replied on your post Jeff.

It was meant for the creator of the thread.
dbursey
dbursey
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 184
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.
subhajeetsur
subhajeetsur
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 27
For server level upgrade u can find SQL utilities which do the same quite easily . It will save ur time a lot
dbursey
dbursey
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 184
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?
subhajeetsur
subhajeetsur
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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
dbursey
dbursey
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 184
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!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87130 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
raadee
raadee
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 476
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search