VBS script + scheduled task to backup SQL Server database
I was looking around since too long, without finding anything, so badly I decided to ask here my problem, all by myself :-P
So here it is:
I am working with a certain software which uses a SQL Server database. I'm at a point where I'd like to scheduled a task to backup certains files AND a precise DB on the SQL Server.
First problem is that the user logged in, don't have any access granted on the DB to backup it. Each time a get the message
Msg 916, Level 14, State 1, Server XXXXXXXXXX, Line 1
The server principal "domainusername" is not able to access the database "XXXX" under the current security context.
Msg 3013, Level 16, State 1, Server XXXXXXXXXX, Line 1
BACKUP DATABASE is terminating abnormally.
From this command line:
C:sqlcmd -E -S XXXXXXXXXX-Q "BACKUP DATABASE XXXX TO DISK='C:TEMPtoto.bak'"
Second problem is that I even don't have any access to the DB...
So I cannot GRANT acess at all, to anybody...
Does somebody know a workaround I could use to achieve what I'm trying to do?
sql-server
edited Mar 1 '17 at 15:32
marc_s
7,09053849
asked Jul 29 '16 at 19:15
Louis-AlexLouis-Alex
61
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 3 more comments
I was looking around since too long, without finding anything, so badly I decided to ask here my problem, all by myself :-P
So here it is:
I am working with a certain software which uses a SQL Server database. I'm at a point where I'd like to scheduled a task to backup certains files AND a precise DB on the SQL Server.
First problem is that the user logged in, don't have any access granted on the DB to backup it. Each time a get the message
Msg 916, Level 14, State 1, Server XXXXXXXXXX, Line 1
The server principal "domainusername" is not able to access the database "XXXX" under the current security context.
Msg 3013, Level 16, State 1, Server XXXXXXXXXX, Line 1
BACKUP DATABASE is terminating abnormally.
From this command line:
C:sqlcmd -E -S XXXXXXXXXX-Q "BACKUP DATABASE XXXX TO DISK='C:TEMPtoto.bak'"
Second problem is that I even don't have any access to the DB...
So I cannot GRANT acess at all, to anybody...
Does somebody know a workaround I could use to achieve what I'm trying to do?
sql-server
edited Mar 1 '17 at 15:32
marc_s
7,09053849
asked Jul 29 '16 at 19:15
Louis-AlexLouis-Alex
61
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Your DBA should be configuring the backups for the development, test, and production DB needs, I'd talk with them about it. Otherwise, sign onto the SQL Server instance as SA or reset the SA password if you're able to and have access to the server to log into the server as the local or domain admin and look over these suggestions here: superuser.com/questions/1103850/…
– Pimp Juice IT
Jul 30 '16 at 2:48
Yeah, good suggestion !! I will give it a try !!
– Louis-Alex
Jul 30 '16 at 2:53
Was my suggestion of any use to you or have you given any a try yet?
– Pimp Juice IT
Jul 30 '16 at 23:04
@PIMP_JUICE_IT I will let you know for sure when i'll test on monday, at work...... :-)
– Louis-Alex
Jul 30 '16 at 23:34
As I thought, without any permission, I get that message : Msg 15151, Level 16, State 1, Server XXXXX/XXXXX, Line 1 Cannot alter the login 'sa', because it does not exist or you do not have permission. Another idea :-) ?
– Louis-Alex
Aug 1 '16 at 13:00
|
show 3 more comments
I was looking around since too long, without finding anything, so badly I decided to ask here my problem, all by myself :-P
So here it is:
I am working with a certain software which uses a SQL Server database. I'm at a point where I'd like to scheduled a task to backup certains files AND a precise DB on the SQL Server.
First problem is that the user logged in, don't have any access granted on the DB to backup it. Each time a get the message
Msg 916, Level 14, State 1, Server XXXXXXXXXX, Line 1
The server principal "domainusername" is not able to access the database "XXXX" under the current security context.
Msg 3013, Level 16, State 1, Server XXXXXXXXXX, Line 1
BACKUP DATABASE is terminating abnormally.
From this command line:
C:sqlcmd -E -S XXXXXXXXXX-Q "BACKUP DATABASE XXXX TO DISK='C:TEMPtoto.bak'"
Second problem is that I even don't have any access to the DB...
So I cannot GRANT acess at all, to anybody...
Does somebody know a workaround I could use to achieve what I'm trying to do?
sql-server
edited Mar 1 '17 at 15:32
marc_s
7,09053849
asked Jul 29 '16 at 19:15
Louis-AlexLouis-Alex
61
I was looking around since too long, without finding anything, so badly I decided to ask here my problem, all by myself :-P
So here it is:
I am working with a certain software which uses a SQL Server database. I'm at a point where I'd like to scheduled a task to backup certains files AND a precise DB on the SQL Server.
First problem is that the user logged in, don't have any access granted on the DB to backup it. Each time a get the message
Msg 916, Level 14, State 1, Server XXXXXXXXXX, Line 1
The server principal "domainusername" is not able to access the database "XXXX" under the current security context.
Msg 3013, Level 16, State 1, Server XXXXXXXXXX, Line 1
BACKUP DATABASE is terminating abnormally.
From this command line:
C:sqlcmd -E -S XXXXXXXXXX-Q "BACKUP DATABASE XXXX TO DISK='C:TEMPtoto.bak'"
Second problem is that I even don't have any access to the DB...
So I cannot GRANT acess at all, to anybody...
Does somebody know a workaround I could use to achieve what I'm trying to do?
sql-server
sql-server
edited Mar 1 '17 at 15:32
marc_s
7,09053849
asked Jul 29 '16 at 19:15
Louis-AlexLouis-Alex
61
edited Mar 1 '17 at 15:32
marc_s
7,09053849
asked Jul 29 '16 at 19:15
Louis-AlexLouis-Alex
61
edited Mar 1 '17 at 15:32
marc_s
7,09053849
edited Mar 1 '17 at 15:32
marc_s
7,09053849
edited Mar 1 '17 at 15:32
marc_s
7,09053849
7,09053849
asked Jul 29 '16 at 19:15
Louis-AlexLouis-Alex
61
asked Jul 29 '16 at 19:15
Louis-AlexLouis-Alex
61
asked Jul 29 '16 at 19:15
Louis-AlexLouis-Alex
61
61
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Your DBA should be configuring the backups for the development, test, and production DB needs, I'd talk with them about it. Otherwise, sign onto the SQL Server instance as SA or reset the SA password if you're able to and have access to the server to log into the server as the local or domain admin and look over these suggestions here: superuser.com/questions/1103850/…
– Pimp Juice IT
Jul 30 '16 at 2:48
Yeah, good suggestion !! I will give it a try !!
– Louis-Alex
Jul 30 '16 at 2:53
Was my suggestion of any use to you or have you given any a try yet?
– Pimp Juice IT
Jul 30 '16 at 23:04
@PIMP_JUICE_IT I will let you know for sure when i'll test on monday, at work...... :-)
– Louis-Alex
Jul 30 '16 at 23:34
As I thought, without any permission, I get that message : Msg 15151, Level 16, State 1, Server XXXXX/XXXXX, Line 1 Cannot alter the login 'sa', because it does not exist or you do not have permission. Another idea :-) ?
– Louis-Alex
Aug 1 '16 at 13:00
|
show 3 more comments
1
Your DBA should be configuring the backups for the development, test, and production DB needs, I'd talk with them about it. Otherwise, sign onto the SQL Server instance as SA or reset the SA password if you're able to and have access to the server to log into the server as the local or domain admin and look over these suggestions here: superuser.com/questions/1103850/…
– Pimp Juice IT
Jul 30 '16 at 2:48
Yeah, good suggestion !! I will give it a try !!
– Louis-Alex
Jul 30 '16 at 2:53
Was my suggestion of any use to you or have you given any a try yet?
– Pimp Juice IT
Jul 30 '16 at 23:04
@PIMP_JUICE_IT I will let you know for sure when i'll test on monday, at work...... :-)
– Louis-Alex
Jul 30 '16 at 23:34
As I thought, without any permission, I get that message : Msg 15151, Level 16, State 1, Server XXXXX/XXXXX, Line 1 Cannot alter the login 'sa', because it does not exist or you do not have permission. Another idea :-) ?
– Louis-Alex
Aug 1 '16 at 13:00
1
1
Your DBA should be configuring the backups for the development, test, and production DB needs, I'd talk with them about it. Otherwise, sign onto the SQL Server instance as SA or reset the SA password if you're able to and have access to the server to log into the server as the local or domain admin and look over these suggestions here: superuser.com/questions/1103850/…
– Pimp Juice IT
Jul 30 '16 at 2:48
Your DBA should be configuring the backups for the development, test, and production DB needs, I'd talk with them about it. Otherwise, sign onto the SQL Server instance as SA or reset the SA password if you're able to and have access to the server to log into the server as the local or domain admin and look over these suggestions here: superuser.com/questions/1103850/…
– Pimp Juice IT
Jul 30 '16 at 2:48
Yeah, good suggestion !! I will give it a try !!
– Louis-Alex
Jul 30 '16 at 2:53
Yeah, good suggestion !! I will give it a try !!
– Louis-Alex
Jul 30 '16 at 2:53
Was my suggestion of any use to you or have you given any a try yet?
– Pimp Juice IT
Jul 30 '16 at 23:04
Was my suggestion of any use to you or have you given any a try yet?
– Pimp Juice IT
Jul 30 '16 at 23:04
@PIMP_JUICE_IT I will let you know for sure when i'll test on monday, at work...... :-)
– Louis-Alex
Jul 30 '16 at 23:34
@PIMP_JUICE_IT I will let you know for sure when i'll test on monday, at work...... :-)
– Louis-Alex
Jul 30 '16 at 23:34
As I thought, without any permission, I get that message : Msg 15151, Level 16, State 1, Server XXXXX/XXXXX, Line 1 Cannot alter the login 'sa', because it does not exist or you do not have permission. Another idea :-) ?
– Louis-Alex
Aug 1 '16 at 13:00
As I thought, without any permission, I get that message : Msg 15151, Level 16, State 1, Server XXXXX/XXXXX, Line 1 Cannot alter the login 'sa', because it does not exist or you do not have permission. Another idea :-) ?
– Louis-Alex
Aug 1 '16 at 13:00
|
show 3 more comments
1 Answer
1
active
oldest
votes
From BOL:
BACKUP DATABASE and BACKUP LOG permissions default to members of the
sysadmin fixed server role and the db_owner and db_backupoperator
fixed database roles
The user account you are using needs to have, at a minimum, one of those permissions. db_backupoperator being the least intrusive as far as security. db_owner means you can do anything to the DB, including dropping it. Sysadmin gives you, well, control to everything on the SQL server (and more than likely the underlying server itself via xp_cmdshell, etc)
answered Jul 29 '16 at 20:27
Kris GruttemeyerKris Gruttemeyer
3,14311238
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f145380%2fvbs-script-scheduled-task-to-backup-sql-server-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
From BOL:
BACKUP DATABASE and BACKUP LOG permissions default to members of the
sysadmin fixed server role and the db_owner and db_backupoperator
fixed database roles
The user account you are using needs to have, at a minimum, one of those permissions. db_backupoperator being the least intrusive as far as security. db_owner means you can do anything to the DB, including dropping it. Sysadmin gives you, well, control to everything on the SQL server (and more than likely the underlying server itself via xp_cmdshell, etc)
answered Jul 29 '16 at 20:27
Kris GruttemeyerKris Gruttemeyer
3,14311238
add a comment |
From BOL:
BACKUP DATABASE and BACKUP LOG permissions default to members of the
sysadmin fixed server role and the db_owner and db_backupoperator
fixed database roles
The user account you are using needs to have, at a minimum, one of those permissions. db_backupoperator being the least intrusive as far as security. db_owner means you can do anything to the DB, including dropping it. Sysadmin gives you, well, control to everything on the SQL server (and more than likely the underlying server itself via xp_cmdshell, etc)
answered Jul 29 '16 at 20:27
Kris GruttemeyerKris Gruttemeyer
3,14311238
add a comment |
From BOL:
BACKUP DATABASE and BACKUP LOG permissions default to members of the
sysadmin fixed server role and the db_owner and db_backupoperator
fixed database roles
The user account you are using needs to have, at a minimum, one of those permissions. db_backupoperator being the least intrusive as far as security. db_owner means you can do anything to the DB, including dropping it. Sysadmin gives you, well, control to everything on the SQL server (and more than likely the underlying server itself via xp_cmdshell, etc)
answered Jul 29 '16 at 20:27
Kris GruttemeyerKris Gruttemeyer
3,14311238
From BOL:
BACKUP DATABASE and BACKUP LOG permissions default to members of the
sysadmin fixed server role and the db_owner and db_backupoperator
fixed database roles
The user account you are using needs to have, at a minimum, one of those permissions. db_backupoperator being the least intrusive as far as security. db_owner means you can do anything to the DB, including dropping it. Sysadmin gives you, well, control to everything on the SQL server (and more than likely the underlying server itself via xp_cmdshell, etc)
answered Jul 29 '16 at 20:27
Kris GruttemeyerKris Gruttemeyer
3,14311238
answered Jul 29 '16 at 20:27
Kris GruttemeyerKris Gruttemeyer
3,14311238
answered Jul 29 '16 at 20:27
Kris GruttemeyerKris Gruttemeyer
3,14311238
answered Jul 29 '16 at 20:27
Kris GruttemeyerKris Gruttemeyer
3,14311238
3,14311238
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f145380%2fvbs-script-scheduled-task-to-backup-sql-server-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Your DBA should be configuring the backups for the development, test, and production DB needs, I'd talk with them about it. Otherwise, sign onto the SQL Server instance as SA or reset the SA password if you're able to and have access to the server to log into the server as the local or domain admin and look over these suggestions here: superuser.com/questions/1103850/…
– Pimp Juice IT
Jul 30 '16 at 2:48
Yeah, good suggestion !! I will give it a try !!
– Louis-Alex
Jul 30 '16 at 2:53
Was my suggestion of any use to you or have you given any a try yet?
– Pimp Juice IT
Jul 30 '16 at 23:04
@PIMP_JUICE_IT I will let you know for sure when i'll test on monday, at work...... :-)
– Louis-Alex
Jul 30 '16 at 23:34
As I thought, without any permission, I get that message : Msg 15151, Level 16, State 1, Server XXXXX/XXXXX, Line 1 Cannot alter the login 'sa', because it does not exist or you do not have permission. Another idea :-) ?
– Louis-Alex
Aug 1 '16 at 13:00