Perl script backup Restore SQL Server

Write a Perl script that is flexible enough to back up or restore.

1. Save Config.txt to C packing directory

Username:sa

password:passwd123!

Modify the contents of the document according to the actual situation

2. Executive bcp.pl

#! C:\Perl64\bin

#通过BCP备份和还原SQLSERVER指定的表

Use 5.014;

#加载用户和密码配置文 pieces

my $username;

My $passwd;

# #获取用户名

My $dir = "C:";

$dir = $dir. " /";

My $filename = "Config.txt";

My $keysword = "username:";

My $count = "50";

My $begincount =index ($keysword, ":");

My ($file);

Open (Fileh, $dir. $filename);

while (my $test =<fileh>) {

if (Index ($test, $keysword) >=0) {

My $test 1=substr ($test, $begincount +1, $count);

#print "$test 1";

Chomp ($username = $test 1);

}

}

Close (Fileh);

# #获取密码

My $dir = "C:";

$dir = $dir. " /";

My $filename = "Config.txt";

My $keysword = "Password:";

My $count = "50";

My $begincount =index ($keysword, ":");

My ($file);

Open (Fileh, $dir. $filename);

while (my $test =<fileh>) {

if (Index ($test, $keysword) >=0) {

My $test 2=substr ($test, $begincount +1, $count);

#print "$test 2";

Chomp ($passwd = $test 2);

}

}

Close (Fileh);

print "-------------------------------------------------------------------\ n";

Print "First check if the user and password profile config.txt save path is $dir $filename\n";

print "-------------------------------------------------------------------\ n";

Print Important: Backup table operation Please enter 0; restore table operation please input 1\n ";

Print "Please enter the operation code:";

Chomp (my $inp =<stdin>);

Print "The operation code you entered is $inp\n";

#判断输入为0即备份操作

if ($INP eq 0) {

print ' Server IP: (do not enter default for localhost) ';

Chomp (My $a = <STDIN>);

if ($a = = ") {

$a = ' localhost ';

}else{

Print "The server IP you entered is: $a \ n";

}

print ' username: ';

Chomp (My $b = <STDIN>);

Print "The username you entered is: $b \ n";

if ($b eq $username) {

Print "User through!\n";

} else {

Print "Sorry, user name error \ n";

Exit

}

Print "User password:";

Chomp (My $readPsw = <STDIN>);

if ($READPSW eq $passwd) {

print "Correct password!\n";

} else {

Print "Sorry, bad password \ n";

Exit

}

Print "Important: The table name format for the backup table is: test.dbo.t1\n";

print ' Backed up table: ';

Chomp (My $d = <STDIN>);

Print "The Backup table you entered: $d \ n";

print ' Save path: ';

Chomp (My $e = <STDIN>);

Print "Save path you entered: $e \ n";

if (-e $e) {

Print "$e already exists! Do you want to delete it? (y delete; n do not delete) \ n ";

Chomp (my $i =<stdin>);

Given ($i)

{

When ("Y") {

Unlink $e;

Print "Start Backup table!\n";

System ("bcp $d out $e-C-t ', '-M 1-a 49152-b 5000-f 1-s $a-u $b-P $READPSW");

}

When ("n")

{

Print "-----------backup terminated-----------\ n";

Print "Specifies that the file exists, please re-enter the filename!\n";

}

Default {print "can only enter Y or n!\n";}

}

}

else{

{

System ("bcp $d out $e-C-t ', '-M 1-a 49152-b 5000-f 1-s $a-u $b-P $READPSW");

}

}

}

#判断输入 for 1 restore operations

elsif ($INP eq 1) {

print ' Server IP: (do not enter default for localhost) ';

Chomp (My $a = <STDIN>);

if ($a = = ") {

$a = ' localhost ';

}else{

Print "The server IP you entered is: $a \ n";

}

print ' username: ';

Chomp (My $b = <STDIN>);

Print "The username you entered is: $b \ n";

if ($b eq $username) {

Print "User through!\n";

} else {

Print "Sorry, user name error \ n";

Exit

}

Print "User password:";

Chomp (My $readPsw = <STDIN>);

if ($READPSW eq $passwd) {

print "Correct password!\n";

} else {

Print "Sorry, bad password \ n";

Exit

}

print ' The table to be restored: ';

Chomp (My $d = <STDIN>);

Print "The Backup table you entered: $d \ n";

print ' Backup save path: ';

Chomp (My $e = <STDIN>);

if (-e $e) {

Print "The Backup Save path you entered: $e \ n";

System ("bcp $d in $e-C-t ', '-M 1-a 49152-b 5000-f 1-s $a-u $b-P $READPSW");

}

else {

Print "Sorry, the specified backup file $e does not exist, please verify!\n";

}

}

else{

Print "Please refer to the important hint!\n";

}

System ' pause ';

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/