Solved

SSIS and backup/restores

Posted on 2011-02-28
17
1,252 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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