We are trying to restore a DB from a backup taken on ServerA to ServerB.
The .bak file is 55.3GB.
We are using the following TSQL:
ALTER DATABASE [DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DB] FROM DISK = N'\\share\Backup\DB_backup_2021_04_12_021002_3975291.bak' WITH FILE = 1,
MOVE N'DB_Data' TO N'D:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\DB_Data.MDF',
MOVE N'DB_Log' TO N'D:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\DB_Log.LDF',
NOUNLOAD, REPLACE, STATS = 5
ALTER DATABASE [DB] SET MULTI_USER
GO
When running in SSMS it takes approximately 4:38 minutes to restore the DB.
If I copy the file locally first, to the same destination drive as the SQL DB currently rests, it takes 1.77 minutes. Then I subsequently restore the file in SQL took another 2 minutes (all together about 3.5 minutes).
When I tried the same thing with a 286GB backup file, it took 19 minutes to (a) copy locally and (b) restore from local disk, as opposed to 1 hour when restoring directly from the network share.
I know the storage/share backend is not a bottleneck, since I can see how long a standard copy takes.
Another anomaly: our source share is a enterprise storage array, when we can see (both in Wireshark) and via the storage monitoring that we are successfully utilizing ODX when copying the file over the network (as was done in the second test). But when we directly restore to SQL from network storage (as opposed to local disk), we don't see any ODX transfers at all!
So, our questions are:
- Why isn't ODX being utilized together with SQL?
- Could that be the difference to explain the 1-step vs 2-step restore detailed above?