#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 }