The Ji Village News

季庄新闻–Haidong Ji’s Blog

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

Sometimes you want to know how much space Sql Server databases on an instance are taking from a system. This should be the sum of all data files and log files. Note this is different from how much space actual data is taking.

Here is one way to do it. Note it uses sysaltfiles, a system table that might be deprecated in future releases. For Sql Server 2005, it is recommended that you use sys.master_file:

select sum(size)*8/1024 as ‘TotalSizeInMeg’, sum(size)*8/1024/1024 as ‘TotalSizeInGig’ from sysaltfiles

Another way to get that is to use sp_helpdb. Executing sp_helpdb without any parameters will give you total size for each database in megabytes. Adding them up will give you total databases size.

Since I am digging Perl, below is the Perl script to do that. You might notice the numbers you get from sysaltfiles and sp_helpdb are different, that’s due to rounding.

use strict;
use Win32::SqlServer;

# Get server name from the command line
my $ServerName = shift or die “Please enter server name as the first only parameter needed”;

# Create object and login in one step with
# integrated security.
my $sqlsrv = sql_init($ServerName, undef, undef, ‘master’);

# Our SQL statement. In this case, this is a system stored procedure.
my $stmnt = < sp_helpdb
SQLEND

# Run query. The return value is a reference to an array.
my $result = $sqlsrv->sql_sp($stmnt);
my $TotalSize = 0;
# Print results. Each array entry is a hash reference.
foreach my $row (@$result) {
print “$$row{name}: $$row{db_size} \n”;
if ($$row{db_size} =~ /(\d+\.\d+)/ix) {
$TotalSize = $TotalSize + $1;
}
}
print “Total databases size is $TotalSize meg”;

#I’ve worked on and off with Perl, but really want to be more proficient with it. I will do more automation with Perl and will share it here for my reader and as a reference for myself. I just created a Perl tag on this blog.
#Here is one script I cooked out today. It takes a directory / folder name as input, and return the latest or newest modified file. Suggestions welcome!

#use File::Copy;
use File::stat;

$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”, “c:/work/$newest”);

This is mainly for my own reference. Save it as ListSysadmin.pl. Run ListSysadmin.pl SqlServerList.txt on command line. It lists all logins that has sa (sysadmin) rights on Sql Server instance, including individual domain members in a domain group that has sysadmin rights.

SqlServerList.txt should be a text file, with each individual line contains a Sql Server instance.

This script is an improvement over Linchi’s original scrip from his Real World SQL Server Administration with Perl. The original script only takes one instance as input parameter. Note: with my current site style sheet on my web site, I think it ignores the angle brackets, so you need to fix the script when you do copy and paste.

use strict;
use Win32::ODBC;

# Import the function from the module SQLDBA::Utility
use SQLDBA::Utility qw( dbaRemoveDuplicates );

# Import functions from the module Win32::NetAdmin
use Win32::NetAdmin qw( GetDomainController
GroupGetMembers
LocalGroupGetMembersWithDomain
UsersExist );

