May 5, 2013 at 11:39 am
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
May 5, 2013 at 6:05 pm
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[/url]
Learn Extended Events
May 6, 2013 at 5:19 am
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[/url]. It should cover just about everything you need. It's free to download. I've got a number[/url] of articles [/url]on Simple-Talk[/url] 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2013 at 5:22 am
Forgot about this article[/url] 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2013 at 5:43 am
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
Change is inevitable... Change for the better is not.
May 16, 2013 at 7:11 am
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
May 16, 2013 at 7:51 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2013 at 9:06 am
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.)
May 20, 2013 at 12:18 pm
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.
May 20, 2013 at 1:48 pm
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.
May 20, 2013 at 2:21 pm
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
Change is inevitable... Change for the better is not.
May 20, 2013 at 3:39 pm
Not sure if I replied on your post Jeff.
It was meant for the creator of the thread.
May 20, 2013 at 6:35 pm
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.
May 22, 2013 at 3:35 am
For server level upgrade u can find SQL utilities which do the same quite easily . It will save ur time a lot
May 22, 2013 at 5:16 pm
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?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply