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.
[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]

, ,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.