SQLTeam.com | Weblogs | Forums

SQL server full restore as standby mode

sql2008

#1

I have a 1.7 TB db. I want to set log shipping on looking to another server in my network. This was already configured, because we had a disaster on a server we need to configure it again.

I tried to get the latest full db log and restore it of course with stand by mode:

RESTORE DATABASE Db
FROM DISK = N'R:\Db_backup_2016_07_04_045906_7280000.bak' WITH FILE = 1,
MOVE N'Primary_Data' TO N'R:\ForteRestore\Data\Forte.mdf',
MOVE N'Static_Data' TO N'R:\ForteRestore\Data\Forte_1.ndf',
MOVE N'Transaction_Data' TO N'R:\ForteRestore\Data\Forte_2.ndf',
MOVE N'Static_Indexes' TO N'R:\ForteRestore\Data\Forte_3.ndf',
MOVE N'Transaction_Indexes' TO N'R:\ForteRestore\Data\Forte_4.ndf',
MOVE N'Host_Data' TO N'R:\ForteRestore\Data\Forte_5.ndf',
MOVE N'Host_Indexes' TO N'R:\ForteRestore\Data\Forte_6.ndf',
MOVE N'Moms_Data' TO N'R:\ForteRestore\Data\Forte_7.ndf',
MOVE N'Moms_Indexes' TO N'R:\ForteRestore\Data\Forte_8.ndf',
MOVE N'Moms_Rpt_Data' TO N'R:\ForteRestore\Data\Forte_9.ndf',
MOVE N'Moms_Rpt_Indexes' TO N'R:\ForteRestore\Data\Forte_10.ndf',
MOVE N'Forte_Log' TO N'R:\ForteRestore\Data\Forte_log.ldf',
STANDBY = N'R:\ForteRestore\StandBy\StandBy.tuf', REPLACE, STATS = 10


Restore process always get's stuck on 0%. Even if I query for process completion percent it stills at 0%.

If I restore the backup as full normal (not stand by), will work and percent starts to grow, but in stand by it doesn't.

Have some of you guys encounter a similar situation?


#2

I have not encountered that issue. Why do you need it to be in standby mode? Why not just NO RECOVERY mode?

Are you using the log shipped database for reporting?


#3

Simply went to the original database and select ship transaction logs and then select the STANDBY instead of NORECOVERY


#4

Hello, I resolved the issue. Problem was with the SQL SERVER and AGENT accounts missing an Active Directory permission known as "PERFORM VOLUME MAINTENANCE TASKS", with this permission the SQL accounts can initialize data files with no wait... Without it, it will have to initialize files inserting 0 in whatever bytes of size is the db, resulting in more restore time. Also I had a problem with the connection of the storage, it was too slow. After resolving this two issues, I made the restore with norecovery then configured log shipping and everything ran smoothly on a 1.78 TB DB.

Just for reference. Thank You!