SQLServer 2000 数据库备份还原存储过程_sqlserver 备份数据库命令行-优快云博客

SQLServer 2000 数据库备份还原存储过程:

-- 这个存储过程是在网上找到的，并非原创。

if exists ( select * from sysobjects where type = ' p ' and name = ' usp_backup ' )

drop proc usp_backup

create proc usp_backup

@flag int out,

@backup_db_name varchar ( 128 ),

@filename varchar ( 1000 ) /*路径＋文件名字*/

declare @sql nvarchar ( 4000 ), @par nvarchar ( 1000 )

if not exists ( select * from master..sysdatabases where name = @backup_db_name )

begin

set @flag = 0 /*数据库不存在*/

return

end

else

begin

if right ( @filename , 1 ) <> ' ' and charindex ( ' ' , @filename ) <> 0

begin

set @par = ' @filename varchar(1000) '

set @sql = ' BACKUP DATABASE ' + @backup_db_name + ' to disk=@filename with init '

execute sp_executesql @sql , @par , @filename

set @flag = 1

return

end

else

begin

set @flag = 0 /*参数@filename输入格式错误*/

return

end

if exists ( select * from sysobjects where type = ' fn ' and name = ' fn_GetFilePath ' )

drop function fn_GetFilePath

create function fn_GetFilePath( @filename nvarchar ( 260 ))

returns nvarchar ( 260 )

begin

declare @file_path nvarchar ( 260 ), @filename_reverse nvarchar ( 260 )

set @filename_reverse = reverse ( @filename )

set @file_path = substring ( @filename , 1 , len ( @filename ) + 1 - charindex ( ' ' , @filename_reverse ))

return @file_path

end

if exists ( select * from sysobjects where type = ' p ' and name = ' usp_restore ' )

drop proc usp_restore

CREATE proc usp_restore

@flag int out, /*过程运行的状态标志,是输入参数*/

@restore_db_name nvarchar ( 128 ), /*要恢复的数据名字*/

@filename nvarchar ( 260 ) /*备份文件存放的路径+备份文件名字*/

declare @proc_result tinyint , /*返回系统存储过程xp_cmdshell运行结果*/

@loop_time smallint , /*循环次数*/

@max_ids smallint , /*@tem表的ids列最大数*/

@file_bak_path nvarchar ( 260 ), /*原数据库存放路径*/

@flag_file bit , /*文件存放标志*/

@master_path nvarchar ( 260 ), /*数据库master文件路径*/

@sql nvarchar ( 4000 ),

@par nvarchar ( 1000 ),

@sql_sub nvarchar ( 4000 ),

@sql_cmd nvarchar ( 100 ),

@sql_kill nvarchar ( 100 )

/*判断参数@filename文件格式合法性，以防止用户输入类似d: 或者 c:a 等非法文件名参数@filename里面必须有''并且不以''结尾*/

if right ( @filename , 1 ) <> ' ' and charindex ( ' ' , @filename ) <> 0

begin

set @sql_cmd = ' dir ' + @filename

EXEC @proc_result = master..xp_cmdshell @sql_cmd ,no_output

IF ( @proc_result <> 0 ) /*系统存储过程xp_cmdshell返回代码值:0(成功）或1（失败）*/

begin

set @flag = 0 /*备份文件不存在*/

return /*退出过程*/

end

create table #tem /*创建临时表,保存由备份集内包含的数据库和日志文件列表组成的结果集*/

(

LogicalName nvarchar ( 128 ), /*文件的逻辑名称*/

PhysicalName nvarchar ( 260 ), /*文件的物理名称或操作系统名称*/

Type char ( 1 ), /*数据文件 (D) 或日志文件 (L)*/

FileGroupName nvarchar ( 128 ), /*包含文件的文件组名称*/

[ Size ] numeric( 20 , 0 ), /*当前大小（以字节为单位）*/

[ MaxSize ] numeric( 20 , 0 ) /*允许的最大大小（以字节为单位）*/

)

/*创建表变量，表结构与临时表基本一样就是多了两列,列ids（自增编号列）,列file_path,存放文件的路径*/

declare @tem table

(

ids smallint identity , /*自增编号列*/

LogicalName nvarchar ( 128 ),

PhysicalName nvarchar ( 260 ),

File_path nvarchar ( 260 ),

Type char ( 1 ),

FileGroupName nvarchar ( 128 )

)

insert into #tem

execute ( ' restore filelistonly from disk= ''' + @filename + '''' )

insert into @tem (LogicalName,PhysicalName,File_path,Type,FileGroupName) /*将临时表导入表变量中,并且计算出相应得路径*/

select LogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Type,FileGroupName from #tem

if @@rowcount > 0

begin

drop table #tem

end

set @loop_time = 1

select @max_ids = max (ids) from @tem /*@tem表的ids列最大数*/

while @loop_time <= @max_ids

begin

select @file_bak_path = file_path from @tem where ids = @loop_time

set @sql_cmd = ' dir ' + @file_bak_path

EXEC @proc_result = master..xp_cmdshell @sql_cmd ,no_output

IF ( @proc_result <> 0 ) /*系统存储过程xp_cmdshell返回代码值:0(成功）或1（失败）*/

set @loop_time = @loop_time + 1

else

BREAK /*没有找到备份前数据文件原有存放路径，退出循环*/

end

set @master_path = ''

if @loop_time > @max_ids

set @flag_file = 1 /*备份前数据文件原有存放路径存在*/

else

begin

set @flag_file = 0 /*备份前数据文件原有存放路径不存在*/

select @master_path = dbo.fn_GetFilePath(filename) from master..sysdatabases where name = ' master '

end

/*@flag_file=1时新的数据库文件还是存放在原来路径，否则存放路径和master数据库路径一样*/

set @sql_sub = ''

select @sql_sub = @sql_sub + ' move ''' + LogicalName + ''' to '''

+ case type /*type='d'是数据文件,type='l'是日志文件 */

when ' d ' then case @flag_file

when 1 then File_path

else @master_path

end

when ' l ' then case @flag_file

when 1 then File_path

else @master_path

end

+ case type

when ' d ' then @restore_db_name

+ ' _DATA '

+ convert (sysname,ids) /*给文件编号*/

+ ' . '

+ right (PhysicalName, 3 ) /*给文件加入后缀名,mdf or ndf*/

+ ''' , '

when ' l ' then @restore_db_name

+ ' _LOG '

+ convert (sysname,ids)

+ ' . '

+ right (PhysicalName, 3 )

+ ''' , '

end

from @tem

set @sql = ' RESTORE DATABASE @db_name ' + ' FROM DISK=@filename with '

set @sql = @sql + @sql_sub + ' replace '

set @par = ' @db_name nvarchar(128),@filename nvarchar(260) '

/*关闭相关进程，把相应进程状况导入临时表中*/

select identity ( int , 1 , 1 ) ids, spid into # temp from master..sysprocesses where dbid = db_id ( @restore_db_name )

if @@rowcount > 0 /*找到相应进程*/

begin

select @max_ids = max (ids) from # temp

set @loop_time = 1

while @loop_time <= @max_ids

begin

select @sql_kill = ' kill ' + convert ( nvarchar ( 20 ),spid) from # temp where ids = @loop_time

execute sp_executesql @sql_kill

set @loop_time = @loop_time + 1

end

drop table # temp

execute sp_executesql @sql , @par , @db_name = @restore_db_name , @filename = @filename

set @flag = 1 /*操作成功*/

end

else

begin

set @flag = 0 /*参数@filename输入格式错误*/

end

再加上一个远程备份的:

CREATE procedure usp_copy

begin

declare @filename nvarchar( 200 )

declare @eloname nvarchar( 200 )

declare @data datetime

declare @lastday varchar( 100 )

-- 建立映射

exec master..xp_cmdshell ' net use z: /192.168.1.2 "*" /user:192.168.1.2administrator '

-- 设备异地文件夹路径

set @filename = ' Z:esysdb ' + convert( char ( 8 ),getdate(), 112 ) + convert(varchar( 2 ),getdate(), 108 ) + ' .bak '

print ' esys备份完成 '

set @eloname = ' Z:elogisdb ' + convert( char ( 8 ),getdate(), 112 ) + convert(varchar( 2 ),getdate(), 108 ) + ' .bak '

print ' elogisdb备份完成 '

select @filename

-- 备份开始

BACKUP DATABASE [sanshu_pf] TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N ' addin 备份 ' , NOSKIP , STATS = 10 , NOFORMAT

BACKUP DATABASE [sanshu_pf] TO DISK = @eloname WITH NOINIT , NOUNLOAD , NAME = N ' addin 备份 ' , NOSKIP , STATS = 10 , NOFORMAT

-- 删除前六天备份数据库

set @lastday = ' del z:* ' + convert( char ( 8 ),getdate() - 6 , 112 ) + ' *.* /f '

exec master..xp_cmdshell @lastday

-- 删除映射

exec master..xp_cmdshell ' net use z: /delete '

end

下面是DOS命令NET USE的帮助:

NET USE
[devicename | *] [//computername/sharename[/volume] [password | *]]
        [/USER:[domainname/]username]
        [/USER:[dotted domain name/]username]
        [/USER:[username@dotted domain name]
        [/SMARTCARD]
        [/SAVECRED]
        [[/DELETE] | [/PERSISTENT:{YES | NO}]]

　　DeviceName:指派名称以便连接到资源或指定断开的设备。有两种类型的设备名: 磁盘驱动器号(即 D: 到 Z:} 和打印机(即 LPT1:到 LPT3:}。如果键入星号而不是特定设备名，则系统会指派下一个可用的设备名。这个名称以后可以作为访问共享资源的名称进行引用。

　　//computername:指控制共享资源的计算机的名字。如果计算机名中包含有空字符，就要将双反斜线 (//) 和计算机名一起用引号 (" ")括起来。计算机名可以有1 到 15 个字符。/volume :指定一个服务器上的NetWare卷。用户必须安装 Netware 的客户服务 (Windows 工作站) 或者 Netware 的网关服务(Windows 服务器) 并使之与 NetWare 服务器相连。

　　Password:指定访问共享资源所需的密码。输入星号 (*) 产生一个密码提示在密码提示行处键入密码时不显示密码。

　　/user:在其后指定建立连接时使用的不同于目前登录用户的用户名。

　　DomainName:指定不同于目前登录域的其他域。如果省略则net use使用当前登录的域。

　　注意，/user:后的登录用户和域可以有三种不同的表示形式，分别为domainname/username，dotted domain name/username和username@dotted domain name，其中dotted domain name提指域名的全称，如office.yesky.com，也即域名加域后缀的完全形式。

　　/SAVECRED:指定保留用户名和密码。除非命令提示输入用户名和密码。否则此开关被忽略，

　　/SMARTCARD:指定连接使用在智能卡上的凭据。

　　/delete:取消指定的网络连接。如果使用星号 (*) 指定连接，则所有网络连接均将取消。

　　/persistent:{yes | no}:控制持久网络连接的使用。默认值为最后一次使用的设置。非设备连接不会持久。Yes 将按其建立时的原样保存所有连接，并在下次登录时还原它们。No 则不保存已建立的连接或后续连接。现存的连接在下一次登录时还原。使用 /delete 删除持久连接。

　　Net use命令还有另两种使用格式，分别如下:

　　NET USE {devicename | *} [password | *] /HOME

　　NET USE [/PERSISTENT:{YES | NO}]

　　其中第一种命令格式将用户连到其域的主目录并将主目录映射为设备名DeviceName。后一种格式用来修改持久连接的使用。

　　下面是两个例子:

　　要连接用户标识符 Dan 就好像是通过 Accounts 域创建的连接:

　　net use d://server/share /user:Accounts/Dan

　　要断开 //Financial/Public 目录: