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 Sunday, May 5, 2013 11:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:17 PM
Points: 34, Visits: 161
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
Post #1449540
Posted Sunday, May 5, 2013 6:05 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:08 PM
Points: 17,967, Visits: 15,975
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
Post #1449551
Posted Monday, May 6, 2013 5:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 14,029, Visits: 28,404
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1449661
Posted Monday, May 6, 2013 5:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 14,029, Visits: 28,404
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1449663
Posted Monday, May 6, 2013 5:43 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
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."

(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 #1449673
Posted Thursday, May 16, 2013 7:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:17 PM
Points: 34, Visits: 161
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
Post #1453506
Posted Thursday, May 16, 2013 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 14,029, Visits: 28,404
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1453547
Posted Thursday, May 16, 2013 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:17 PM
Points: 34, Visits: 161
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! (I noticed that after I posted.)
Post #1453609
Posted Monday, May 20, 2013 12:18 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:11 AM
Points: 889, Visits: 2,459
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.
Post #1454674
Posted Monday, May 20, 2013 1:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 5:29 AM
Points: 51, Visits: 424
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.





Post #1454718
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse