SQL

1. Query to set Single user to Multi user in Sql server

exec sp_dboption 'epicor905', 'single user', 'FALSE'

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid where d.name = 'epicor905'

kill spid

ALTER DATABASE epicor905 SET MULTI_USER

2. SQL Server scheduled Backup and the files moved to old folder then deleting old backup

Step 1 :

declare @sql as nvarchar(1000)

set @sql='BACKUP DATABASE epicor905 TO DISK = ''D:\Backup\Today\Epicor905 -'+CONVERT(VARCHAR(8), GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':','')+'.bak'''

exec sp_executesql @sql

Step 2 :

D:\Morellato\Backup\movefiles.bat

Create a folder Today and Old in the server below path

D:\Backup

Create a file Movefiles and Oldfile.bak and put it in the D:\Backup\ folder

Movefiles

d:

cd "D:\Backup\Today"

forfiles /M *.bak /D -1 /C "cmd /C move @file ..\Old\"

cd "D:\Backup\Old"

copy "D:\Backup\oldfile.bak" .

forfiles /M *.bak /D -10 /C "cmd /C del @file"

save as a batch file

3. Query to find last restore date and time in Sql server

Declare @DB sysname = 'EpicorPilot905'
Select * from msdb.dbo.restorehistroy where destination_database_name = @DB;
(or)

DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 rsh.user_name AS [Restored By],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type 
 END AS [Restore Type],
 rsh.restore_date AS [Restore Started],
 bmf.physical_device_name AS [Restored From], 
 rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
GO

4. How to start SQL Server Agent when Agent show disabled

Step 1.
Run sp_configure to check 'Agent XPs' value.

EXEC SP_CONFIGURE 'Agent XPs'

Step 2The above screenshot shows that advanced options is not enabled on this instance, so we must first enable advanced option to see all of the advanced configuration values.

EXEC SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'show advanced options'

Step 3Run sp_configure again to check the value for Agent XPs. Here we can see the run value is set to 0.

EXEC SP_CONFIGURE 'Agent XPs'

Now we need to change this setting from 0 to 1 to run SQL Server Agent in SQL Server Management Studio.

EXEC SP_CONFIGURE 'Agent XPs',1
GO
RECONFIGURE

Step 4

Now restart your SQL Server Agent service from SQL Server Configuration Manager. This time the service should come up and we can successfully access all of the content for SQL Server Agent.

5. SQL Server Replace Multiple Spaces in String with Single Space

DECLARE @str varchar(150)

SET @str='Hello    Welcome   to   Aspdotnet-suresh.com'

Select REPLACE(REPLACE(REPLACE(@str,' ','{}'),'}{',''),'{}',' ')