Restoring Full and Differential backup daily with SQL

We have two SQL servers, one for production and one for reporting purpose.
SQL 1: production, SQL 2008
SQL 2: backup, SQL 2008
db1: database to be copied daily from SQL 1 to SQL 2

So we run full backup of db1 from SQL 1 at 12AM and restore db2 to SQL 2 every day. The problem becomes as the size of db1 grows, now almost 70GB, I experience disk error from time to time on SQL 2 which I guess it's too much writing activity on SQL 2.

Now, I am planning to change this from full restoring daily to full(saturday) and differential (Sunday to Friday) restoring.

I have a script for full backup restoring as below, which will be run only on Saturday.
I'm not really familiar with scripting SQL, so need help on scripting the differential backup restoring for Sunday to Friday.


-- Turn tracelog on for restoring db to Network path
DBCC TraceOn(1807)
GO

--Do Actual Restore
RESTORE DATABASE db1
FROM DISK = '\\netarch-1\ScheduledBackups\DB1_Backup\db1.bak'
WITH MOVE 'db1_uat_dat' TO '\\SQL2\DB1\db1.mdf',
MOVE 'db1_uat_log' TO '\\SQL2\DB1\db1.ldf', 
REPLACE

use db1
GO
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'db1App', 'db1App'
GO

Open in new window

LVL 1
crcsupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Now, I am planning to change this from full restoring daily to full(saturday) and differential (Sunday to Friday) restoring.
Why you chose this path? What do you pretend to win with this? I can't see any advantage on this.

Did you think in a Replication solution?
crcsupportAuthor Commented:
Victor, I explained it in the original post for the reason.

rewriting 70GB daily for full restoring gives overhead to the disk on SQL 2 which has low end SATA disk. Disk life gets short as I have to replace more often as the database grows.

Maybe I wrote too long. What I want for help is for SQL scripting for restoring differential backup to db1 in SQL2, not reasoning why I should do Full and differential restoring instead of Full restoring.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you realize that you'll always need to perform a full restore before applying the differential restore?
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

crcsupportAuthor Commented:
Vitor, You're right.
No differential, but incremental restore. Full then series of incremental restore from Sunday to Friday. Do you have scripts to assist for this post?
ZberteocCommented:
You CANNOT restore incremental. Once you restored a database with recovery so it can be actually accessed you CANNOT restore to it only the differential since last restore. The only way to restore is to use FULL+DIF+LOG all at the same time. So what you try to do is impossible.

The closest way to an incremental restore idea is using log shipping but even then you will have moments when the database is NOT accessible.

https://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/

The best way to have a reporting copy of your database is to use transactional replication . The database is kept constantly up to date, or how often you want/need, and it will be accessible at any time.

http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/


You could also use the latest technology like availability groups with Always On but you need Enterprise editions on both sides.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crcsupportAuthor Commented:
Zberteoc, Thanks for the comment. Lots of information..
Allow me understand correctly. After I restored full with recovery to SQL 2 and operational, I cannot restore incremental or differential?
So, it seems the only possible way is by replication..
ZberteocCommented:
That is correct. Every time you do a restore you HAVE to include the FULL backup. The rest is optional...

Only the log shipping can apply the incremental restores but like I said it renders database not accessible while doing that. You could tweak that process to only happen during night, like your initial intention was but I would say replication is preferable.
crcsupportAuthor Commented:
No I can't take down the db1 on SQL1. So log shipping is not the choice I could..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.