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
--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',
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'db1App', 'db1App'