#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.
[sourcecode language=”perl”]
#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
}
[/sourcecode]