远程数据库备份还原

备份环境：把数据库服务器(192.168.30.8)的数据库(db)备份到(192.168.30.145)的盘符下

1、首先,做一个与客户端的映射

exec master..xp_cmdshell

'net use z: //192.168.30.145/c$ "密码" /user:192.168.30.145/administrator'

 

说明：

z: 是映射网络路径对应本机的盘符,与下面的备份对应

//192.168.30.145/c$    是要映射的网络路径

192.168.30.145/administrator    192.168.30.145是远程的计算机名,administrator是登陆的用户名

密码     上面指定的administrator用户的密码

 

2、进行数据库备份

backup database TEST to disk='z:/Test.bak'

3、备份完成后删除映射

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

 

用sql语句表示如下（变量）：

远程数据库的备份：

conn.Open();

    commandStr = "exec master..xp_cmdshell 'net use z: " + ip + "//" + filePath + " /"" + pwd + "/" /user:" + ip + "//" + userName + "';";

    commandStr += "backup database "+dbName +" to disk='z://" + fileName + ".bak';";

    commandStr += "exec master..xp_cmdshell 'net use z: /delete';";

    SqlCommand mycommand = new SqlCommand(commandStr);

    mycommand.Connection = conn;

    mycommand.CommandTimeout = 100000000;

    mycommand.ExecuteNonQuery();   

    conn.Close();

 

 

远程数据库的还原：

commandStr = "use   master;exec master..xp_cmdshell 'net use z: " + ip + "//" + filePath + " /"" + pwd + "/" /user:" + ip + "//" + userName + "';";

    commandStr += "alter database "+dbName +" set single_user with rollback immediate;";

    commandStr += "RESTORE DATABASE " + dbName + " FROM DISK   ='z://" + fileName + "';";

    commandStr += " alter database " + dbName + " set multi_user;";

    commandStr += "exec master..xp_cmdshell 'net use z: /delete';";