May 8, 2017 at 6:45 am
I'm transitioning from sql maintenance plan to scripts mainly so I can format the backup file name to what I want. My issue is when my script runs the filename isnโt getting into the msdb..backupset table. Is there a setting or something Iโm missing in my scripts? The script is pretty straight forward and simple.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupDatabases_USER] Script Date: 5/8/2017 8:44:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[BackupDatabases_USER] @backuplocation VARCHAR(1000)
AS
--declare @BackupLocation varchar(1000)
--set @BackupLocation= 'D:\MSSQL\Backup\Full'
IF( Substring(Reverse(@backuplocation), 1, 1) <> '\' )
SET @backuplocation = @backuplocation + '\'
DECLARE UserDatabases_CTE_Cursor CURSOR FOR
-- Selecting user database names.
SELECT sd.name--,SizeMeg
FROM sys.sysdatabases sd
JOIN (SELECT database_id
--,name
,
Cast(( ( Sum(size) * 8 ) / 1024.0 ) AS DECIMAL(18, 2)) sizemeg
FROM sys.master_files
WHERE type_desc = 'rows'
and database_id>4
GROUP BY database_id) bak
ON bak.database_id = sd.[dbid]
OPEN UserDatabases_CTE_Cursor
DECLARE @dbname VARCHAR(100);
DECLARE @BackupFileName VARCHAR(100);
DECLARE @backuppath VARCHAR(100);
DECLARE @backupquery VARCHAR(500);
-- make sure that the below path exists
SET @backuppath = @backuplocation
FETCH NEXT FROM UserDatabases_CTE_Cursor INTO @dbname
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
set @BackupFileName = 'FullBackup__' + @@servername + '__' +@dbname + '__'+ replace(replace(replace(replace(replace(convert(varchar(50), getdate(), 126),'T',''),' ',''),'-',''),':',''),'.','')
-- Backup SQL statement
SET @backupquery = 'backup database ' + @dbname + ' to disk = '''
+ @backuppath + @BackupFileName
+ '.bak'''
-- Print SQL statement
PRINT @backupquery
-- Execute backup script
EXEC (@backupquery)
-- Get next database
FETCH NEXT FROM UserDatabases_CTE_Cursor INTO @dbname
END
CLOSE UserDatabases_CTE_Cursor
DEALLOCATE UserDatabases_CTE_Cursor
text-ิตp3W
May 8, 2017 at 6:54 am
disregard. i found it. I'll reply w/ what i end up with to close the loop
May 8, 2017 at 12:26 pm
backupmediafamily?
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
July 10, 2024 at 7:05 pm
removed dupe
July 10, 2024 at 7:05 pm
removed dupe
July 12, 2024 at 2:15 pm
Snargables wrote:disregard. i found it. I'll reply w/ what i end up with to close the loop
Did you ever find the solution? If so, would you be kind enough to post it?
what is the issue you are facing?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" ๐
July 12, 2024 at 5:29 pm
Jason A. Long wrote:Snargables wrote:disregard. i found it. I'll reply w/ what i end up with to close the loop
Did you ever find the solution? If so, would you be kind enough to post it?
what is the issue you are facing?
Although I don't have an issue that any of this would solve, the OP said they found the issue and will post the answer to the problem they described... they haven't done that. ๐
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply