sql 数据库备份还原

-------------还原----------------------

USE [myDB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBRestore]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DBRestore]
GO
/****** 对象:  StoredProcedure [dbo].[DBRestore]    脚本日期: 06/12/2010 14:20:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[DBRestore]
 -- Add the parameters for the stored procedure here
 @FileName varchar(500)
 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.

  --ALTER DATABASE myDB  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  restore database db from disk= @FileName  WITH  move 'myDB_dat' to 'c:/Program Files/microsoft sql server/mssql.1/mssql/data/myDB.mdf', move 'myDB_log' to 'c:/Program Files/microsoft sql server/mssql.1/mssql/data/myDB.ldf',  REPLACE

END

 

------------------------备份---

backup database myDB to disk='" + name + "' with format

 

 

----------------------设置多用户

USE master
GO
DECLARE @SQL VARCHAR(MAX);
SET @SQL=''
SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)
from master..sysprocesses
WHERE dbid=DB_ID('hotel');
EXEC(@SQL);
GO
ALTER DATABASE hotel SET MULTI_USER

 

 

IMMEDIATE 单用户

 

 

第二种方法：添加引用：Microsoft.SqlServer.Smo;Microsoft.SqlServer.SmoExtend

 

 

 using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Common;

    public class DBSqlSMO
    {
        private static string connStr;

        public static string ConnStr
        {
            get
            {
                if (connStr == null)
                    connStr = DataAccess.connectionString;
                return connStr;
            }
            set { connStr = value; }
        }

        static string serverName = "localhost";
        static string login = "";
        static string psw = "";
        static string database = "";
        static bool secure = false;
        static void getSet()
        {
            string[] strs = ConnStr.Split(';');

            foreach (string str1 in strs)
            {
                string str = str1.Trim();
                if (string.IsNullOrEmpty(str))
                    continue;
                string[] s = str.Split('=');
                string s1 = s[0].ToLower().Trim();
                if (s1 == "data source")
                    serverName = s[1].Trim();
                else if (s1 == "user id")
                    login = s[1].Trim();
                else if (s1 == "password")
                    psw = s[1].Trim();
                else if (s1 == "initial catalog")
                    database = s[1].Trim();
                else if (s1 == "integrated security")
                    secure = s[1].ToLower().Trim() == "true";
            }
        }

        public static void Backup(string name, PercentCompleteEventHandler backup_PercentComplete, ServerMessageEventHandler backup_Complete)
        {
            getSet();
            Microsoft.SqlServer.Management.Common.ServerConnection conn = new Microsoft.SqlServer.Management.Common.ServerConnection(new SqlConnection(ConnStr));
            Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(conn);
            Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];
            Microsoft.SqlServer.Management.Smo.Backup backup = new Backup();
            backup.Devices.AddDevice(name, DeviceType.File);
            backup.Database = database;
            if (backup_PercentComplete != null)
            {
                backup.PercentCompleteNotification = 1;
                backup.PercentComplete  += new PercentCompleteEventHandler(backup_PercentComplete);
            }
            if (backup_Complete != null)
                backup.Complete += new ServerMessageEventHandler(backup_Complete);
            backup.SqlBackup(server);
        }

        public static void Restore(string filename)
        {
            getSet();
            Restore restore = new  Restore ();
            Microsoft.SqlServer.Management.Common.ServerConnection conn = new Microsoft.SqlServer.Management.Common.ServerConnection(new SqlConnection(ConnStr));
            Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(conn);
            restore.Action = RestoreActionType.Database;
            restore.Database = database;
            restore.ReplaceDatabase = true;
            restore.Devices.AddDevice(filename, DeviceType.File);
            server.KillAllProcesses(database);
            restore.SqlRestore(server);
        }
    }

 

好了。

 

 

 

 

 

select   filename   from   master.dbo.sysdatabases   where   name   =   @databasename

 

SQL语句获取数据库文件全路径

select   filename   from   master.dbo.sysdatabases   where   name   =   @databasename

一些有用的系统存储过程及用法  
  ---------------------------  
  得到SQL   SERVER   的服务器名  
  select   convert(sysname,   serverproperty(N'servername'))  
   
  读取键值  
  xp_instance_regread   N'HKEY_LOCAL_MACHINE',   N'SOFTWARE/Microsoft/MSSQLServer/Setup',   N'SQLPath'  
   
  得到SQL   SERVER   平台信息  
  xp_msver   N'ProductVersion',   N'Language',   N'Platform',   N'WindowsVersion',   N'ProcessorCount',   N'PhysicalMemory'  
   
  得到SQL   SERVER实例的登陆模式  
  xp_instance_regread   N'HKEY_LOCAL_MACHINE',   N'SOFTWARE/Microsoft/MSSQLServer/MSSQLServer',   'LoginMode'  
  LoginMode=2则为混合认证=1缺省   nt认证   =0   sa认证  
  The   login   security   mode;   0   indicates   Normal,   1   indicates   Windows   NT   Integrated,   and   2   indicates   Mixed.  
   
  修改SQL   SERVER实例的登陆模式  
  xp_instance_regwrite   N'HKEY_LOCAL_MACHINE',   N'SOFTWARE/Microsoft/MSSQLServer/MSSQLServer',   'LoginMode',   N'REG_DWORD',   1  
  1---Windows认证模式  
  2---SQL和Windows认证模式  
   
  得到sql   server   服务器名，和域名列表  
  xp_ntsec_enumdomains  
   
  exec   sp_grantdbaccess   N'zhang',   N'zhang'  
  exec   sp_droplogin   N'zhang'  
  exec   sp_revokedbaccess   N'zhang'  
  exec   sp_dbcmptlevel   N'dbname'  
   
  sp_stored_procedures    
  得到存储过程列表  
   
  xp_availablemedia   2  
  得到硬盘分区信息  
   
  EXECUTE   master.dbo.xp_dirtree   N'E:/',   1,   1    
  得到E:/下的文件列表  
   
  EXECUTE   master.dbo.xp_fileexist   N'F:/Program   Files/Microsoft   SQL   Server/MSSQL/BACKUP/fdsa.dat'  
  文件是否存在  
   
  backup   log   database_name   with   NO_LOG|TRUNCATE_ONLY    
  截断事务日志  
   
  DBCC   SHRINKDATABASE   database_name  
  收缩数据库  
   
  exec   sp_addumpdevice   N'disk',   N'bakdevice',   N'D:/BACKUP/bakdevice'  
  添加备份设备  
  exec   sp_dropdevice   N'bakdevice'  
  删除备份设备  
   
  xp_instance_regread   N'HKEY_CURRENT_USER',   N'Software/Microsoft/MSSQLServer',   N'LastBackupFileDir'  
  上次备份的路径  
   
  xp_instance_regwrite   N'HKEY_CURRENT_USER',   N'Software/Microsoft/MSSQLServer',   N'LastBackupFileDir',REG_SZ,   N'D:/Program   Files/Microsoft   SQL   Server/MSSQL$FANHUI/BACKUP/'  
  改写备份路径  
   
  sp_rename   'tablename.id1','id'  
  更改字段名