MS SQL

Дата последнего восстановления базы DB_NAME:

select max(restore_date) as restore_date from msdb..restorehistory where destination_database_name = 'DB_NAME'

Дата последнего бэкапа и путь к нему для базы DB_NAME: 

select top 1 a.backup_finish_date, b.physical_device_name from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b on b.media_set_id=a.media_set_id where a.database_name = 'DB_NAME' order by a.backup_finish_date desc

Модель восстановления, владелец, статус, дата создания для базы DB_NAME:

select DatabasePropertyEx('DB_NAME','Recovery') as recovery, suser_sname(sid) as owner, DatabasePropertyEx('DB_NAME','Status') as status, crdate as createed from master..sysdatabases where name = 'DB_NAME'

Другое:

use database
go
sp_configure 'allow updates',1
reconfigure with override
go

sp_configure 'deadlock checking period' ,5000
go


use master
go
create login username
with password='123456',
DEFAULT_DATABASE = master,
DEFAULT_LANGUAGE =us_english,
CHECK_POLICY = OFF
go


use master
go
backup log AAAA with no_log
--backup log dexia with truncate_only
go

use AAAA
go
DBCC SHRINKFILE (New_Log_File,470)
go

sp_helpdb AAAA

sp_helplogins 'user'

#Порты

--Для 2005 и 2008
declare @rc int, @instace_name varchar(20), @dir varchar(5), @reg varchar(100)
set @instace_name = (select @@servicename)
set @reg = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instace_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
exec @rc = master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @reg ,'TcpPort', @dir output
select @dir

--Для 2000
xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp', N'TcpPort'




#Сбросить пароль для всех на 123456

set nocount on
go
use master
go
SELECT 'ALTER LOGIN [' + ltrim(rtrim(log.name)) + '] WITH PASSWORD = 123456, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
      + char(10)
FROM
sys.server_principals AS log
JOIN sys.sql_logins AS sqllog ON sqllog.principal_id = log.principal_id
WHERE log.type = 'S' and log.principal_id > 255
GO

-- alter login LOGIN_NAME with password = '123456', CHECK_POLICY = OFF   -- отдельно для 1 логина

#Password

alter login sa with CHECK_POLICY = OFF
alter login sa with password = 'pfpbr'


#Kill

use master
select 'kill',*  from sysprocesses where dbid=db_id('')

sp_who

#history_of_restore

use msdb
go
select destination_database_name, restore_date
from restorehistory
where destination_database_name = ''
order by destination_database_name, restore_date
desc
go

select max(restore_date) from msdb..restorehistory
where destination_database_name = ''
go

#compatibility_level

use master

go

exec dbo.sp_dbcmptlevel 'AAAA' , 100

go

#restore

sp_helpdb AAAA

restore filelistonly from disk = ''

/*

sp_dboption 'AAAA', 'dbo use only',  true

go

use master

go

restore database AAAA

  from disk = ''

     with replace, 

move 'DATA' to '',

move 'LOG' to '',

stats = 1

go

*/