can this be done ?
The database in question is used by the accounting department and the
application uses the SA login to access the database.
Any insight would be greatful.
TIAThis is a multi-part message in MIME format.
--=_NextPart_000_0013_01C361A3.29261130
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Ok... after looking at the SQL server, it appears that the accounting =app is using a Login ID, that in turn is the dbo for the accounting =database, so I have given that ID the Backup DB permission on the =server. Now what can I do to place a short cut on the desktops so that =the database is backed up when click (thus on demand). Is there a =script file that I need to write or a command line to be entered ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:us9V7HcYDHA.656@.tk2msftngp13.phx.gbl...
An app with the sa password is like a 3-yr old with a .357 magnum. =Your accounting app should not have sysadmin rights. You can grant =BACKUP DATABASE to any login and that's all you would need for that.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"_M_" <here@.gone.com> wrote in message =news:#KbMuEcYDHA.2464@.TK2MSFTNGP09.phx.gbl...
Have a client that needs to do an "on demand" back up of a database. =How
can this be done ?
The database in question is used by the accounting department and the
application uses the SA login to access the database.
Any insight would be greatful.
TIA
--=_NextPart_000_0013_01C361A3.29261130
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ok... after looking at the SQL server, =it appears that the accounting app is using a Login ID, that in turn is =the dbo for the accounting database, so I have given that ID the Backup DB =permission on the server. Now what can I do to place a short cut on the =desktops so that the database is backed up when click (thus on demand). Is =there a script file that I need to write or a command line to be entered ?
"Tom Moreau"
An app with the sa password is like =a 3-yr old with a .357 magnum. Your accounting app should not have sysadmin = rights. You can grant BACKUP DATABASE to any login and that's =all you would need for that.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"_M_"
--=_NextPart_000_0013_01C361A3.29261130--|||This is a multi-part message in MIME format.
--=_NextPart_000_0338_01C361A5.7026B970
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You can create a little .CMD file that has the following:
osql -SMyServer -E -Q"backup database MyDB to disk =3D 'C:\MyDB.bak'"
or
osql -SMyServer -UMyLogin -PMyPassword -Q"backup database MyDB to disk ==3D 'C:\MyDB.bak'"
Ideally, use Windows authentication as shown in the first example, so =that no one can see the password. Alternatively, you can write the =second one as:
osql -SMyServer -UMyLogin -Q"backup database MyDB to disk =3D ='C:\MyDB.bak'"
It will then prompt the user for the password.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"_M_" <here@.gone.com> wrote in message =news:O7KcoScYDHA.888@.TK2MSFTNGP10.phx.gbl...
Ok... after looking at the SQL server, it appears that the accounting =app is using a Login ID, that in turn is the dbo for the accounting =database, so I have given that ID the Backup DB permission on the =server. Now what can I do to place a short cut on the desktops so that =the database is backed up when click (thus on demand). Is there a =script file that I need to write or a command line to be entered ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:us9V7HcYDHA.656@.tk2msftngp13.phx.gbl...
An app with the sa password is like a 3-yr old with a .357 magnum. =Your accounting app should not have sysadmin rights. You can grant =BACKUP DATABASE to any login and that's all you would need for that.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"_M_" <here@.gone.com> wrote in message =news:#KbMuEcYDHA.2464@.TK2MSFTNGP09.phx.gbl...
Have a client that needs to do an "on demand" back up of a database. =How
can this be done ?
The database in question is used by the accounting department and the
application uses the SA login to access the database.
Any insight would be greatful.
TIA
--=_NextPart_000_0338_01C361A5.7026B970
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You can create a little .CMD file that =has the following:
osql -SMyServer -E -Q"backup =database MyDB to disk =3D 'C:\MyDB.bak'"
or
osql -SMyServer -UMyLogin -PMyPassword -Q"backup database MyDB to disk =3D ='C:\MyDB.bak'"
Ideally, use Windows authentication as shown in the first example, =so that no one can see the password. Alternatively, you can write the =second one as:
osql -SMyServer =-UMyLogin -Q"backup database MyDB to disk =3D 'C:\MyDB.bak'"
It will then prompt the user for the =password.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"_M_"
Ok... after looking at the SQL server, =it appears that the accounting app is using a Login ID, that in turn is =the dbo for the accounting database, so I have given that ID the Backup DB =permission on the server. Now what can I do to place a short cut on the =desktops so that the database is backed up when click (thus on demand). Is =there a script file that I need to write or a command line to be entered ?
"Tom Moreau"
An app with the sa password is like =a 3-yr old with a .357 magnum. Your accounting app should not have sysadmin = rights. You can grant BACKUP DATABASE to any login and that's =all you would need for that.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"_M_"
--=_NextPart_000_0338_01C361A5.7026B970--|||Dbo does not have to have backup database permission. A dbo can do anything
to the database that he/she owns..
Here is something short I did
1. create this proc ( you need to adjust the db name)
create proc dbo.bu as
declare @.fname varchar(128)
select @.fname = 'c:\' +
db_name() + '_DB_' + convert(varchar(100), getdate(),112)
+ datename(hh,getdate()) + datename(mi,getdate())
--select @.fname
backup database northwind to disk = @.fname
create a dos batch job like this
OSQL -Ulogin -Ppassword -Q"exec dbname.dbo.bu"
when the job is run the backup occurs...
Then put a shortcut to the job on their desktop
I would create a new login for the database which has ONLY backup
permissions, since the login and password are easily found...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and its
community of SQL Server professionals.
www.sqlpass.org
"_M_" <here@.gone.com> wrote in message
news:#KbMuEcYDHA.2464@.TK2MSFTNGP09.phx.gbl...
> Have a client that needs to do an "on demand" back up of a database. How
> can this be done ?
> The database in question is used by the accounting department and the
> application uses the SA login to access the database.
> Any insight would be greatful.
> TIA
>|||I like both yours and Tom's responses... Can yours be modified to use
Windows Authentication or can Tom's be modified to save each backup in the
time-date file name like yours ?
I'm sort of new to this SQL server stuff but I have had a semester of DB in
college...
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OVu5IfcYDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Dbo does not have to have backup database permission. A dbo can do
anything
> to the database that he/she owns..
> Here is something short I did
> 1. create this proc ( you need to adjust the db name)
> create proc dbo.bu as
> declare @.fname varchar(128)
> select @.fname = 'c:\' +
> db_name() + '_DB_' + convert(varchar(100), getdate(),112)
> + datename(hh,getdate()) + datename(mi,getdate())
> --select @.fname
> backup database northwind to disk = @.fname
> create a dos batch job like this
> OSQL -Ulogin -Ppassword -Q"exec dbname.dbo.bu"
> when the job is run the backup occurs...
> Then put a shortcut to the job on their desktop
> I would create a new login for the database which has ONLY backup
> permissions, since the login and password are easily found...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and its
> community of SQL Server professionals.
> www.sqlpass.org
>
> "_M_" <here@.gone.com> wrote in message
> news:#KbMuEcYDHA.2464@.TK2MSFTNGP09.phx.gbl...
> > Have a client that needs to do an "on demand" back up of a database.
How
> > can this be done ?
> >
> > The database in question is used by the accounting department and the
> > application uses the SA login to access the database.
> >
> > Any insight would be greatful.
> >
> > TIA
> >
> >
>|||This is a multi-part message in MIME format.
--=_NextPart_000_03C3_01C361AA.859BF130
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Tom's can be modified. ;-) Just have it execute the proc instead of =the raw BACKUP:
osql -SMyServer -E -Q"exec MyBackupProc"
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"_M_" <here@.gone.com> wrote in message =news:uofpKqcYDHA.2448@.TK2MSFTNGP09.phx.gbl...
I like both yours and Tom's responses... Can yours be modified to use
Windows Authentication or can Tom's be modified to save each backup in =the
time-date file name like yours ?
I'm sort of new to this SQL server stuff but I have had a semester of DB =in
college...
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OVu5IfcYDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Dbo does not have to have backup database permission. A dbo can do
anything
> to the database that he/she owns..
> Here is something short I did
> 1. create this proc ( you need to adjust the db name)
> create proc dbo.bu as
> declare @.fname varchar(128)
> select @.fname =3D 'c:\' +
> db_name() + '_DB_' + convert(varchar(100), getdate(),112)
> + datename(hh,getdate()) + datename(mi,getdate())
> --select @.fname
> backup database northwind to disk =3D @.fname
> create a dos batch job like this
> OSQL -Ulogin -Ppassword -Q"exec dbname.dbo.bu"
> when the job is run the backup occurs...
> Then put a shortcut to the job on their desktop
> I would create a new login for the database which has ONLY backup
> permissions, since the login and password are easily found...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and its
> community of SQL Server professionals.
> www.sqlpass.org
>
> "_M_" <here@.gone.com> wrote in message
> news:#KbMuEcYDHA.2464@.TK2MSFTNGP09.phx.gbl...
> > Have a client that needs to do an "on demand" back up of a database.
How
> > can this be done ?
> >
> > The database in question is used by the accounting department and =the
> > application uses the SA login to access the database.
> >
> > Any insight would be greatful.
> >
> > TIA
> >
> >
>
--=_NextPart_000_03C3_01C361AA.859BF130
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Tom's can be modified. ;-) =Just have it execute the proc instead of the raw BACKUP:
osql -SMyServer -E -Q"exec MyBackupProc"
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"_M_"
--=_NextPart_000_03C3_01C361AA.859BF130--|||Tried to create the procedure as listed by Wayne... I get an error message
(Invalid syntax at or around backup"
I did do a cut and paste as well as completely re-typing it myself. The
server is SQL 2000 if that matters...
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OVu5IfcYDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Dbo does not have to have backup database permission. A dbo can do
anything
> to the database that he/she owns..
> Here is something short I did
> 1. create this proc ( you need to adjust the db name)
> create proc dbo.bu as
> declare @.fname varchar(128)
> select @.fname = 'c:\' +
> db_name() + '_DB_' + convert(varchar(100), getdate(),112)
> + datename(hh,getdate()) + datename(mi,getdate())
> --select @.fname
> backup database northwind to disk = @.fname
> create a dos batch job like this
> OSQL -Ulogin -Ppassword -Q"exec dbname.dbo.bu"
> when the job is run the backup occurs...
> Then put a shortcut to the job on their desktop
> I would create a new login for the database which has ONLY backup
> permissions, since the login and password are easily found...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and its
> community of SQL Server professionals.
> www.sqlpass.org
>
> "_M_" <here@.gone.com> wrote in message
> news:#KbMuEcYDHA.2464@.TK2MSFTNGP09.phx.gbl...
> > Have a client that needs to do an "on demand" back up of a database.
How
> > can this be done ?
> >
> > The database in question is used by the accounting department and the
> > application uses the SA login to access the database.
> >
> > Any insight would be greatful.
> >
> > TIA
> >
> >
>sql
No comments:
Post a Comment