SQLServer常用命令

■获取能登陆sqlserver 数据库实例的用户和权限(命令一行实行)

SQLCMD.EXE -E -S ".¥实例名" -Q "with ServerPermsAndRoles as (select spr.name as principal_name, spr.type_desc as principal_type, spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,'permission' as security_type,spm.state_desc from sys.server_principals spr inner join sys.server_permissions spm on spr.principal_id = spm.grantee_principal_id where spr.type in ('s', 'u') union all select sp.name as principal_name, sp.type_desc as principal_type, spr.name as security_entity, 'role membership' as security_type, null as state_desc from sys.server_principals sp inner join sys.server_role_members srm on sp.principal_id = srm.member_principal_id inner join sys.server_principals spr on srm.role_principal_id = spr.principal_id where sp.type in ('s', 'u')) select * from ServerPermsAndRoles order by principal_name"

■获取当前的数据库版本信息：

SELECT
SERVERPROPERTY('productversion') as 'Product Version',
SERVERPROPERTY('productlevel') as 'Service Pack',
SERVERPROPERTY('edition') as 'Edition',
SERVERPROPERTY('instancename') as 'Instance',
SERVERPROPERTY('servername') as 'Server Name'

■为数据库testDB创建用户，并赋予sysadmin角色。

 sqlcmd -E -S .\TESTDBI -Q "CREATE LOGIN testuser WITH PASSWORD='abc123$%',DEFAULT_DATABASE=testDB; ALTER SERVER ROLE [sysadmin] ADD MEMBER [testuser];"

■执行sql脚本:
sqlcmd -S .\TESTDBI -U sa -P sa -i C:\test1.sql -o C:\out.txt
sqlcmd -E -S .\TESTDBI -i C:\test1.sql -i C:\test2.sql -o C:\out.txt

▪参数：
-o 表示输出文件路径（不加-o,直接在cmd命令窗口输出).
-S 表示要连接的数据库服务器
-U 表示登录的用户ID，
-P 表示登录密码
-i 表示要执行的脚本文件路径

■数据库备份与恢复:
#备份
sqlcmd.exe -E -S localhost\实例名
BACKUP DATABASE MyDB
TO DISK='MyDB.bak'
WITH INIT
GO
#恢复
sqlcmd.exe -E -S localhost\实例名
RESTORE DATABASE MyDB
FROM DISK = 'MyDB.bak'
WITH REPLACE
GO

或：
sqlcmd -E -S localhost\testDBI
RESTORE DATABASE test
FROM DISK = 'C:\test.bak'
WITH MOVE 'test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.testDBI\MSSQL\DATA\test_DATA.MDF',
MOVE 'test_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.testDBI\MSSQL\DATA\test_LOG.LDF',
REPLACE
GO

■判断sqlserver数据库实例是否存在：HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL