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

Restore multiple Differential backups Expand / Collapse
Author
Message
Posted Thursday, April 02, 2009 7:23 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:46 AM
Points: 12, Visits: 35
Hi All,

I am backing up a production Database in FULL using SQL's own backup feature. I am then also doing differential backups every hour.

I want to restore these backs on a test server. I have already restored the full back on this test server. I have also restored one differential backup on the test server successfully.

Post doing this, I am not able to restore the next differential backup available from the Production server.

How can this be done? I need to keep the test server updated every hour.
Is there something I am missing?

Server's involved:
Production Server:- SQL2005 64x
Test Server:- SQL2005 32x

Thanks,
Anukul



-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles
Post #688857
Posted Thursday, April 02, 2009 7:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 41,495, Visits: 34,416
To restore more backups the database has to be in norecovery, which means it's not readable. Diff backups aren't really suitable for keeping a test server up to date. Have you considered replication?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #688870
Posted Thursday, April 02, 2009 7:58 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:46 AM
Points: 12, Visits: 35
GilaMonster (4/2/2009)
To restore more backups the database has to be in norecovery, which means it's not readable. Diff backups aren't really suitable for keeping a test server up to date. Have you considered replication?


I cannot setup replication as the production database is not in my control.
Can you provide a script to restore all differential backs hour after hour?

Also, can this be achieved by the SQL Management Console itself?

Thanks for all your help :)



-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles
Post #688917
Posted Thursday, April 02, 2009 8:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 41,495, Visits: 34,416
Anukul (4/2/2009)
Can you provide a script to restore all differential backs hour after hour?

No need. It's trivial. Add WITH NORECOVERY to all of your restored. However that means that the database on the test server is not readable.

What you are trying to do cannot be done with backups (leave a database read-write but still be able to restore further backups)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #688941
Posted Thursday, April 02, 2009 8:21 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:46 AM
Points: 12, Visits: 35
GilaMonster (4/2/2009)
Anukul (4/2/2009)
Can you provide a script to restore all differential backs hour after hour?

No need. It's trivial. Add WITH NORECOVERY to all of your restored. However that means that the database on the test server is not readable.

What you are trying to do cannot be done with backups (leave a database read-write but still be able to restore further backups)


That is exactly what I am trying to accomplish. I want the database readable, but updated. Also, I cannot setup replication on the production DB.
What is the best way out now?

Thanks a Lot!



-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles
Post #688952
Posted Thursday, April 02, 2009 8:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 41,495, Visits: 34,416
Restore from scratch each time.
Write a custom sync program or a rather complex SSIS package to do the same thing. This will have a non-trivial impact on production

Replication's the usual suggestion for this and it's the only easy way of doing it. Probably your best bet is to restore the full and then the latest diff. Doe the test server really need to be up to the hour accurate? If it can be as production was the previous night, then backup/restore is feasible.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #688971
Posted Thursday, April 02, 2009 8:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 41,495, Visits: 34,416
Double post. Sorry


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #688981
Posted Thursday, April 02, 2009 9:03 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:46 AM
Points: 12, Visits: 35
GilaMonster (4/2/2009)
Restore from scratch each time.
Write a custom sync program or a rather complex SSIS package to do the same thing. This will have a non-trivial impact on production

Replication's the usual suggestion for this and it's the only easy way of doing it. Probably your best bet is to restore the full and then the latest diff. Doe the test server really need to be up to the hour accurate? If it can be as production was the previous night, then backup/restore is feasible.


Thanks!
Can you in anyway help me in writing the Sync program or that complex SSIS package?

Also, if replication is the real solution, can it be setup from the test server rather than the production server? This way we will keep the production server untouched.

Yes, I need the test server updated with at-least one hour old updates.

Thanks.



-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles
Post #689023
Posted Thursday, April 02, 2009 9:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 41,495, Visits: 34,416
Anukul (4/2/2009)
Can you in anyway help me in writing the Sync program or that complex SSIS package?

I've just finished doing one for a client. It took me an entire week do do for about 1/4 of his database. I meant it when I said not simple. Plus it will have a significant impact on your production server.

Basically, for SSIS you'll need a data flow per table in the database. Each data flow will have a data source which reads the entire table off production, a lookup that checks test to see if it's a new row or an existing row, then a data destination (which is the table on test) for new rows, a second data destination (a table in a staging database) for updated rows. Then you need a SQL task for each table in the database that will update from that table in the staging DB to the table in the test DB.

For a standalone app, much the same thing. For each table in production you'd need to insert new rows into test, update changed rows and delete rows that are no longer in prod.

Also, if replication is the real solution, can it be setup from the test server rather than the production server? This way we will keep the production server untouched.

No. The publication has to be set up on the publisher.
Why can you not change production? Is there anyone you can speak to to try and get permission?

Yes, I need the test server updated with at-least one hour old updates.

Then your options are basically restore full backup plus latest diff ever hour, or spend a couple weeks writing a program to synchronise dev with prod. I would recommend the former as it's far less impact and far less work, but it does make the test server hard to use as all users will have to disconnect before the restore happens




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #689077
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse