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
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
Hello - I am not a DBA, let's start with that...but I have acquired the task of backup up a db and copying it to a different server. I've been reading the BOLs on backup (haven't even GOTTEN to recovery) and I am confused by the plethora of info. So, here's the story - we have a db which was upgraded and moved to a different server (server B). I managed to create a linked server where it used to live (server A) so that reporting which pointed to tables on that db would still be able to access those tables. We found out the hard way that you can't use functions across linked servers (well, you CAN, but it's difficult) - anyway, we had a "reporting" version of that db which has not been updated since January, so we did a temporary measure of pointing the functions to that reporting version on server a and the tables to the actual tables on server b. 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. That way, we have our reporting version which is only 24-hours old. I'm reading about the back-ups, and I don't know if I should be doing a copy-only or what. I don't want to just execute the backup task in ssms without a better idea of what I'm doing and if anything I do can hurt the production db on server b. Seems like a backup would be just a read of the db, but some of the verbiage in the BOL makes me question that. Also - do I have to have the logs, as well?

Sorry for the total ignorance...this greatness has been thrust upon me, as it were. I appreciate any guidance/input/knowledge sharing! Even a plain and simple tutorial to read would be tremendously helpful.

Thank you --

Donna
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32525 Visits: 18556
You can do a copy only backup or a regular full backup. If you are doing differential database backups on that server, then you would want to do a copy only backup.

Otherwise, you should be safe doing the regular full backup.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40156 Visits: 32653
Jason has it right. Unless you're running differentials, you can just run a regular full backup.

Since you're just getting going, you might want to check out Shawn McGehee's book on backups. It should cover just about everything you need. It's free to download. I've got a number of articles on Simple-Talk about backups too.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40156 Visits: 32653
Forgot about this article completely. It's got the same title as your question, part of why I alerted to this one.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86650 Visits: 41098
dbursey (5/5/2013)
That way, we have our reporting version which is only 24-hours old.


IF your server storage is on a SAN and the main server and the reporting server are on the same SAN, ask the SAN folks if they could setup an automatic SAN "snapshot" for you. The update time every night would be measured in seconds or less.

If not on a SAN or the particular SAN doesn't have that capability, then you might want to consider "replication" which will do most everything automatically and could be used to keep the reporting database in sync up to the minute.

Of course, the backup/restore method DOES test the production backups for viability and that's a major warm-fuzzy.

--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
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
Hello, all - thank you for your replies! What a lot of great information and resources! Just to get my feet wet, I tried to copy the database backup file from server A over to server B and then use that file to restore on server B. Guess what? The servers are incompatible because server B is a lower version than server A. No restore for you! Sigh....so, I'm thinking that either a server upgrade is in order, OR I'd better start looking at those work-arounds for executing functions across servers. But meanwhile, I'm going to be reading all that great material (Grant, I especially like the Accidental DBA article you found - that is perfect!).

Thanks again, good people!

Donna
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40156 Visits: 32653
dbursey (5/16/2013)
Hello, all - thank you for your replies! What a lot of great information and resources! Just to get my feet wet, I tried to copy the database backup file from server A over to server B and then use that file to restore on server B. Guess what? The servers are incompatible because server B is a lower version than server A. No restore for you! Sigh....so, I'm thinking that either a server upgrade is in order, OR I'd better start looking at those work-arounds for executing functions across servers. But meanwhile, I'm going to be reading all that great material (Grant, I especially like the Accidental DBA article you found - that is perfect!).

Thanks again, good people!

Donna


Thank you!

But I didn't find it, I wrote it.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
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
Grant Fritchey (5/16/2013)
dbursey (5/16/2013)
Hello, all - thank you for your replies! What a lot of great information and resources! Just to get my feet wet, I tried to copy the database backup file from server A over to server B and then use that file to restore on server B. Guess what? The servers are incompatible because server B is a lower version than server A. No restore for you! Sigh....so, I'm thinking that either a server upgrade is in order, OR I'd better start looking at those work-arounds for executing functions across servers. But meanwhile, I'm going to be reading all that great material (Grant, I especially like the Accidental DBA article you found - that is perfect!).

Thanks again, good people!

Donna


Thank you!

But I didn't find it, I wrote it.


Even better! :-D (I noticed that after I posted.)
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1901 Visits: 2729
Jeff Moden (5/6/2013)


IF your server storage is on a SAN and the main server and the reporting server are on the same SAN, ask the SAN folks if they could setup an automatic SAN "snapshot" for you. The update time every night would be measured in seconds or less.


If you use snapshots, be very careful to work with the SAN folks so the SAN doesn't run out of whatever space it uses to store the snapshot differentials, particularly on an active database.

A snapshot means the SAN has to keep track of the state at the point in time of the snapshot, plus the actual current state of the source data. On write capable snapshots, it also has to keep track of the "changes" written to the "snapshot". For what it's worth, it appears that SANs are more likely to keep track of the old data in the snapshot storage area, while virtual hypervisors are more likely to keep track of the new data in the snapshot storage area.
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
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.
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