July 30, 2018 at 7:44 am
Hi colleagues.
I made T-SQL script for automatic backup databases which is on AlwaysON availability group. Whole script works prorerly but "EXEC [master].sys.xp_cmdshell @CMD_COMMAND, NO_OUTPUT" (28th row) expression don't create folders. @CMD_COMMAND contains the required value. where I was wrong?
Thanks in advance!
DECLARE @DISKSTR varchar (255);
DECLARE @ERR_MESSAGE nvarchar(max);
DECLARE @SUBJECT_MESSAGE nvarchar(max);
DECLARE @DB_NAME nvarchar(max);
DECLARE @PATH_NAME varchar (255);
DECLARE @CMD_COMMAND nvarchar(max)
DECLARE @FILE_NAME nvarchar(max)
DECLARE db_name_cursor CURSOR
FOR SELECT [name] FROM sys.databases WHERE [replica_id] IS NOT NULL
OPEN db_name_cursor;
FETCH NEXT FROM db_name_cursor INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF sys.fn_hadr_backup_is_preferred_replica(@DB_NAME) <> 1
SELECT 'Это не предпочтительная реплика для резервного копирования - выход из программы';
ELSE
SET @PATH_NAME = N'T:\BackUP\Full'
-- DROP TABLE #PATH_DIRS;
CREATE TABLE #PATH_DIRS (dir varchar(250))
INSERT #PATH_DIRS EXEC master..xp_cmdshell 'dir T:\BackUP\Full /A:D /B'
IF (NOT EXISTS(SELECT dir FROM #PATH_DIRS WHERE dir = @DB_NAME))
BEGIN
SET @CMD_COMMAND = 'mkdir '
SET @CMD_COMMAND += @PATH_NAME
SET @CMD_COMMAND += '\'
SET @CMD_COMMAND += @DB_NAME
EXEC [master].sys.xp_cmdshell @CMD_COMMAND, NO_OUTPUT
BEGIN
SET @FILE_NAME = @PATH_NAME + '\' + @DB_NAME + '_' + replace(convert(char(19),getdate(),120),':','-') + '.bak'
BACKUP DATABASE @DB_NAME TO DISK = @FILE_NAME WITH COPY_ONLY, COMPRESSION;
IF @@ERROR <> 0
BEGIN
SET @ERR_MESSAGE = CONVERT(varchar(25), ERROR_SEVERITY());
SET @ERR_MESSAGE += CONVERT(varchar(25), ERROR_NUMBER());
SET @ERR_MESSAGE += ERROR_STATE();
SET @ERR_MESSAGE += ERROR_MESSAGE();
SET @SUBJECT_MESSAGE = N'Ошибка резервного копирования базы данных ';
SET @SUBJECT_MESSAGE += @DB_NAME;
SET @SUBJECT_MESSAGE += ' на сервере ';
SET @SUBJECT_MESSAGE += @@SERVERNAME;
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = @SUBJECT_MESSAGE,
@body = @ERR_MESSAGE
END
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = N'Резервное копирование базы данных @... успешно завершено',
@body = @ERR_MESSAGE
*/
END
END
ELSE
BEGIN
SET @FILE_NAME = @PATH_NAME + '\' + @DB_NAME + '_' + replace(convert(char(19),getdate(),120),':','-') + '.bak'
BACKUP DATABASE @DB_NAME TO DISK = @FILE_NAME WITH COPY_ONLY, COMPRESSION;
IF @@ERROR <> 0
BEGIN
SET @ERR_MESSAGE = CONVERT(varchar(25), ERROR_SEVERITY());
SET @ERR_MESSAGE += CONVERT(varchar(25), ERROR_NUMBER());
SET @ERR_MESSAGE += ERROR_STATE();
SET @ERR_MESSAGE += ERROR_MESSAGE();
SET @SUBJECT_MESSAGE = N'Ошибка резервного копирования базы данных ';
SET @SUBJECT_MESSAGE += @DB_NAME;
SET @SUBJECT_MESSAGE += ' на сервере ';
SET @SUBJECT_MESSAGE += @@SERVERNAME;
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = @SUBJECT_MESSAGE,
@body = @ERR_MESSAGE
END
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = N'Резервное копирование базы данных @... успешно завершено',
@body = @ERR_MESSAGE
*/
END
DROP TABLE #PATH_DIRS;
FETCH NEXT FROM db_name_cursor INTO @DB_NAME;
END
CLOSE db_name_cursor
DEALLOCATE db_name_cursor
GO
July 30, 2018 at 7:53 am
do you get any errors when running? You should try putting a try/catch around it instead of using @@Error. It could be permissions, but it could also be the mapped drive. Did you try unc (\\servername\share name\.....)?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 30, 2018 at 8:03 am
avpco - Monday, July 30, 2018 7:44 AMHi colleagues.
I made T-SQL script for automatic backup databases which is on AlwaysON availability group. Whole script works prorerly but "EXEC [master].sys.xp_cmdshell @CMD_COMMAND, NO_OUTPUT" (28th row) expression don't create folders. @CMD_COMMAND contains the required value. where I was wrong?
Thanks in advance!
DECLARE @DISKSTR varchar (255);
DECLARE @ERR_MESSAGE nvarchar(max);
DECLARE @SUBJECT_MESSAGE nvarchar(max);
DECLARE @DB_NAME nvarchar(max);
DECLARE @PATH_NAME varchar (255);
DECLARE @CMD_COMMAND nvarchar(max)
DECLARE @FILE_NAME nvarchar(max)DECLARE db_name_cursor CURSOR
FOR SELECT [name] FROM sys.databases WHERE [replica_id] IS NOT NULL
OPEN db_name_cursor;
FETCH NEXT FROM db_name_cursor INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF sys.fn_hadr_backup_is_preferred_replica(@DB_NAME) <> 1
SELECT 'Ðто не Ð¿Ñ€ÐµÐ´Ð¿Ð¾Ñ‡Ñ‚Ð¸Ñ‚ÐµÐ»ÑŒÐ½Ð°Ñ Ñ€ÐµÐ¿Ð»Ð¸ÐºÐ° Ð´Ð»Ñ Ñ€ÐµÐ·ÐµÑ€Ð²Ð½Ð¾Ð³Ð¾ ÐºÐ¾Ð¿Ð¸Ñ€Ð¾Ð²Ð°Ð½Ð¸Ñ - выход из программы';
ELSE
SET @PATH_NAME = N'T:\BackUP\Full'
-- DROP TABLE #PATH_DIRS;
CREATE TABLE #PATH_DIRS (dir varchar(250))
INSERT #PATH_DIRS EXEC master..xp_cmdshell 'dir T:\BackUP\Full /A:D /B'
IF (NOT EXISTS(SELECT dir FROM #PATH_DIRS WHERE dir = @DB_NAME))
BEGIN
SET @CMD_COMMAND = 'mkdir '
SET @CMD_COMMAND += @PATH_NAME
SET @CMD_COMMAND += '\'
SET @CMD_COMMAND += @DB_NAME
EXEC [master].sys.xp_cmdshell @CMD_COMMAND, NO_OUTPUT
BEGIN
SET @FILE_NAME = @PATH_NAME + '\' + @DB_NAME + '_' + replace(convert(char(19),getdate(),120),':','-') + '.bak'
BACKUP DATABASE @DB_NAME TO DISK = @FILE_NAME WITH COPY_ONLY, COMPRESSION;
IF @@ERROR <> 0
BEGIN
SET @ERR_MESSAGE = CONVERT(varchar(25), ERROR_SEVERITY());
SET @ERR_MESSAGE += CONVERT(varchar(25), ERROR_NUMBER());
SET @ERR_MESSAGE += ERROR_STATE();
SET @ERR_MESSAGE += ERROR_MESSAGE();
SET @SUBJECT_MESSAGE = N'Ошибка резервного ÐºÐ¾Ð¿Ð¸Ñ€Ð¾Ð²Ð°Ð½Ð¸Ñ Ð±Ð°Ð·Ñ‹ данных ';
SET @SUBJECT_MESSAGE += @DB_NAME;
SET @SUBJECT_MESSAGE += ' на Ñервере ';
SET @SUBJECT_MESSAGE += @@SERVERNAME;
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = @SUBJECT_MESSAGE,
@body = @ERR_MESSAGE
END
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = N'Резервное копирование базы данных @... уÑпешно завершено',
@body = @ERR_MESSAGE
*/
END
END
ELSE
BEGIN
SET @FILE_NAME = @PATH_NAME + '\' + @DB_NAME + '_' + replace(convert(char(19),getdate(),120),':','-') + '.bak'
BACKUP DATABASE @DB_NAME TO DISK = @FILE_NAME WITH COPY_ONLY, COMPRESSION;
IF @@ERROR <> 0
BEGIN
SET @ERR_MESSAGE = CONVERT(varchar(25), ERROR_SEVERITY());
SET @ERR_MESSAGE += CONVERT(varchar(25), ERROR_NUMBER());
SET @ERR_MESSAGE += ERROR_STATE();
SET @ERR_MESSAGE += ERROR_MESSAGE();
SET @SUBJECT_MESSAGE = N'Ошибка резервного ÐºÐ¾Ð¿Ð¸Ñ€Ð¾Ð²Ð°Ð½Ð¸Ñ Ð±Ð°Ð·Ñ‹ данных ';
SET @SUBJECT_MESSAGE += @DB_NAME;
SET @SUBJECT_MESSAGE += ' на Ñервере ';
SET @SUBJECT_MESSAGE += @@SERVERNAME;
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = @SUBJECT_MESSAGE,
@body = @ERR_MESSAGE
END
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = N'Резервное копирование базы данных @... уÑпешно завершено',
@body = @ERR_MESSAGE
*/
END
DROP TABLE #PATH_DIRS;
FETCH NEXT FROM db_name_cursor INTO @DB_NAME;
END
CLOSE db_name_cursor
DEALLOCATE db_name_cursor
GO
Possibly NTFS permissions on the folder involved? What execution context does the script operate under? Whatever that is, IT will need access to be able to make new folders in the T:\BackUP\Full folder.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 30, 2018 at 8:17 am
Mike01 - Monday, July 30, 2018 7:53 AMdo you get any errors when running? You should try putting a try/catch around it instead of using @@Error. It could be permissions, but it could also be the mapped drive. Did you try unc (\\servername\share name\.....)?
this code no return any errors.
Even I have executed "EXEC [master].sys.xp_cmdshell 'T:\Backup\Full\example_db', NO_OUTPUT" code and it made appropriate folder.
This code executed by sysadmin and server local administrator role.
July 30, 2018 at 8:18 am
sgmunson - Monday, July 30, 2018 8:03 AMavpco - Monday, July 30, 2018 7:44 AMHi colleagues.
I made T-SQL script for automatic backup databases which is on AlwaysON availability group. Whole script works prorerly but "EXEC [master].sys.xp_cmdshell @CMD_COMMAND, NO_OUTPUT" (28th row) expression don't create folders. @CMD_COMMAND contains the required value. where I was wrong?
Thanks in advance!
DECLARE @DISKSTR varchar (255);
DECLARE @ERR_MESSAGE nvarchar(max);
DECLARE @SUBJECT_MESSAGE nvarchar(max);
DECLARE @DB_NAME nvarchar(max);
DECLARE @PATH_NAME varchar (255);
DECLARE @CMD_COMMAND nvarchar(max)
DECLARE @FILE_NAME nvarchar(max)DECLARE db_name_cursor CURSOR
FOR SELECT [name] FROM sys.databases WHERE [replica_id] IS NOT NULL
OPEN db_name_cursor;
FETCH NEXT FROM db_name_cursor INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
IF sys.fn_hadr_backup_is_preferred_replica(@DB_NAME) <> 1
SELECT 'Ðто не Ð¿Ñ€ÐµÐ´Ð¿Ð¾Ñ‡Ñ‚Ð¸Ñ‚ÐµÐ»ÑŒÐ½Ð°Ñ Ñ€ÐµÐ¿Ð»Ð¸ÐºÐ° Ð´Ð»Ñ Ñ€ÐµÐ·ÐµÑ€Ð²Ð½Ð¾Ð³Ð¾ ÐºÐ¾Ð¿Ð¸Ñ€Ð¾Ð²Ð°Ð½Ð¸Ñ - выход из программы';
ELSE
SET @PATH_NAME = N'T:\BackUP\Full'
-- DROP TABLE #PATH_DIRS;
CREATE TABLE #PATH_DIRS (dir varchar(250))
INSERT #PATH_DIRS EXEC master..xp_cmdshell 'dir T:\BackUP\Full /A:D /B'
IF (NOT EXISTS(SELECT dir FROM #PATH_DIRS WHERE dir = @DB_NAME))
BEGIN
SET @CMD_COMMAND = 'mkdir '
SET @CMD_COMMAND += @PATH_NAME
SET @CMD_COMMAND += '\'
SET @CMD_COMMAND += @DB_NAME
EXEC [master].sys.xp_cmdshell @CMD_COMMAND, NO_OUTPUT
BEGIN
SET @FILE_NAME = @PATH_NAME + '\' + @DB_NAME + '_' + replace(convert(char(19),getdate(),120),':','-') + '.bak'
BACKUP DATABASE @DB_NAME TO DISK = @FILE_NAME WITH COPY_ONLY, COMPRESSION;
IF @@ERROR <> 0
BEGIN
SET @ERR_MESSAGE = CONVERT(varchar(25), ERROR_SEVERITY());
SET @ERR_MESSAGE += CONVERT(varchar(25), ERROR_NUMBER());
SET @ERR_MESSAGE += ERROR_STATE();
SET @ERR_MESSAGE += ERROR_MESSAGE();
SET @SUBJECT_MESSAGE = N'Ошибка резервного ÐºÐ¾Ð¿Ð¸Ñ€Ð¾Ð²Ð°Ð½Ð¸Ñ Ð±Ð°Ð·Ñ‹ данных ';
SET @SUBJECT_MESSAGE += @DB_NAME;
SET @SUBJECT_MESSAGE += ' на Ñервере ';
SET @SUBJECT_MESSAGE += @@SERVERNAME;
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = @SUBJECT_MESSAGE,
@body = @ERR_MESSAGE
END
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = N'Резервное копирование базы данных @... уÑпешно завершено',
@body = @ERR_MESSAGE
*/
END
END
ELSE
BEGIN
SET @FILE_NAME = @PATH_NAME + '\' + @DB_NAME + '_' + replace(convert(char(19),getdate(),120),':','-') + '.bak'
BACKUP DATABASE @DB_NAME TO DISK = @FILE_NAME WITH COPY_ONLY, COMPRESSION;
IF @@ERROR <> 0
BEGIN
SET @ERR_MESSAGE = CONVERT(varchar(25), ERROR_SEVERITY());
SET @ERR_MESSAGE += CONVERT(varchar(25), ERROR_NUMBER());
SET @ERR_MESSAGE += ERROR_STATE();
SET @ERR_MESSAGE += ERROR_MESSAGE();
SET @SUBJECT_MESSAGE = N'Ошибка резервного ÐºÐ¾Ð¿Ð¸Ñ€Ð¾Ð²Ð°Ð½Ð¸Ñ Ð±Ð°Ð·Ñ‹ данных ';
SET @SUBJECT_MESSAGE += @DB_NAME;
SET @SUBJECT_MESSAGE += ' на Ñервере ';
SET @SUBJECT_MESSAGE += @@SERVERNAME;
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = @SUBJECT_MESSAGE,
@body = @ERR_MESSAGE
END
EXEC msdb.dbo.sp_notify_operator @profile_name = N'SGC_DBA',
@name = N'SGC_DBA',
@subject = N'Резервное копирование базы данных @... уÑпешно завершено',
@body = @ERR_MESSAGE
*/
END
DROP TABLE #PATH_DIRS;
FETCH NEXT FROM db_name_cursor INTO @DB_NAME;
END
CLOSE db_name_cursor
DEALLOCATE db_name_cursor
GOPossibly NTFS permissions on the folder involved? What execution context does the script operate under? Whatever that is, IT will need access to be able to make new folders in the T:\BackUP\Full folder.
Even I have executed "EXEC [master].sys.xp_cmdshell 'T:\Backup\Full\example_db', NO_OUTPUT" code and it made appropriate folder.
This code executed by sysadmin and server local administrator role.
July 30, 2018 at 8:32 am
As suggested above, please try using a UNC path rather than T:\Backup\Full.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 30, 2018 at 8:33 am
avpco - Monday, July 30, 2018 8:17 AMMike01 - Monday, July 30, 2018 7:53 AMdo you get any errors when running? You should try putting a try/catch around it instead of using @@Error. It could be permissions, but it could also be the mapped drive. Did you try unc (\\servername\share name\.....)?this code no return any errors.
Even I have executed "EXEC [master].sys.xp_cmdshell 'T:\Backup\Full\example_db', NO_OUTPUT" code and it made appropriate folder.
This code executed by sysadmin and server local administrator role.
Okay, so it works when you run it from SQL Server Management Studio (aka SSMS). However, what process are you using to execute that code when it fails to create the folders? That process might be a SQL Agent Job. and that would require that the SQL Agent Service's "service account" have the necessary permission to create sub-folders in the T:\BackUP\Full sub-folder. That's what I was suggesting previously.could be the problem. You would NOT capture any errors in the scripted code you've provided.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 1, 2018 at 12:16 am
Thanks all.
I found error.
Variable @CMD_COMMAND must be varchar but not nvarchar.
Extended stored procedure xp_cmdshell waiting varchar input as main parameter.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply