常用sql

1、 sqlserver导出数据到Excel

无标题：

EXEC master..xp_cmdshell 'bcp "SELECT * FROM wsbm..lsb" queryout C:/Book2.xls -c -S"(local)" -U"sa" -P"malong"'

 

有标题：

EXEC master..xp_cmdshell 'bcp "select ''data1'' ,''data2'' union all SELECT data1,data2 FROM wsbm..lsb" queryout C:/Book2.xls -c -S"(local)" -U"sa" -P"malong"'

2、数据备份

backup database cars to disk='"+this.txt_lj.Text.Trim()+"' with init

3、数据还原

public bool backupdata(string path)
  {
   string strconn="Server="+server.ToString()+";Database=master;User ID="+sa.ToString()+";Password="+pass.ToString()+";Trusted_Connection=False";
   SqlConnection back_conn = new SqlConnection(strconn);
   back_conn.Open();

   //KILL DataBase Process
   SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='cars'", back_conn);
   SqlDataReader dr;
   dr = cmd.ExecuteReader();
   ArrayList list = new ArrayList();
   while(dr.Read())
   {
    list.Add(dr.GetInt16(0));
   }
   dr.Close();
   for(int i = 0; i < list.Count; i++)
   {
    cmd = new SqlCommand(string.Format("KILL {0}", list[i]), back_conn);
    cmd.ExecuteNonQuery();
   }

   SqlCommand cmdRT = new SqlCommand();
   cmdRT.CommandType = CommandType.Text;
   cmdRT.Connection = back_conn;
   cmdRT.CommandText = @"restore database cars from disk='"+path+"'";

   try
   {
    cmdRT.ExecuteNonQuery();    
    return true;
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message);
    return false;
   }
   finally
   {
    back_conn.Close();
   }

  }