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.
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'
GOASKER
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.
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.
Do you realize that you'll always need to perform a full restore before applying the differential restore?
ASKER
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?
No differential, but incremental restore. Full then series of incremental restore from Sunday to Friday. Do you have scripts to assist for this post?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..
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..
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.
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.
ASKER
No I can't take down the db1 on SQL1. So log shipping is not the choice I could..
Did you think in a Replication solution?