High Database Restoration time

• santhosh_ms3

  Default port

  Points: 1482

  More actions

  February 10, 2009 at 1:52 am

  #134539

  Hi ,

  I have a database having about 32 GB now. Its keep on increasing by daily basis.

  If i take a back up its taking nearly 2 hrs. But while restoring its about 4hrs 30 min.

  Can anyone suggest an alternative method to restore the database?

  Now i 'm using restoration script as below,

  RESTORE DATABASE [Hello] FROM DISK = N'\\ets-es\DB Backup\Hello.bak'

  WITH FILE = 1,

  MOVE N'Hello_data' TO N'D:\Hello\dbfiles\Hello.mdf1',

  MOVE N'DateFG200801File1' TO N'D:\Hello\dbfiles\Hello_1.ndf',

  MOVE N'DateFG200802File1' TO N'D:\Hello\dbfiles\Hello_2.ndf',

  MOVE N'DateFG200803File1' TO N'D:\Hello\dbfiles\Hello_3.ndf',

  MOVE N'DateFG200804File1' TO N'D:\Hello\dbfiles\Hello_4.ndf',

  MOVE N'DateFG200805File1' TO N'D:\Hello\dbfiles\Hello_5.ndf',

  MOVE N'DateFG200806File1' TO N'D:\Hello\dbfiles\Hello_6.ndf',

  MOVE N'DateFG200807File1' TO N'D:\Hello\dbfiles\Hello_7.ndf',

  MOVE N'DateFG200808File1' TO N'D:\Hello\dbfiles\Hello_8.ndf',

  MOVE N'DateFG200809File1' TO N'D:\Hello\dbfiles\Hello_9.ndf',

  MOVE N'DateFG200810File1' TO N'D:\Hello\dbfiles\Hello_10.ndf',

  MOVE N'DateFG200811File1' TO N'D:\Hello\dbfiles\Hello_11.ndf',

  MOVE N'DateFG200812File1' TO N'D:\Hello\dbfiles\Hello_12.ndf',

  MOVE N'DateFG200901File1' TO N'D:\Hello\dbfiles\Hello_13.ndf',

  MOVE N'DateFG200902File1' TO N'D:\Hello\dbfiles\Hello_14.ndf',

  MOVE N'DateFG200903File1' TO N'D:\Hello\dbfiles\Hello_15.ndf',

  MOVE N'DateFG200904File1' TO N'D:\Hello\dbfiles\Hello_16.ndf',

  MOVE N'DateFG200905File1' TO N'D:\Hello\dbfiles\Hello_17.ndf',

  MOVE N'DateFG200906File1' TO N'D:\Hello\dbfiles\Hello_18.ndf',

  MOVE N'DateFG200907File1' TO N'D:\Hello\dbfiles\Hello_19.ndf',

  MOVE N'DateFG200908File1' TO N'D:\Hello\dbfiles\Hello_20.ndf',

  MOVE N'DateFG200909File1' TO N'D:\Hello\dbfiles\Hello_21.ndf',

  MOVE N'DateFG200910File1' TO N'D:\Hello\dbfiles\Hello_22.ndf',

  MOVE N'DateFG200911File1' TO N'D:\Hello\dbfiles\Hello_23.ndf',

  MOVE N'DateFG200912File1' TO N'D:\Hello\dbfiles\Hello_24.ndf',

  MOVE N'DateFG201001File1' TO N'D:\Hello\dbfiles\Hello_25.ndf',

  MOVE N'DateFG201002File1' TO N'D:\Hello\dbfiles\Hello_26.ndf',

  MOVE N'DateFG201003File1' TO N'D:\Hello\dbfiles\Hello_27.ndf',

  MOVE N'DateFG201004File1' TO N'D:\Hello\dbfiles\Hello_28.ndf',

  MOVE N'DateFG201005File1' TO N'D:\Hello\dbfiles\Hello_29.ndf',

  MOVE N'DateFG201006File1' TO N'D:\Hello\dbfiles\Hello_30.ndf',

  MOVE N'DateFG201007File1' TO N'D:\Hello\dbfiles\Hello_31.ndf',

  MOVE N'DateFG201008File1' TO N'D:\Hello\dbfiles\Hello_32.ndf',

  MOVE N'DateFG201009File1' TO N'D:\Hello\dbfiles\Hello_33.ndf',

  MOVE N'DateFG201010File1' TO N'D:\Hello\dbfiles\Hello_34.ndf',

  MOVE N'DateFG201011File1' TO N'D:\Hello\dbfiles\Hello_35.ndf',

  MOVE N'DateFG201012File1' TO N'D:\Hello\dbfiles\Hello_36.ndf',

  MOVE N'DateFG201101File1' TO N'D:\Hello\dbfiles\Hello_37.ndf',

  MOVE N'DateFG201102File1' TO N'D:\Hello\dbfiles\Hello_38.ndf',

  MOVE N'DateFG201103File1' TO N'D:\Hello\dbfiles\Hello_39.ndf',

  MOVE N'DateFG201104File1' TO N'D:\Hello\dbfiles\Hello_40.ndf',

  MOVE N'DateFG201105File1' TO N'D:\Hello\dbfiles\Hello_41.ndf',

  MOVE N'DateFG201106File1' TO N'D:\Hello\dbfiles\Hello_42.ndf',

  MOVE N'DateFG201107File1' TO N'D:\Hello\dbfiles\Hello_43.ndf',

  MOVE N'DateFG201108File1' TO N'D:\Hello\dbfiles\Hello_44.ndf',

  MOVE N'DateFG201109File1' TO N'D:\Hello\dbfiles\Hello_45.ndf',

  MOVE N'DateFG201110File1' TO N'D:\Hello\dbfiles\Hello_46.ndf',

  MOVE N'DateFG201111File1' TO N'D:\Hello\dbfiles\Hello_47.ndf',

  MOVE N'DateFG201112File1' TO N'D:\Hello\dbfiles\Hello_48.ndf',

  MOVE N'DateFG201201File1' TO N'D:\Hello\dbfiles\Hello_49.ndf',

  MOVE N'DateFG201202File1' TO N'D:\Hello\dbfiles\Hello_50.ndf',

  MOVE N'DateFG201203File1' TO N'D:\Hello\dbfiles\Hello_51.ndf',

  MOVE N'DateFG201204File1' TO N'D:\Hello\dbfiles\Hello_52.ndf',

  MOVE N'DateFG201205File1' TO N'D:\Hello\dbfiles\Hello_53.ndf',

  MOVE N'DateFG201206File1' TO N'D:\Hello\dbfiles\Hello_54.ndf',

  MOVE N'DateFG201207File1' TO N'D:\Hello\dbfiles\Hello_55.ndf',

  MOVE N'DateFG201208File1' TO N'D:\Hello\dbfiles\Hello_56.ndf',

  MOVE N'DateFG201209File1' TO N'D:\Hello\dbfiles\Hello_57.ndf',

  MOVE N'DateFG201210File1' TO N'D:\Hello\dbfiles\Hello_58.ndf',

  MOVE N'DateFG201211File1' TO N'D:\Hello\dbfiles\Hello_59.ndf',

  MOVE N'DateFG201212File1' TO N'D:\Hello\dbfiles\Hello_60.ndf',

  MOVE N'DateFG201301File1' TO N'D:\Hello\dbfiles\Hello_61.ndf',

  MOVE N'NonPartFG1File1' TO N'D:\Hello\dbfiles\Hello_62.ndf',

  MOVE N'NCIndexFGFile1' TO N'D:\Hello\dbfiles\Hello_63.ndf',

  MOVE N'MiscFGFile1' TO N'D:\Hello\dbfiles\Hello_64.ndf',

  MOVE N'PatientFG1File1' TO N'D:\Hello\dbfiles\Hello_65.ndf',

  MOVE N'PatientFG2File1' TO N'D:\Hello\dbfiles\Hello_66.ndf',

  MOVE N'PatientFG3File1' TO N'D:\Hello\dbfiles\Hello_67.ndf',

  MOVE N'PatientFG4File1' TO N'D:\Hello\dbfiles\Hello_68.ndf',

  MOVE N'PatientFG5File1' TO N'D:\Hello\dbfiles\Hello_69.ndf',

  MOVE N'PatientFG6File1' TO N'D:\Hello\dbfiles\Hello_70.ndf',

  MOVE N'PatientFG7File1' TO N'D:\Hello\dbfiles\Hello_71.ndf',

  MOVE N'PatientFG8File1' TO N'D:\Hello\dbfiles\Hello_72.ndf',

  MOVE N'PatientFG9File1' TO N'D:\Hello\dbfiles\Hello_73.ndf',

  MOVE N'PatientFG10File1' TO N'D:\Hello\dbfiles\Hello_74.ndf',

  MOVE N'PatientFG11File1' TO N'D:\Hello\dbfiles\Hello_75.ndf',

  MOVE N'PatientFG12File1' TO N'D:\Hello\dbfiles\Hello_76.ndf',

  MOVE N'PatientFG13File1' TO N'D:\Hello\dbfiles\Hello_77.ndf',

  MOVE N'PatientFG14File1' TO N'D:\Hello\dbfiles\Hello_78.ndf',

  MOVE N'PatientFG15File1' TO N'D:\Hello\dbfiles\Hello_79.ndf',

  MOVE N'PatientFG16File1' TO N'D:\Hello\dbfiles\Hello_80.ndf',

  MOVE N'PatientFG17File1' TO N'D:\Hello\dbfiles\Hello_81.ndf',

  MOVE N'PatientFG18File1' TO N'D:\Hello\dbfiles\Hello_82.ndf',

  MOVE N'PatientFG19File1' TO N'D:\Hello\dbfiles\Hello_83.ndf',

  MOVE N'PatientFG20File1' TO N'D:\Hello\dbfiles\Hello_84.ndf',

  MOVE N'PatientFG21File1' TO N'D:\Hello\dbfiles\Hello_85.ndf',

  MOVE N'PatientFG22File1' TO N'D:\Hello\dbfiles\Hello_86.ndf',

  MOVE N'PatientFG23File1' TO N'D:\Hello\dbfiles\Hello_87.ndf',

  MOVE N'PatientFG24File1' TO N'D:\Hello\dbfiles\Hello_88.ndf',

  MOVE N'PatientFG25File1' TO N'D:\Hello\dbfiles\Hello_89.ndf',

  MOVE N'PatientFG26File1' TO N'D:\Hello\dbfiles\Hello_90.ndf',

  MOVE N'PatientFG27File1' TO N'D:\Hello\dbfiles\Hello_91.ndf',

  MOVE N'PatientFG28File1' TO N'D:\Hello\dbfiles\Hello_92.ndf',

  MOVE N'PatientFG29File1' TO N'D:\Hello\dbfiles\Hello_93.ndf',

  MOVE N'PatientFG30File1' TO N'D:\Hello\dbfiles\Hello_94.ndf',

  MOVE N'AuditFG1File1' TO N'D:\Hello\dbfiles\Hello_95.ndf',

  MOVE N'AuditFG2File1' TO N'D:\Hello\dbfiles\Hello_96.ndf',

  MOVE N'AuditFG3File1' TO N'D:\Hello\dbfiles\Hello_97.ndf',

  MOVE N'AuditFG4File1' TO N'D:\Hello\dbfiles\Hello_98.ndf',

  MOVE N'AuditFG5File1' TO N'D:\Hello\dbfiles\Hello_99.ndf',

  MOVE N'AuditFG6File1' TO N'D:\Hello\dbfiles\Hello_100.ndf',

  MOVE N'AuditFG7File1' TO N'D:\Hello\dbfiles\Hello_101.ndf',

  MOVE N'AuditFG8File1' TO N'D:\Hello\dbfiles\Hello_102.ndf',

  MOVE N'AuditFG9File1' TO N'D:\Hello\dbfiles\Hello_103.ndf',

  MOVE N'AuditFG10File1' TO N'D:\Hello\dbfiles\Hello_104.ndf',

  MOVE N'AuditFG11File1' TO N'D:\Hello\dbfiles\Hello_105.ndf',

  MOVE N'AuditFG12File1' TO N'D:\Hello\dbfiles\Hello_106.ndf',

  MOVE N'AuditFG13File1' TO N'D:\Hello\dbfiles\Hello_107.ndf',

  MOVE N'AuditFG14File1' TO N'D:\Hello\dbfiles\Hello_108.ndf',

  MOVE N'AuditFG15File1' TO N'D:\Hello\dbfiles\Hello_109.ndf',

  MOVE N'AuditFG16File1' TO N'D:\Hello\dbfiles\Hello_110.ndf',

  MOVE N'AuditFG17File1' TO N'D:\Hello\dbfiles\Hello_111.ndf',

  MOVE N'AuditFG18File1' TO N'D:\Hello\dbfiles\Hello_112.ndf',

  MOVE N'AuditFG19File1' TO N'D:\Hello\dbfiles\Hello_113.ndf',

  MOVE N'AuditFG20File1' TO N'D:\Hello\dbfiles\Hello_114.ndf',

  MOVE N'AuditFG21File1' TO N'D:\Hello\dbfiles\Hello_115.ndf',

  MOVE N'AuditFG22File1' TO N'D:\Hello\dbfiles\Hello_116.ndf',

  MOVE N'AuditFG23File1' TO N'D:\Hello\dbfiles\Hello_117.ndf',

  MOVE N'AuditFG24File1' TO N'D:\Hello\dbfiles\Hello_118.ndf',

  MOVE N'AuditFG25File1' TO N'D:\Hello\dbfiles\Hello_119.ndf',

  MOVE N'AuditFG26File1' TO N'D:\Hello\dbfiles\Hello_120.ndf',

  MOVE N'AuditFG27File1' TO N'D:\Hello\dbfiles\Hello_121.ndf',

  MOVE N'AuditFG28File1' TO N'D:\Hello\dbfiles\Hello_122.ndf',

  MOVE N'AuditFG29File1' TO N'D:\Hello\dbfiles\Hello_123.ndf',

  MOVE N'AuditFG30File1' TO N'D:\Hello\dbfiles\Hello_124.ndf',

  MOVE N'CDCFG1File1' TO N'D:\Hello\dbfiles\Hello_125.ndf',

  MOVE N'CDCFG2File1' TO N'D:\Hello\dbfiles\Hello_126.ndf',

  MOVE N'CDCFG3File1' TO N'D:\Hello\dbfiles\Hello_127.ndf',

  MOVE N'CDCFG4File1' TO N'D:\Hello\dbfiles\Hello_128.ndf',

  MOVE N'CDCFG5File1' TO N'D:\Hello\dbfiles\Hello_129.ndf',

  MOVE N'CDCFG6File1' TO N'D:\Hello\dbfiles\Hello_130.ndf',

  MOVE N'CDCFG7File1' TO N'D:\Hello\dbfiles\Hello_131.ndf',

  MOVE N'CDCFG8File1' TO N'D:\Hello\dbfiles\Hello_132.ndf',

  MOVE N'CDCFG9File1' TO N'D:\Hello\dbfiles\Hello_133.ndf',

  MOVE N'CDCFG10File1' TO N'D:\Hello\dbfiles\Hello_134.ndf',

  MOVE N'CDCFG11File1' TO N'D:\Hello\dbfiles\Hello_135.ndf',

  MOVE N'CDCFG12File1' TO N'D:\Hello\dbfiles\Hello_136.ndf',

  MOVE N'CDCFG13File1' TO N'D:\Hello\dbfiles\Hello_137.ndf',

  MOVE N'CDCFG14File1' TO N'D:\Hello\dbfiles\Hello_138.ndf',

  MOVE N'CDCFG15File1' TO N'D:\Hello\dbfiles\Hello_139.ndf',

  MOVE N'CDCFG16File1' TO N'D:\Hello\dbfiles\Hello_140.ndf',

  MOVE N'CDCFG17File1' TO N'D:\Hello\dbfiles\Hello_141.ndf',

  MOVE N'CDCFG18File1' TO N'D:\Hello\dbfiles\Hello_142.ndf',

  MOVE N'CDCFG19File1' TO N'D:\Hello\dbfiles\Hello_143.ndf',

  MOVE N'CDCFG20File1' TO N'D:\Hello\dbfiles\Hello_144.ndf',

  MOVE N'CDCFG21File1' TO N'D:\Hello\dbfiles\Hello_145.ndf',

  MOVE N'CDCFG22File1' TO N'D:\Hello\dbfiles\Hello_146.ndf',

  MOVE N'CDCFG23File1' TO N'D:\Hello\dbfiles\Hello_147.ndf',

  MOVE N'CDCFG24File1' TO N'D:\Hello\dbfiles\Hello_148.ndf',

  MOVE N'CDCFG25File1' TO N'D:\Hello\dbfiles\Hello_149.ndf',

  MOVE N'CDCFG26File1' TO N'D:\Hello\dbfiles\Hello_150.ndf',

  MOVE N'CDCFG27File1' TO N'D:\Hello\dbfiles\Hello_151.ndf',

  MOVE N'CDCFG28File1' TO N'D:\Hello\dbfiles\Hello_152.ndf',

  MOVE N'CDCFG29File1' TO N'D:\Hello\dbfiles\Hello_153.ndf',

  MOVE N'CDCFG30File1' TO N'D:\Hello\dbfiles\Hello_154.ndf',

  MOVE N'Hello_log' TO N'D:\Hello\dbfiles\Hello_155.ldf',

  NOUNLOAD, REPLACE, STATS = 10

  GO

• Sakthivel Chidambaram

  SSCertifiable

  Points: 5531

  More actions

  February 10, 2009 at 1:57 am

  #941011

  You can do that...

  Try to backup to a dedicated volume only for backup which does not hosts Data files or tempdb...

  Use options available in backup command to increase the transfer size... Refer http://www.sqlserverdba.co.cc/2008/11/how-to-make-sql-backup-process-faster.html for more details...

  Exclude backup directory from Antivirus Scanner

  Regards,
  Sakthi
  My Blog -> http://www.sqlserverdba.co.cc

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  February 10, 2009 at 10:24 pm

  #941813

  Are you restoring to the same files? Creating this many files might take time.

  Why so many files, out of curiosity? Seems like extreme overkill and an admin nightmare.

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  February 19, 2009 at 1:08 pm

  #947070

  Also, are you restoring over a Network? (From shared directory in another server)

  -Roy