Solved

SSIS and backup/restores

Posted on 2011-02-28
17
1,247 Views
Last Modified: 2013-11-10
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
Comment
Question by:anushahanna
17 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 63 total points
ID: 35003698
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
 
LVL 3

Assisted Solution

by:ModuleKev
ModuleKev earned 375 total points
ID: 35005064
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
 
LVL 3

Assisted Solution

by:ModuleKev
ModuleKev earned 375 total points
ID: 35005076
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35007000
>>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
 
LVL 6

Author Comment

by:anushahanna
ID: 35007006
sorry,.. meant "does updates to the tables "
0
 
LVL 3

Assisted Solution

by:ModuleKev
ModuleKev earned 375 total points
ID: 35007145
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35007691
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35007699
we do have enterprise ed
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Author Comment

by:anushahanna
ID: 35007719
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
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 62 total points
ID: 35007869
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
 
LVL 3

Assisted Solution

by:ModuleKev
ModuleKev earned 375 total points
ID: 35007900
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35008223
reb73, thanks for the workaround..
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35008226

ModuleKev, server2 is for reports and 1 for transactions. both are sql 2008r2. downtime is 30 minutes that is agreed upon and expected.
0
 
LVL 3

Assisted Solution

by:ModuleKev
ModuleKev earned 375 total points
ID: 35008707
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35010067
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
 
LVL 3

Assisted Solution

by:ModuleKev
ModuleKev earned 375 total points
ID: 35015162
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35015701
very helpful - many thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now