Main: {
my $log = shift or die “***Err: $0 expects a file name.\n”;
open(LOG, “$log”) or die “***Err: couldn’t open $log.\n”;
while () {
my $line = $_;
if ($line =~ /\[?(\w+)/ix) {
my $serverConn = $1;

print "Instance: $1\n";

# get all the accounts/logins with sysadmin role from SQL Server
my $loginRef = getSQLSysadmins($serverConn);
# in case the server name is followed by an instance name
my ($server) = $serverConn =~ /([^\\]+)(\\.+)?/;

# replace BUILTIN with the server name
foreach (@{$loginRef->{groupSet}}) {
s/BUILTIN\\/uc($server) . “\\”/e; # evaluate the right side before replace
}

# remove all local groups and get their members
$loginRef = removeLocalGroups($loginRef);

# get global group members
$loginRef = getGlobalGroupMembers($loginRef);

# now print out all the NT/SQL logins that are SQL sysadmin
print “Listing all NT/SQL logins with the sysadmin rights …\n”;
foreach (sort @{$loginRef->{resultSet}}) { print “\t$_\n”; }
}
}
close(LOG);

} # Main

#########################
sub getSQLSysadmins {
my $serverConn = shift or
die “***Err: getSQLSysadmins() expects a server.”;

my $connStr = “Driver={SQL Server};Server=$serverConn;” .
“Trusted_Connection=Yes;Database=master”;
my $conn = new Win32::ODBC ($connStr) or
die “***Err: Can’t connect to $serverConn. ” . Win32::ODBC::Error();

# get sysadmin logins (NT and SQL)
my @logins = ();
my $sql = q/SELECT loginname FROM master.dbo.syslogins
WHERE sysadmin = 1
AND isntgroup = 0/;

unless ($conn->Sql( $sql )) {
@logins = (@logins, $conn->Data) while ($conn->FetchRow());
} else {
print “***Err: executing $sql. “, Win32::ODBC::Error(), “\n”;
}

# get sysadmin nt groups
my @groups = ();
my $sql = q/select loginname from master.dbo.syslogins
where sysadmin = 1
and isntgroup = 1/;

unless ($conn->Sql( $sql )) {
@groups = (@groups, $conn->Data) while ($conn->FetchRow());
} else {
print “***Err: executing $sql. “, Win32::ODBC::Error(), “\n”;
}
$conn->Close();

return { resultSet => \@logins,
groupSet => \@groups,
userSet => undef };
} # getSQLSysadmins

###########################
sub removeLocalGroups {
my $loginRef = shift or
die “***Err: removeLocalGroups() expects a reference.”;

foreach my $group (@{$loginRef->{groupSet}}) {
if (my $rc = getLocalGroupMembers($group)) {
push @{$loginRef->{userSet}}, @{$rc};
}
else {
push @{$loginRef->{userSet}}, $group;
}
}
delete $loginRef->{groupSet};
return $loginRef;
} # removeLocalGroups

##############################
sub getGlobalGroupMembers {
my $loginRef = shift or
die “***Err: getGlobalGroupMembers() expects a reference.\n”;
my @logins = ();

foreach my $user (@{$loginRef->{userSet}}) {
if ($user =~ /^NT\s+AUTHORITY\\/) {
push @logins, $user;
next;
}

my ($domain, $name) = $user =~ /^(.+?)\\(.+)$/;
if ( UsersExist($domain, $name ) ) { # a user account
push @logins, $user;
}
else {
my $pdc;
my @users;
if (GetDomainController(undef, $domain, $pdc)) {
$pdc =~ s/\\//g;

if ( UsersExist($pdc, $name ) ) {
push @logins, $user;
}
else { # $name is a global group account
# get users in the global group
if (GroupGetMembers($pdc, $name, \@users)) {
# prefix the users with the domain name
push @logins, map { “$domain\\$_”; } @users;
}
else { # you shouldn’t reach here
print “***Err: can’t retrieve users for $domain\\$name.\n”;
next;
}
}
}
else {
print “***Err: can’t find domain controller for $domain.\n”;
next;
}
}
}

push @logins, @{$loginRef->{resultSet}};
$loginRef->{resultSet} = dbaRemoveDuplicates(\@logins);
return $loginRef;
} # getGlobalGroupMembers

################################
sub getLocalGroupMembers {
my $group = shift or
die “***Err: getLocalGroupMember() expects a group name.”;

my ($domain, $group_name) = $group =~ /^(.+?)\\(.+)$/ or
die “***Err: $group must be qualified with a domain/machine name.”;

my @users = ();
if (LocalGroupGetMembersWithDomain($domain, $group_name, \@users)) {
return \@users;
}
else {
return undef;
}
} # getLocalGroupMembers

One important method for performance tuning is to decrease number of logical reads, by adding, updating, and/or refreshing indexes and statistics. To see if those changes make a difference for you, it is important to look at query statistics.

Here is a sample output after set statistics io on:

Table ‘Table1′. Scan count 0, logical reads 1673, physical reads 4, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table2′. Scan count 0, logical reads 1673, physical reads 10, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table3′. Scan count 1, logical reads 11871, physical reads 1, read-ahead reads 11865, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#TempTable_____________________________________________________________________________________________________________000000000022′. Scan count 2, logical reads 276, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table2′. Scan count 156, logical reads 1723, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I used to tally those numbers by hand. This is error prone, and can be time-consuming, especially if the output is big. So I cooked up a Perl script below for this:

#!/usr/bin/perl
use strict;

my $LogicalReads = 0;

Main: {
my $log = shift or die "***Err: $0 expects a file name.\n";
open(LOG, "$log") or die "***Err: couldn't open $log.\n";
while () {
findPattern($_);
}
close(LOG);
print "Total logical read is $LogicalReads\n";
} # Main

#######################
sub findPattern {
my $line = shift or die "***Err: function findPattern() expects a string.\n";
if ($line =~ /logical\sreads\s+(\d+)/ix) {
$LogicalReads = $LogicalReads + $1;
}
} # findPattern

Thanks Linchi and Tom for ideas!

I attended an introductory Perl course recently. I’ve always wanted to learn the language. It is a very powerful scripting tool with excellent regular expression support.

The training delivered by Daina Pettit from LearningTree was excellent. One nugget I got out of the class, that I think is worth half of the class cost, is this:

On command line, be it *nix or ActiveState on Windows, type:

perl -de 0

to open Perl debugger interactively. Perl will try to run any statement you type. It is a great way to learn.

-d launches the debugger. -e takes one line of program. When given 0, it will take whatever we type through the prompt.

Fellow SqlServerCentral author K. Brian Kelley wrote an excellent article on using Perl and Windows::ODBC module to manage Sql Server here. Brian is apparently a big fan of Perl.

As I am writing and publishing more and more blog entries, it becomes important that I have good and reliable backups. I know that if something happens and I cannot recover my entries and comments, I would be terribly upset. So I want to share my process here. Please feel free to share your backup methods by commenting. Hopefully somebody will find it useful.

Backup comprises two parts:

1. File backup

On a Linux machine with Apache, the default web files and directories reside in /var/www/html. Yours may be different. This include all php files and sub-directories like wp-content, wp-include, etc.

The easiest way to do it is to use tar command with -z to compress them. My post here gives you a pointer on tar.

These files are fairly static, so you do not need to back them up too often. You want to do file backup after you tried a new theme, installed a new plugin, etc. You should transfer this backup to a different box, in case the web host dies.

2. Database backup

For mysql database backup, you can use mysqldump, a utility that comes with mysql. mysqldump will iterate through all tables within the database, get their ddl, and dump all data in the form of insert statements. Below is the code to dump everything out and compress them using gzip:

mysqldump -q -e -hlocalhost -uLogin -pPassword MyBlogDb | gzip - > ./BlogDbBackup.sql.gz

Once again, gzip can shrink the size down pretty substantially. So do use it, especially if you have space issues.

There is a database backup plugin that comes with WordPress 2, which is the method I use. However, you do need to run chmod 777 on your database backup directory. If not, you will see the warning message of not enough privilege on your WordPress manage page. And you should be able to find out the backup directory name from that page, so you can run chmod.

In addition to the backup plugin, I also installed the wp-cron plugin. With wp-cron, I can schedule daily database backup and send it to an email address I defined. If I have a choice, I’d rather put everything in a shell script that backs everything up, compress them, and email the file to me. I then use cron to schedule it. However, since I am not root on my web host, I have had troubles setting up a smtp client for email, as documented here.

Now backup is done. The next step is to make sure my backup files work. I set up a standalone LAMP (Linux, Apache, MySql, PHP) box, extracted php files, restored the database by running:

mysql -hlocalhost -uLogin -p MyBlogDb < BlogDbBackup.sql

I then modified wp-config.php file. And behold, it worked. Even my Chinese entries displayed properly.

In a future post, I will share with you some of the lessons I learned in the process. Stay tuned…