Alerts and Notifications for SQL Server Login, Database User and Role Membership Changes

https://www.mssqltips.com/sqlservertip/5426/alerts-and-notifications-for-sql-server-login-database-user-and-role-membership-changes/

Advertisements

own blog

DECLARE @name VARCHAR(50-- database name   
DECLARE @path VARCHAR(256-- path for backup files   
DECLARE @fileName VARCHAR(256-- filename for backup   
DECLARE @fileDate VARCHAR(20-- used for file name  
 
--Provide the path where all the databases needs to be backed up 
SET @path = 'MyBackupFilePath'   
 
--used to suffix the current date at the end of backup filename 
SELECT @fileDate = REPLACE(CONVERT(VARCHAR(20),GETDATE(),105),'-','')

DECLARE db_cursor CURSOR FOR   
 
--Use this for all database except the system databases and any exclusion you can make 
SELECT name  
FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')   
 
--Uncomment and use this for only specific databases. 
--Those database names you can provide under IN clause 
--SELECT name  
--FROM master.dbo.sysdatabases  
--WHERE name IN ('MyDB1','MyDB2')  
 
 
OPEN db_cursor    
FETCH NEXT FROM db_cursor INTO @name    
 
WHILE @@FETCH_STATUS = 0    
BEGIN    
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'   
       BACKUP DATABASE @name TO DISK = @fileName WITH STATS = 1   
 
       FETCH NEXT FROM db_cursor INTO @name    
END    
 
CLOSE db_cursor    
DEALLOCATE db_cursor

Sql server backup Script for All Database ddmmyyyy without compression

DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name

–Provide the path where all the databases needs to be backed up
SET @path = ‘c:\bck\’

–used to suffix the current date at the end of backup filename
SELECT @fileDate = REPLACE(CONVERT(VARCHAR(20),GETDATE(),105),’-‘,”)

DECLARE db_cursor CURSOR FOR

–Use this for all database except the system databases and any exclusion you can make
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’)

–Uncomment and use this for only specific databases.
–Those database names you can provide under IN clause
–SELECT name
–FROM master.dbo.sysdatabases
–WHERE name IN (‘MyDB1′,’MyDB2’)

 

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName WITH STATS = 1

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

SQL Script to find the database & their files details on a SQL Server

SQL Script to find the database & their files details on a SQL Server

Sometimes we need a SQL Script which can gives the below information about the databases created on a SQL Server for house keeping purpose: –

1) Database Name
2) Database ID
3) Database Files name with their physical location
4) Database Creation Time
5) Users access type for Database
6) Database recovery model
7) Database State (ON/OFF)
8) Database Collation
9) File size on the Disk

http://www.sqlservercentral.com/blogs/vivekssqlnotes/2018/03/30/sql-script-to-find-the-database-their-files-details-on-a-sql-server/