perl脚本备份还原sqlserver

爱被打了一巴掌 2021-12-14 10:31 302阅读 0赞

写了一个perl脚本灵活来备份或还原。

1.将config.txt保存到c盘根目录

username:sa  
password:passwd123!

根据实际情况修改该文件内容

2.执行bcp.pl

\#!C:\\Perl64\\bin  
\#通过BCP备份和还原SQLSERVER指定的表  
use 5.014;

\#加载用户和密码配置文件  
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 $test1=substr($test,$begincount+1,$count);  
\#print "$test1";  
chomp($username=$test1);  
\}  
\}  
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 $test2=substr($test,$begincount+1,$count);  
\#print "$test2";  
chomp($passwd=$test2);  
\}  
\}  
close(FILEH);  
print "-------------------------------------------------------------------\\n";  
print "请首先检查用户和密码配置文件config.txt保存路径是否为$dir$filename\\n";  
print "-------------------------------------------------------------------\\n";

print "重要提示:备份表操作请输入0;还原表操作请输入1\\n";  
print "请输入操作代码:";  
chomp(my $inp=<STDIN>);  
print "你输入的操作代码为$inp\\n";

\#判断输入为0即备份操作  
if ($inp eq 0)\{  
print '服务器IP:(不输入默认为localhost)';  
chomp(my $a = <STDIN>);  
if ($a=='')\{  
$a='localhost';  
\}else\{  
print "你输入的服务器IP为:$a\\n";  
\}

print '用户名:';  
chomp(my $b = <STDIN>);  
print "你输入的用户名为:$b\\n";  
if($b eq $username) \{

print "用户通过!\\n";

\} else \{  
print "对不起,用户名错误\\n";  
exit;  
\}

print "用户密码: ";  
chomp (my $readPsw = <STDIN>);  
if($readPsw eq $passwd) \{

print "密码正确!\\n";

\} else \{  
print "对不起,密码错误\\n";  
exit;  
\}

print "重要提示:备份表的表名格式为:test.dbo.t1\\n";  
print '备份的表:';  
chomp(my $d = <STDIN>);  
print "你输入的备份的表:$d\\n";

print '保存路径:';  
chomp(my $e = <STDIN>);  
print "你输入的保存路径:$e\\n";

if (-e $e)\{  
print "$e已经存在!是否删除?(y删除;n不删除)\\n";  
chomp(my $i=<STDIN>);  
given ($i)  
\{  
when ("y") \{  
unlink $e;  
print "开始备份表!\\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 "-----------备份已经终止-----------\\n";  
print "指定文件存在,请重新输入文件名!\\n";  
\}  
default \{print "只能输入y或n!\\n";\}  
\}  
\}  
else\{  
\{  
system (" bcp $d out $e -c -t ',' -m 1 -a 49152 -b 5000 -F 1 -S $a -U $b -P $readPsw ");  
\}  
\}  
\}

\#判断输入为1还原操作  
elsif($inp eq 1)\{  
print '服务器IP:(不输入默认为localhost)';  
chomp(my $a = <STDIN>);  
if ($a=='')\{  
$a='localhost';  
\}else\{  
print "你输入的服务器IP为:$a\\n";  
\}  
print '用户名:';  
chomp(my $b = <STDIN>);  
print "你输入的用户名为:$b\\n";  
if($b eq $username) \{

print "用户通过!\\n";

\} else \{  
print "对不起,用户名错误\\n";  
exit;  
\}

print "用户密码: ";  
chomp (my $readPsw = <STDIN>);  
if($readPsw eq $passwd) \{

print "密码正确!\\n";

\} else \{  
print "对不起,密码错误\\n";  
exit;  
\}

print '需要还原的表:';  
chomp(my $d = <STDIN>);  
print "你输入的备份的表:$d\\n";

print '备份保存路径:';  
chomp(my $e = <STDIN>);  
if (-e $e) \{  
print "你输入的备份保存路径:$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 "对不起,指定的备份文件$e不存在,请核实!\\n";  
\}  
\}  
else\{  
print "请参考重要提示!\\n";  
\}  
system 'pause';

转载于:https://www.cnblogs.com/javaEEspring/archive/2012/02/22/2523335.html