Move SQL Server backup file and restore it with Perl

#There are two key parts for this script. The first part copies the latest backup and paste it in the local g drive. The second part do the restore from this backup file. It’s a LightSpeed backup and restore. Linchi Shea’s SQLDBA helper Perl files are needed. This is mainly for my own safekeeping.

#Part 1
use File::Copy;
use File::stat;
use SQLDBA::Utility qw( dbaRunOsql );  # import the function

$dirname = shift or die "Please provide a directory to search for";
$timediff=0;

opendir DIR, "$dirname";

while (defined ($file = readdir(DIR)))
{
	if($file ne "." && $file ne "..")
	{
		$diff = time()-stat("$dirname/$file")->mtime;
		if($timediff == 0)
                       {
                       	$timediff=$diff;
                       	$newest=$file;
                       }
                if($diff< $timediff)
		       {
                       	$timediff=$diff;
                       	$newest=$file;
                       }
        }
}

print $newest,"\n";

copy("$dirname/$newest", "g:/$newest");


#Part 2
# Construct the SQL script
my $sql =<<__SQL__;
SET NOCOUNT ON

USE Master

DECLARE \@strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'

CREATE table #tmpUsers(
spid smallint,
ecid smallint,
status varchar(30),
loginname varchar(128),
hostname varchar(128),
blk char(5),
dbname nchar(50),
cmd nchar(16))

INSERT INTO #tmpUsers EXEC SP_WHO


DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'MyDatabase'

DECLARE \@spid varchar(10)
DECLARE \@dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO \@spid, \@dbname2
WHILE (\@\@fetch_status <> -1)
BEGIN
	IF (\@\@fetch_status <> -2)
	BEGIN
	PRINT 'Killing ' + \@spid
	SET \@strSQL = 'KILL ' + \@spid
	EXEC (\@strSQL)
	END
	FETCH NEXT FROM LoginCursor INTO  \@spid, \@dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers
PRINT 'Done'

-- The line below is the command to restrict access (behine EM) to dbo (schema owner) and sysadm (sys user):
--exec sp_dboption N\@dbname, N'dbo use', N'true'
--exec sp_dboption N\@dbname, N'dbo use', N'false'



EXEC master.dbo.xp_restore_database \@database='MyDatabase'
  , \@filename = 'G:\\$newest'
GO

__SQL__

# Prepare the osql command line options
my $optRef = {
      '-E' => undef,
      '-n' => undef,
      '-w' => '1024',
      '-d' => 'master',
      '-l' => '5'
   };

# Execute the SQL script on the APOLLO named instance on the local server
my $result = dbaRunOsql('MyServerName', $sql, $optRef);
if (!defined $result) {
   die "***Err: failed to run osql.exe.";
}
else {
   print $result;  # Print the results
}