How to find members in Log in group in SQL Server
The following Query will display the members in a group
EXEC xp_logininfo '<Group_Name>', 'members'
example:
EXEC xp_logininfo 'BUILTIN\Administrators', 'members'
How to find services are running under which service account in SQL Server
If you want to get the list for all servers at a time register all servers in one centralized server and run the below script.
DECLARE @DBEngineLogin VARCHAR(100)
DECLARE @AgentLogin VARCHAR(100)
EXECUTE master.dbo.xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name = 'ObjectName',
@value = @DBEngineLogin OUTPUT
EXECUTE master.dbo.xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
@value_name = 'ObjectName',
@value = @AgentLogin OUTPUT
SELECT @@SERVERNAME,[DBEngineLogin] = @DBEngineLogin, [AgentLogin] = @AgentLogin
Script To find files used space in SQL Server
The following script will give you the space used for all files in server:
create TABLE #DBInfo
(
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] SELECT
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO #DBInfo
(
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
FreeSpaceMB,
FreeSpacePct,
PollDate)
EXEC sp_MSForEachDB @command
SELECT
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
FreeSpaceMB,
FreeSpacePct,
PollDate
FROM #DBInfo
ORDER BY
DatabaseName
drop table #DBInfo
Auto Restore Script
Hi, Today I am going to share you most useful script for restoring a database from backup file.
Suppose if your database having more number of data & log files it will be bit tough to restore using with MOVE option. Below script will generate the restoration script.
use [ReportServer] --current database
go
declare @dbname varchar(100)
declare @filepath varchar(500)
--declare @islightspeed int
set @dbname ='ReportServer' --databasename
set @filepath ='\\mhcalbq34clu1bu\BACKUP\DAILY_BACKUP\AANGSQL\QNXT_REPORTDB_WA_DIFF_DB.BAK' --backup file path
--set @islightspeed =1 -- =1 lightspeedscript else SQL script
--if @islightspeed =0
begin
select 'restore database '+@dbname +' from disk ='+''''+@filepath +''' with'
union all
select 'move' +''''+name +'''' +' to ' +''''+physical_name + ''',' from sys.database_files
union all
select 'replace,stats=10'
end
-- else
-- begin
-- select 'exec master.dbo.xp_restore_database @database =N'+''''+@dbname +''''+',
--'+ '@filename = N'+''''+@filepath +''','
--union all
--select '@filenumber = 1,
--@with = N''REPLACE'',
--@with = N''STATS = 10'','
--union all
--select '@with = N''MOVE N''''' + name +'''''' +' To N''''' + physical_name +''''''',' from sys.database_files
--union all
--select '@affinity = 0,
--@logging = 0'
--end
