1

I have a server with SQL Server 2008 R2 installed in it. This server is used as a D -1 Database, in other words, the database it is one day behind from production server.

The problem is that my production database has more than 300GB and the daily restore is becoming an issue for the team who needs this server, because the job isn't finished before the morning day.

Is there some solution that replace the daily full backup, some kind of replication with delay ?

Improve this question
9
  • 1
    SQL Server Replication , Log shipping is another option.
    – M.Ali
    Oct 1, 2014 at 20:57
  • You might look into LiteSpeed for your backup/restore. Much faster and you can do table by table restores too.
    – Steve
    Oct 1, 2014 at 22:22
  • 2
    What sort of activity will you have against D-1? Will it be read-only, read/write or purely as a warm standby for DR purposes?
    – Michael Green
    Oct 2, 2014 at 0:32
  • 1
    If its read/write, then replication might be your best bet. You can use snapshot replication since you'll be changing data at the destination, then you just schedule the snapshot during off hours.
    – Nabil Becker
    Oct 2, 2014 at 4:07
  • 2
    Some people, when confronted with a problem, think “I know, I'll use replication.” Now they have two problems.
    – Mark Storey-Smith
    Oct 2, 2014 at 18:59

2 Answers 2

Reset to default
1

Instead of restoring a daily full backup, you could:

  1. Take weekly full backups of production, restoring them to the D-1 server the next day.
  2. Take daily differential backups of production, restoring them to the D-1 server the following day.

The daily restores from the differential backup will be much faster than restoring the entire database each day.

I'd advise against using replication for this; as @Mark Storey-Smith said, you will now have two problems to manage instead of one.

Improve this answer
2
  • How I will restore a daily differential backup without the full backup ?
    – Jose Rocha
    Oct 6, 2014 at 14:08
  • You'd restore the full backup once per week. You really don't need a full backup daily.
    – Hannah Vernon ♦
    Oct 6, 2014 at 14:27
0

A software free solution would be to speed up your backups. I sped my backups up dramatically by specifying multiple targets for the the backup. You can experiment with how many to use to get best results. For a database that large, I use 20 backup files.

BACKUP DATABASE (DBName) TO DISK = (Path to file 1), DISK = (Path to file 2), .

You might also look at what else is going on during the time of your backup, if other backups are firing off to the same place at the same time or not.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.