CMD命令操作MSSQL2005数据库(命令整理)

打开cmd命令
执行：sqlcmd/?

这是sqlcmd命令的一些帮助信息
通过上面可以知道怎么连数据库了
执行：sqlcmd -s 服务器地址 -d 数据库名称 -u 用户密码 -p 密码
以下是操作数据库的一下命令

0. 创建数据库
create database db001
1. 创建用户
create login user1
with password = 'user_pw';
2. 修改数据的所有者
use db001
exec sp_changedbowner 'user1'
go
3. 设置read_committed_snapshot
alter database [db001] set read_committed_snapshot on
go
4.修改字符集
alter database db001 collate sql_latin1_general_cp437_cs_as
go
5. 获取所有数据库名:
select name from master..sysdatabases
6 . 获取所有表名:
select name from sysobjects where type='u'
xtype='u':表示所有用户表;
xtype='s':表示所有系统表;
7. 获取所有字段名:
select name from syscolumns where id=object_id('tablename')
8. 查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%tablename%'
9. 查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = n'tablename'
10. 获取数据库文件路径
select ltrim(rtrim(filename)) from dbname ..sysfiles where charindex('mdf',filename)>0
or
select ltrim(rtrim(filename)) from dbname ..sysfiles where charindex('ldf',filename)>0

mssql2005_数据库备份语句

--完整备份
backup database northwindcs
to disk='g:\backup\northwindcs_full_20070908.bak'

--差异备份
backup database northwindcs
to disk='g:\backup\northwindcs_diff_20070908.bak'
with differential

--日志备份，默认截断日志
backup log northwindcs
to disk='g:\backup\northwindcs_log_20070908.bak'

--日志备份，不截断日志
backup log northwindcs
to disk='g:\backup\northwindcs_log_20070908.bak'
with no_truncate

--截断日志不保留
backup log northwindcs
with no_log

--或者
backup log northwindcs
with truncate_only
--截断之后日志文件不会变小
--有必要可以进行收缩

--文件备份
exec sp_helpdb northwindcs --查看数据文件
backup database northwindcs
file='northwindcs' --数据文件的逻辑名
to disk='g:\backup\northwindcs_file_20070908.bak'

--文件组备份
exec sp_helpdb northwindcs --查看数据文件
backup database northwindcs
filegroup='primary' --数据文件的逻辑名
to disk='g:\backup\northwindcs_filegroup_20070908.bak'
with init

--分割备份到多个目标
--恢复的时候不允许丢失任何一个目标
backup database northwindcs
to disk='g:\backup\northwindcs_full_1.bak'
,disk='g:\backup\northwindcs_full_2.bak'

--镜像备份
--每个目标都是相同的
backup database northwindcs
to disk='g:\backup\northwindcs_mirror_1.bak'
mirror
to disk='g:\backup\northwindcs_mirror_2.bak'
with format --第一次做镜像备份的时候格式化目标

--镜像备份到本地和远程
backup database northwindcs
to disk='g:\backup\northwindcs_mirror_1.bak'
mirror
to disk='\\192.168.1.200\backup\northwindcs_mirror_2.bak'
with format

--每天生成一个备份文件
declare @path nvarchar(2000)
set @path ='g:\backup\northwindcs_full_'
+convert(nvarchar,getdate(),112)+'.bak'

backup database northwindcs
to disk=@path

--从norecovery或者
--standby模式恢复数据库为可用
restore database northwindcs_bak
with recovery

--查看目标备份中的备份集
restore headeronly
from disk ='g:\backup\northwindcs_full_20070908.bak'

--查看目标备份的第一个备份集的信息
restore filelistonly
from disk ='g:\backup\northwindcs_full_20070908_2.bak'
with file=1

--查看目标备份的卷标
restore labelonly
from disk ='g:\backup\northwindcs_full_20070908_2.bak'

--备份设置密码保护备份
backup database northwindcs
to disk='g:\backup\northwindcs_full_20070908.bak'
with password = '123',init

restore database northwindcs
from disk='g:\backup\northwindcs_full_20070908.bak'
with password = '123'