If you don't have access to the original server and all you have is the backup file, then you'll need to restore the headers from the backup file first to see what's inside it.
You can do this by:
create table #headers
(
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed bit,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLSN numeric(25,0),
LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),
DatabaseBackupLSN numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
[CodePage] smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(128),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0),
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier,
CompressedBackupSize bigint,
Containment tinyint
);
go
insert into #headers exec('restore headeronly from disk=''c:\DEV\UnitTest.bak''');
go
You now have a temporary table(#headers) which contains details of all the backups contained within that device file.
To find the most recent full database backup we filter for only full db backups (backupType=1) and then look for the most recent backup in the device, which will be the one with the largest LSN. This gives us the position value, which is used in the RESTORE command to specify to SQL Server which backup we want to restore:
declare @filepos int;
select top 1 @filepos=Position from #headers where BackupType=1 order by lastlsn desc;
restore database unittest from Disk=N'c:\DEV\UnitTest.bak' with file=@filepos, recovery, replace;
As you say you will still have access to the original server, we can then query the tables in msdb that SQL Server uses to store this information.
The following queries are assuming you are running them in a SQL session on the original server. If you're not allowed to do that, then you'll need to look into using a linked server and 4 part naming.
There are 2 ways you can specify the set of backups you want to look at. One is via the path to the backup file, the other is by referencing the name on the backup device. I've included both in this query, with some notes about which lines to comment out depend on how you're doing it.
This will produce a RESTORE statement which you can run in a seperate SSMS session. I've also included an example at the bottom of how you could automate this, though you'd want to do some testing beforehand to make sure it's doing what you want.
declare @filepos int, @dbname varchar(50), @devname varchar(50)
/*
dbname is the name of the database you want to restore. Need this, as it's possible for backups of more than one database to be present in the same backup file or device
devname is either the name of the backup device, or the path to the .bak file
*/
select @dbname='Unittest', @devname='c:\DEV\unittest.bak'
select @filepos=max(c.position)
from
msdb.dbo.backupmediafamily a
inner join msdb.dbo.backupmediaset b on a.media_set_id=b.media_set_id
inner join msdb.dbo.backupset c on b.media_set_id=c.media_set_id
where
a.physical_device_name=@devname --use this line if you know the path to the backup
--a.logical_device_name=@devname --this line if you only know a device name.
and c.type='D' and c.database_name=@dbname
--This will build the restore statement if you know the device name
select 'restore database ['+@dbname+'] from '+@devname+' with file='+cast(@filepos as varchar(4))+', recovery, replace'
--This will build the restore statement if you know the backup file path
select 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'
/*
This section only if feeling brave and confident, and you've checked the output previously, as it will execute whatever the generated restore command is, so this could cause a major problem if not checked first, or even overwrite production if run on the wrong box.
*/
declare @restore_cmd varchar(250)
select @restore = 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'
exec(@restore)
Best Answer
With some suggestion from Akina, I'm able to find one post in SO about how to determine if an IP is online or not through the network before executing the MySQL import command. Here is the link to the answer : https://stackoverflow.com/a/21252613/10910692
Below is the code I'm using in my batch file:
I also took Akina's suggestion to concatenate both the import and update query into one file and only need to call that one file for the update. As I mention in my question, the code above is repeated for about 20 times.
If I stick to my previous method and assuming that all IP is offline, it will take about 800 secs = 13-14 mins for the batch file to finish processing while with the method above, it reduces to 1-2 mins only! (based on assumption that each command took 5 secs max while in reality its less than 5 secs per command).