--path of backup MSSQL\Backup\Production\My_DB\My_DB_200903251116.bak
--epp2admin is creates a backup in the folder under 'Production' and then creates a ---folder for the database name; then dates the files
DECLARE @MailSubject varchar(1000)
DECLARE @AlertMessage varchar(1000)
DECLARE @MailProfile varchar(1000)
DECLARE @HTMLCode VARCHAR(MAX)
DECLARE @CmdString VarChar(1000)
DECLARE @Path varchar(200)
DECLARE @DB sysname
DECLARE @FullPath varchar(400)
SET @MailProfile = (Select TOP 1 Name from msdb.dbo.SysMail_Profile WHERE Name LIKE '%Email Profile')
BEGIN TRY
CREATE TABLE
#Key (KeyValue Varchar(150), KeyData VarChar(150))
INSERT
#KEY
EXECUTE
master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'BackupDirectory'
SELECT
@PATH = KeyData + '\ePP2Admin' from #Key
DROP TABLE
#Key
DECLARE CurDB CURSOR FOR
SELECT
DATABASE_NAME = db_name(s_mf.database_id)
FROM
sys.master_files s_mf
WHERE -- ONLINE
s_mf.state = 0 -- Only look at databases to which we have access
and
has_dbaccess(db_name(s_mf.database_id)) = 1 -- Not master, tempdb or model
and
db_name(s_mf.database_id) not in ('Master','tempdb','model')
group by
s_mf.database_id
order by
1
OPEN CurDB
FETCH CurDB into @DB
while @@fetch_status = 0
begin
set @FullPath = @Path + '\' + @db
exec master..xp_create_subdir @FullPath
set @FullPath = @FullPath + '\' + @DB + '_'
+ datename( yyyy , getdate())
+ right('00' + cast(datepart( mm , getdate()) as varchar(2)) , 2 )
+ right('00' + datename( dd , getdate()), 2 )
+ right('00' + datename( hh , getdate()), 2 )
+ right('00' + datename( mi , getdate()), 2 )
+ '.bak'
Backup DATABASE @DB to disk = @FullPath WITH INIT, BLOCKSIZE = 65536
--SET @CMDString = 'BACKUP DATABASE ' + @DB + ' TO DISK = ''' + @FullPath + ''' WITH INIT, STATS = 10'
--EXEC (@CMDString)
fetch curDB into @DB
end
CLOSE CurDB
deallocate curDB
END TRY
BEGIN CATCH
DECLARE @Error_Number INT
DECLARE @Error_Severity INT
DECLARE @Error_State varchar(100)
DECLARE @Error_Message VarChar(1000)
SELECT
@Error_Number = ERROR_NUMBER(),
@Error_Severity = ERROR_SEVERITY(),
@Error_State = ERROR_STATE(),
@Error_Message = ERROR_MESSAGE();
SELECT @MailSubject =
'ALERT: Error ' + CAST(@Error_Number AS Varchar(5)) +
', Severity ' + CAST(@Error_Severity AS Varchar(2)) +
', State ' + CAST(@Error_State AS Varchar(2)) +
', occured on \\' + @@ServerName
SELECT @HTMLCode = @Error_Message
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,
@recipients = 'SQL_Notify@YourCompany.com',
@subject = @MailSubject,
@Body = @HTMLCode
END CATCH
Posts
