1

I know IFI (Instant File Initialization) is good to have enabled on SQL Servers. How about backup archive locations? We backup our DBs to a local SSD drive (so it backs up quickly), then throughout the day we slowly move the backups over to an archive location and perform restores and DBCCs on a non-prod server. I was curious if that archive location we copy the backups to should have IFI enabled?

0

If the archive location is part of same server and if you are performing restores to same server,then you will have IFI enabled.You can always enable Instant file initialization on server you are performing restores too ,I dont see an issue enabling it on prod server and not enabling it on test server.you can further check using below trace flags if IFI is enabled or not.

DBCC TRACEON(3004,-1); DBCC TRACEON(3605,-1);

| improve this answer | |
  • No the archive location is on a separate file server. So its like this: 1) Prod server, 2) File server holding final resting place of backups, 3) non-prod used for restores from the file server (Restores are done here -- MDF/LDF drives on this server, not the #2 file server). I want to know if I should have IFI turned on on that File server for copying the files to. – Chris Woods Feb 12 '15 at 3:42
  • Do you need help in enabling it or do you want to know whether you can enable it or not ? – TheGameiswar Feb 12 '15 at 3:44
  • I want to know if enabling it will help speed up the copying of the backup from the Prod SSD drive to the File server "final archive" drive. Enabling IFI speeds up file creation in restores, growths, and new DB creation. So I am assuming it also speeds up copying of the file from one server to the one where you have it enabled. But not sure. – Chris Woods Feb 12 '15 at 4:02
  • No manual copy wont speed up things.IFI works with sql server and the way it works is to not by zeroing out the empty space.tyy robo copy or equivalent and try to compress backup before copying can be some of things to speed up copy – TheGameiswar Feb 12 '15 at 4:19
  • Thanks, I know how IFI works, I was thinking copying zeroes out the data first before it starts the copy. – Chris Woods Feb 12 '15 at 4:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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