[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1261
  • Last Modified:

SSIS and backup/restores

Server1 has the database.
It has to restored to Server2 everyday at a particular time.

I can run a command from Server1 to backup to the shared folder that Server 2 also has access to. Then I can run a command from Server to restore to the database name desired.

With plain t-sql job, i am not seeing a way to do this in one process/job. With SSIS, can i accomplish the above in one sitting.?

thanks
0
anushahanna
Asked:
anushahanna
8 Solutions
 
AnujCommented:
This approach is okay for small databases, what if the database is too large? its take considerable amount of time for the back up and restore process. In this situation i will go for any of the following Snapshot replication, or mirroring or log shipping.
0
 
ModuleKevCommented:
I'd backup locally, copy the backup over, then restore.

The backups I'd handle with normal maintenance plans. Then have an SSIS package check a share on the server to see if a new backup file exists at a/between certain times, copy the backup file over then do the restore.

BUT It all depends on what you want to do on Server 2.
0
 
ModuleKevCommented:
We only tend to do this approach for batch boxes with db's less than 10GB...

Wouldn't try it on a 75GB DB everyday.

More info required as to what you're going to be requiring.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
anushahannaAuthor Commented:
>>I'd backup locally, copy the backup over, then restore.

how would you automate it? how will server2 know that the backup copy is done and available and it should start restore now.

>>check a share on the server to see if a new backup file exists
During the copy, the file would seem to exist; would it know when it is actually done copying?

the main desire it to automate everything a-z.
ie. Backup from Server1 to Shared folder of Server2
Server2 picks up the backup file and does the restores, and does not updates to the tables based on logic already existing and will be available as a script.

I do not see how this can be done through one SQL job.

the DB is 8GB.
0
 
anushahannaAuthor Commented:
sorry,.. meant "does updates to the tables "
0
 
ModuleKevCommented:
What is server 2 serving? Is this something that will happen in the middle of the night?

We've got backups here that run as 2am, we know take 30 mins. Then have a job on the other server that pulls them over at 3am, if no files retry after 20 mins, if error retry after 20 mins.

It would be possible to put it all in one package if the executing user has permissions to both machines to the levels required.

Create a new SSIS package. Create a Backup Task from Maintenance Plan Tasks, then use a File System Task to move/copy file then execute a SQL Task to restore the DB, all linked with "On Success" on workflow.

This would be an out of hours task. I wouldn't use the process for high availability.

P.S. Log shipping won't be an option unless you want to use it as part of a recovery model, unless you have Enterprise Edition so could do database snapshots of the effectively offline db, and be able to perform select queries.
0
 
anushahannaAuthor Commented:
yes, it will run midnight.

>>P.S. Log shipping won't be an option unless you want to use it as part of a recovery model, unless you have Enterprise Edition so could do database snapshots of the effectively offline db, and be able to perform select queries.

i did not follow the above.. are you recommending http://msdn.microsoft.com/en-us/library/ms175158.aspx from one server to another?
0
 
anushahannaAuthor Commented:
we do have enterprise ed
0
 
anushahannaAuthor Commented:
the one problem i have with the ssis approach is Backup DB Task does not give me option to give a fixed name for the backup file. it gives its own name with date suffix.. that does not help me to tell the restore part to know what is the backup file name?

is there a way to have it accept the name I give it?
0
 
reb73Commented:
You can just choose a generic SQL Execute task and use the BACKUP transact-sql statement to get around Backup DB Task limitation in terms of file names
0
 
ModuleKevCommented:
No, was in reference to anujnb. It all depends on what you want Server 2 to do

What does Server 1 do and what Server 2 is expected to do. Is it just a batch box, do you wish to use it in the event of a failure? Does Server 2 have users connecting to it? Also what version of SQL have you got? What downtime can you afford? Do you need high availability?

The short answer to your question is Yes, you can use SSIS to do a backup on one machine and a restore on another as I've put above. Is it the right choice for your purposes? I've no idea.
0
 
anushahannaAuthor Commented:
reb73, thanks for the workaround..
0
 
anushahannaAuthor Commented:

ModuleKev, server2 is for reports and 1 for transactions. both are sql 2008r2. downtime is 30 minutes that is agreed upon and expected.
0
 
ModuleKevCommented:
Ok good.

Log Shipping & Database Mirroring - Yeah could do with snapshots... probably not the nicest. Also when you do a new snapshot, your report users must be off the db..
Replication - Yes
Simple backup and restore - Yes, provided you can backup and restore within your time frame.

So if this is for reporting, are users ok with data being a day out if you're only doing it at midnight? How much does your data change in a day? If they need up to date data, then you prob should consider replication and forget the SSIS route.

A bit about replication here:
http://msdn.microsoft.com/en-us/library/ms151784.aspx

There's a lot more out on the web.
0
 
anushahannaAuthor Commented:
yes, delay of 1 day in report data is acceptable for business. so replication probably is not needed in this case.

Log Shipping & Database Mirroring & Snapshots - all come under the category of replication?
0
 
ModuleKevCommented:
They are different ways of maintaining high availability, along with Clustering, i.e if one server goes down, you're not screwed. SQLServerCentral has some good topics on.

On the SSIS side, you could always check the folder on Server1 to find the latest file with file name like MyBackup%, then copy it over - i.e if you're doing a full backup at 11pm every night, may be no point doing another for 12pm. If it can't be done using one of the existing objects then a Script task will be able to do it. Or like reb73 says use a custom sql script. There's many ways.

0
 
anushahannaAuthor Commented:
very helpful - many thanks!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now