Restore DB

Question

Restore DB

ramyours2003

SSChampion

Points: 12266
More actions
May 20, 2019 at 9:27 am

#3645261
what is the procedure to restore a database of size 2 TB with a minimal down time ?
- This topic was modified 4 years, 11 months ago by Dave Convery.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

In what sense? Are you restoring over an existing database, or creating a new one. If the latter, there is no downtime, as such. If an existing one, I assume that the existing database won't be updated during the restore period, as it would be lost. If so, you could restore the database under a different name, then, once the restore is complete, rename the old database and then rename the new one (to the old one's name).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ramyours2003 SSChampion Points: 12266 More actions · Answer 2

we need to restore to a new db on other servers , generally how can we achieve in a shorter time not configuring any high availability techniques

Andrey Right there with Babe Points: 780 More actions · Answer 3

have a look

https://www.mssqltips.com/sqlservertip/4935/optimize-sql-server-database-restore-performance/

Andrey Right there with Babe Points: 780 More actions · Answer 4

also, still actual

https://www.red-gate.com/simple-talk/sql/backup-and-recovery/faster-restores-best-practices-to-increase-speed/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Then, restore all the main BAK and all the existing TRN files to the new server but make sure that ALL of those restores, including the last one, ALL use the NORECOVERY option. When you're ready, take a "Tail Log Backup" on the original. That will set the original to "Recovering" status and no one will be able to make any changes. Restore the backup for for the tail log on the new server and use the RECOVERY option. Total down time (for the restore, anyway) will be measured in short minutes if you prepare by making sure that you read up on it and have your scripts ready.

I've done this many times for many databases where I work. We did more than 64 databases of decent size and 1 of them was 2TB and another was 1TB. Total downtime was about 20 minutes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 6

I found that striping the database backup significantly improved both the backup and restore times. That is the multiple files that is referred to in the article, but doesn't provide much information.