December 14, 2011 at 7:19 pm
Hi All,
Sorry to do this but i need to give some background first as this may help with the solution. At our off-site locations we run SQL Express so we don't have the ability for backups via a maintenance plan etc. So i found on the net a VB script that I setup as scheduled tasks which then calls a .SQL script to complete the SQL backups Daily which was working perfectly.
I've run into an issue since installing SQL2008 R2, previously we ran SQL2005 SP2 and there weren't any issues. Now I have to have the User who is assigned the Backup task as a "SysAdmin" inside security which i don't want to do. I thought if i assign the user as a "BackupOperator" that may work but it hasn't and now I'm stuck having this user as a sysadmin or risk no backups.
Any ideas at all, if more information is needed let me know and i can also put the Code from VBS/SQL script if required.
Thanks
Tava
December 14, 2011 at 10:24 pm
Yes, Please post the backup script here so that we can analyze the requirement.
December 14, 2011 at 10:42 pm
Attached are both the VBS and SQL scripts. This was just directly from the Net. No need in there to store any users and/or passwords.
December 14, 2011 at 11:41 pm
Now I have to have the User who is assigned the Backup task as a "SysAdmin" inside security which i don't want to do. I thought if i assign the user as a "BackupOperator" that may work but it hasn't and now I'm stuck having this user as a sysadmin or risk no backups.
You don't need to provide SysAdmin rights to a user just to take the backup of a database. Adding the user in db_backupoperator role is sufficient for that task.
Schedule this stored procedure in a SQL Server Agent job & make that user the owner of the backup job.
December 15, 2011 at 12:12 am
By the way, the T-SQL script you provided was not a stored procedure. It was a batch only. So you may need to wrap this script under a stored procedure.
It can be done like this:
CREATE PROCEDURE USP_Backup_All_Databases
@BackupDirectory nvarchar(200)
AS
BEGIN
DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30)
--Add a list of all databases you don't want to backup to this.
DECLARE @BACKUP_COMMAND VARCHAR(MAX)
DECLARE Database_CURSOR CURSOR
FOR
SELECT name FROM sys.databases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'AdventureWorks'
--SET @BackupDirectory = '$(dir)'
IF (RIGHT(@BackupDirectory,1) <> '\')
BEGIN
SET @BackupDirectory = @BackupDirectory + '\'
END
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0
BEGIN
SET @Name = @DB + '( Daily BACKUP )'
SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
BEGIN
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
--SET some more pretty stuff for sql server.
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
END
ELSE
BEGIN
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
--SET some more pretty stuff for sql server.
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
END
SET @BACKUP_COMMAND = 'BACKUP DATABASE '+@DB+' TO DISK = '+@BackupFile+'
WITH NAME = '+@Name+', DESCRIPTION = '+@Description+' ,
MEDIANAME = '+@MediaName+', MEDIADESCRIPTION = '+@Description+' ,
STATS = 10'
--EXEC @BACKUP_COMMAND
PRINT @BACKUP_COMMAND
FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
END
Once the procedure has been created, you need to execute/schedule below command to take the backup:
Execute USP_Backup_All_Databases @BackupDirectory = 'Your Complete Backup Path Here'
December 15, 2011 at 12:49 am
What's the exact error that you're getting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2011 at 12:50 am
Divine Flame (12/15/2011)
By the way, the T-SQL script you provided was not a stored procedure. It was a batch only. So you may need to wrap this script under a stored procedure.
Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2011 at 1:02 am
GilaMonster (12/15/2011)
Divine Flame (12/15/2011)
By the way, the T-SQL script you provided was not a stored procedure. It was a batch only. So you may need to wrap this script under a stored procedure.Why?
So that it is easy to maintain afterwards, and he doesn't need to go to agent jobs every time to make any change to script. Just to organize the things.
December 15, 2011 at 1:46 am
Divine Flame (12/15/2011)
GilaMonster (12/15/2011)
Divine Flame (12/15/2011)
By the way, the T-SQL script you provided was not a stored procedure. It was a batch only. So you may need to wrap this script under a stored procedure.Why?
So that it is easy to maintain afterwards and he doesn't need to go to agent jobs every time to make any change to script
No SQL Agent or jobs here. Scheduled task calling a .SQL script file that was downloaded and used exactly as-is with no modifications. Move it to a procedure and there's now code in 3 places instead of 2.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2011 at 2:18 am
GilaMonster (12/15/2011)
Divine Flame (12/15/2011)
GilaMonster (12/15/2011)
Divine Flame (12/15/2011)
By the way, the T-SQL script you provided was not a stored procedure. It was a batch only. So you may need to wrap this script under a stored procedure.Why?
So that it is easy to maintain afterwards and he doesn't need to go to agent jobs every time to make any change to script
No SQL Agent or jobs here. Scheduled task calling a .SQL script file that was downloaded and used exactly as-is with no modifications. Move it to a procedure and there's now code in 3 places instead of 2.
Divine Flame (12/14/2011)
Tava (12/14/2011)
Now I have to have the User who is assigned the Backup task as a "SysAdmin" inside security which i don't want to do. I thought if i assign the user as a "BackupOperator" that may work but it hasn't and now I'm stuck having this user as a sysadmin or risk no backups.You don't need to provide SysAdmin rights to a user just to take the backup of a database. Adding the user in db_backupoperator role is sufficient for that task.
Schedule this stored procedure in a SQL Server Agent job & make that user the owner of the backup job.
Yup, he has a scheduled task which is calling a vbs file & that vbs file internally calls SQL script file. I suggested him to put this SQL script inside a SQL Agent Job rather than windows scheduled task & make the user (who is added in db_backupoperator role) owner of this backup job.
This is not necessarily the best approach. But I am sure your suggestions/changes will make it better:-).
December 15, 2011 at 3:15 am
Divine Flame (12/15/2011)
I suggested him to put this SQL script inside a SQL Agent Job rather than windows scheduled task.
On SQL Express edition?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2011 at 3:32 am
GilaMonster (12/15/2011)
Divine Flame (12/15/2011)
I suggested him to put this SQL script inside a SQL Agent Job rather than windows scheduled task.On SQL Express edition?
Ah!!! wasn't aware that Express Edition doesn't have SQL Server Agent.
Tava : Whatever I suggested, doesn't apply to your scenario:unsure:.
Thanks Gail for clearing me on this :-).
December 15, 2011 at 9:05 pm
Hi All,
Thanks for your help, I'll answer some questions that were asked and hopefully clarify a few things.
Divine Flame,
Its 2008 R2 Express Version so their aren't any Agent jobs in this version for your theory to work.
GilaMonster,
I don't get any errors, the script executes as a Scheduled task and then no backup gets created i have to make the user a SYS_ADMIN role inside SQL for it to work. If i used Backup_Operator it just does nothing. So I'm assuming its a security issue?
In SQL2005 it worked successfully without adding any user to specific roles (sysadmin or backup_operator)
The script itself vbs/sql doesn't need to be modified i posted that incase it has some impersonate security restrictions with 2008.
December 15, 2011 at 9:58 pm
Tava (12/15/2011)
Hi All,I don't get any errors, the script executes as a Scheduled task and then no backup gets created i have to make the user a SYS_ADMIN role inside SQL for it to work. If i used Backup_Operator it just does nothing. So I'm assuming its a security issue?
In SQL2005 it worked successfully without adding any user to specific roles (sysadmin or backup_operator)
I think it worked successfully in SQL Server 2005 because in SQL Server 2005, BUILTIN\ADMINISTRATOR was added in SysAdmin role by default when you install it. This has been changed in SQL Server 2008.
December 15, 2011 at 10:04 pm
Divine Flame (12/15/2011)
Tava (12/15/2011)
Hi All,I don't get any errors, the script executes as a Scheduled task and then no backup gets created i have to make the user a SYS_ADMIN role inside SQL for it to work. If i used Backup_Operator it just does nothing. So I'm assuming its a security issue?
In SQL2005 it worked successfully without adding any user to specific roles (sysadmin or backup_operator)
I think it worked successfully in SQL Server 2005 because in SQL Server 2005, BUILTIN\ADMINISTRATOR was added in SysAdmin role by default when you install it. This has been changed in SQL Server 2008.
You're right, I didn't even notice that SQL2008 removed the BUILTIN\ADMINISTRATOR security group while it existed in SQL2005.
So there seems like nothing much can be done besides granting the user SysAdmin rights, is that correct?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply