April 2, 2009 at 7:23 am
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
[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]
April 2, 2009 at 7:32 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 7:58 am
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 🙂
[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]
April 2, 2009 at 8:13 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 8:21 am
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!
[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]
April 2, 2009 at 8:33 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 8:39 am
Double post. Sorry
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 9:03 am
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.
[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]
April 2, 2009 at 9:45 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply