ITPub博客

首页 > 数据库 > SQL Server > Mssql2k之数据库管理

Mssql2k之数据库管理

原创 SQL Server 作者:kyle 时间:2019-05-30 20:30:07 0 删除 编辑
 总则:所有在企业管理器(简称EM)里的窗口操作都能在查询分析器(简称QA)里用SQL语句实现(语句能够实现窗口操作不能实现的功能,如修改数据库名称就非得用sp_renamedb来实现),在QA里选中关键字同时键Shift+F1能得到实时的联机帮助,绝大多数SQL语句先写日志后操作。[@more@]

一、基础知识
 Mssqlserver2k有4个版本,分别为企业版、标准版、开发版和个人版。大型数据库管理系统中,仅Mssqlserver只支持windows操作系统,不支持其他操作系统。
 企业版和标准版用于生产数据库服务器,仅支持windows系列操作系统server以上的版本,两者的区别在于企业版支持更多的cpu个数和内存容量,性能上也提供更多的调优选项。
 开发版和个人版仅供个人学习、编程使用,均不能用于生产数据库服务器。个人版支持windows系列操作系统所有版本,开发版除win98外支持windows的其他任何版本,开发版拥有企业版的所有功能,个人版类似标准版。
 请将Mssqlserver2k安装在非操作系统盘,理由:
 1、不会因系统崩溃而导致数据库物理文件丢失
 2、随着应用时间的推移,数据膨胀会引起操作系统盘空间减少而导致服务器I/O读取速度慢和存储空间不足。

二、四个系统数据库的介绍
 1、Master数据库
 Master数据库记录了sqlserver所有的服务器级系统信息,所有的注册帐户和密码,以及所有的系统设置信息,还记录了所有用户定义数据库的存储位置和初始化信息。
 2、Tempdb数据库
 Tempdb记录了所有的临时表、临时数据和临时创建的存储过程。Tempdb数据库是一个全局资源,没有专门的权限限制,允许所有可以连上sqlserver服务器的用户使用。
 在Tempdb数据库存放的所有数据信息都是临时的。每当连接断开时,所有的临时表和临时存储过程都将自动丢弃。每次系统启动时sqlserver都会根据Model数据库重新创建Tempdb数据库。
 3、Model数据库
 Model数据库是用户建立新数据库的模板,它包含了将复制到每个用户数据库中去的系统表。每当创建数据库的语句create database执行时,服务器总是通过复制model数据库来建立新数据库的前面部分,新数据库的后面部分被初始化成空白的数据页,以供用户存放数据。
 4、Msdb数据库
 Msdb数据库主要被sqlserver agent用来进行复制、作业调度以及管理报警等活动。该数据库常被用来通过调度任务排除故障。
 5、说明:在具体应用中可以在EM里将这四个系统数据库隐藏起来,可以避免用户误操作,也可以起到让用户知道操作系统数据库带来的后果。(ps:至于pubs和northwind是微软用来讲解使用方法的练习数据库,可删除)

三、三种备份和恢复
 1、通过EM的备份和还原数据库。
 2、在QA里执行sp_detach_db 'dbname',true分离数据库,将操作系统文件*.mdf(主数据文件)和*.ldf(日志文件)拷贝出来,当然你也可以通过停止sqlserver服务来拷贝操作系统文件。
  通过sp_attach_db(两个操作系统文件都完好)或sp_attach_single_file_db(主数据文件完好,日志文件破坏)来附加数据库(如果在附加中有问题,参考这里:http://blog.itpub.net/post/1626/11352)。
  也可以通过EM来附加数据库。
 3、QA里执行backup database 'dbname' to disk='路径'来备份数据库,restore database 'dbname' from disk='路径'来还原数据库。(以上提到的sql语句里的符号如''等都应该是半角的。这里的操作实际上是上面1里对应的sql语句操作。)
 4、说明:人为手工的备份要有规范。例如遵循这样的格式:dbname+year+month+day+manipulator,如db20050218kyle,文件存放路径统一在非操作系统盘里,例如d盘,文件夹统一命名,例如backup。
 5、以上3种备份方式都针对本地备份。异地备份(见http://blog.itpub.net/post/1626/9281)。为了防止不可预知因素导致数据丢失,可将备份文件另外存盘或刻录等。
  养成对数据库的任何操作之前备份该数据库的习惯。

四、两种数据导入导出方法
 1、通过DTS来做(可单表、多表操作,提取的数据带列名)
 2、bcp实用工具来做(单表操作,提取的数据不带列名)
  例如(hostname表示机器名,user表示sqlserver登陆用户名,resu表示对应的用户登陆的密码):
  exec master..xp_cmdshell 'bcp dbname..table_name out d:\test.xls -c -Shostname -Uuser -Presu'
 3、T-sql语句做(单表操作)。以下实例针对excel和mssql的互导:
  ⑴--excel数据导入mssql
  select * into newtable from opendatasource( 'microsoft.jet.oledb.4.0','data source="d:\test.xls";user id=admin;password=;extended properties=excel 5.0')...[sheet1$]
  --newtable为mssql目标表,d:\test.xls为excel文件名,sheet1$为存放excel数据的工作表
  ⑵--mssql数据导出excel
  insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="d:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]([姓名],[性别],[学号])
  select xm,xh,xb from pantoschoolxfz..xs_student
  -- d:\test.xls为excel文件名,要预先建好,并且在工作表Sheet1里预先建好“姓名,性别,学号”这3列。提取的数据一一对应好。

五、数据库维护计划和作业的应用(前提都是sqlserver agent服务要启动)
 1、数据库维护计划
 2、作业(见http://blog.itpub.net/post/1626/9281

六、安全性介绍
 1、两种登陆方式:
  标准登陆方式(sqlserver和windows),采用sqlserver提供的用户名和密码登陆连接,可用sp_denylogin 'builtin\administrators'拒绝操作系统管理员登陆连接(sp_grantlogin 'builtin\administrators'反转),也称非信任登陆机制;这种认证方式是两种方式中最安全的。
  集成登陆方式(仅windows),将windows的用户和工作组映射为sqlserver的登陆方式,也称信任机制。
 2、一个特殊帐户:sa,为系统默认帐户,不能删除,拥有最高的管理权限,可以执行sqlserver服务器范围内的所有操作,所以一定要给sa加上密码,密码推荐不少于8位,最好是字母、数字和特殊符号的组合。
 3、两个特殊数据库用户:
  dbo,数据库的拥有者,在安装sqlserver时,被设置到model数据库中,不能被删除,所以dbo在每个数据库中都存在。dbo是数据库的最高权力者,对应于创建该数据库的登陆用户,即所有的数据库的dbo都对应于sa帐户;
  guest,这个用户可以使任何已经登陆到sqlserver服务器的用户都可以访问数据库,即使它还没有成为本数据库的用户。所有的系统数据库除model以外都有guest用户。所有新建的数据库都没有这个用户,如果有必要添加guest用户,请用sp_grantdbaccess来明确建立这个用户。
 4、还原数据库的时候之所以要删除本数据库的用户如user,然后在安全性→登陆里重新建这个用户和指定相应的访问权限,是因为这个用户在master里不存在。
  当然你也可以用sp_addlogin 'user','resu'来新建user用户,sp_change_users_login 'update_one','user','user'来指定在master中的对应。
 5、具有system administrators服务器角色的成员拥有与sa一样的权限,具有db_owner数据库角色的用户具有对本数据库的完全操作权限。

七、sql脚本的生成
 说明:可以选择生成某个具体数据库对象的脚本,也可以生成整个数据库对象的脚本。

八、事件探查器的应用
 说明:追踪对后台数据库的每一个请求,以此可以定位前台页面的哪个属性对应后台表的哪个字段。

九、日志的截断
 backup log dbname with no_log
 dbcc shrinkdatabase('dbname')
 说明:如果是将这段语句放在job里,那么要将此job的执行时间设置在所有其他job之后,以保证其他job产生的日志也可被截断。

十、服务器网络实用工具和客户端网络实用工具
 1、服务器网络实用工具相当于oracle的监听器。功能用于监听所有访问sqlserver实例的网络协议栈,并根据配置决定Net-Library 顺序。
 通过mssql自带的服务器网络实用工具可改变sqlserver默认访问端口1433。
 sqlserver服务使用两个端口:TCP-1433、UDP-1434。其中1433用于供sqlserver对外提供服务,1434用于向请求者返回sqlserver使用了那个TCP/IP端口。
 如果改变了端口号,访问sqlserver要使用IP+端口号的形式。
 服务器网络实用工具里勾选“隐藏服务器”,将忽略已设置好的的默认端口号,强制监听TCP端口号2433。作用是让客户端无法通过枚举服务器来看到这台服务器,起到保护的作用,且不影响连接。
 2、客户端网络实用工具根据“服务器别名配置”指定的协议和参数来跟sqlserver实例进行匹配连接,注意服务器别名要跟sqlserver服务器匹配,使用的协议要一样。这里的功能类似windows下hosts文件里指定IP和机器名的对应,起到自动解析作用。

十一、误操作恢复
 见http://kyle.itpub.net/post/1626/9116

十二、其他
 1、查看版本,在QA里执行:select @@version,如结果中出现Mssqlserver2k-8.00.194的描述,表示此版本没有打过补丁。请备份数据库后安装sp4补丁。
 2、查看数据库所存放的路径,在QA里执行:select name,filename from master..sysdatabases
 3、在dos下可使用osql或isql来操纵sqlserver,osql可以运行isql无法运行的脚本。更多使用信息查看sqlserver联机帮助或在dos下通过空格加斜杠问号得到更具体的帮助,例如:osql /?
 4、sqlserver默认使用1433端口。
 5、sqlserver agent服务使用TCP-1625、TCP-1640端口提供服务。
 6、查询分析器,通过1601端口访问1433,连接sqlserver

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/68303/viewspace-787667/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-01-12

  • 博文量
    145
  • 访问量
    110038