marrowyung
asked on
The native MS SQL backup command
Dear all,
if we try to backup a MS SQL DB from console and we will find out that the SQL server will execute:
BACKUP DATABASE command,
do anyone knows what command exactly backup database will call?
if we try to backup a MS SQL DB from console and we will find out that the SQL server will execute:
BACKUP DATABASE command,
do anyone knows what command exactly backup database will call?
here you are :
BACKUP (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms186865(v=sql.100).aspx
and it depends on your sql server\version and what you selected in GUI and kind of backup (full; trn;diff)
and where too....
for example (from above link)
A. Backing up a complete database
The following example backs up the AdventureWorks database to a disk file.
BACKUP DATABASE AdventureWorks
TO DISK = 'Z:\SQLServerBackups\AdvWo
WITH FORMAT;
GO
BACKUP (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms186865(v=sql.100).aspx
and it depends on your sql server\version and what you selected in GUI and kind of backup (full; trn;diff)
and where too....
for example (from above link)
A. Backing up a complete database
The following example backs up the AdventureWorks database to a disk file.
BACKUP DATABASE AdventureWorks
TO DISK = 'Z:\SQLServerBackups\AdvWo
WITH FORMAT;
GO
ASKER
no, what I mean is, I am not sure if when running BACKUP DATABASE, there are some kind of .exe applicatoin that do the backup job for SQL server.
as we don't anyone unauthorized person to back that up any DB instead of using the corporate standard tools.
Anyway to make that stop that operatino?
as we don't anyone unauthorized person to back that up any DB instead of using the corporate standard tools.
Anyway to make that stop that operatino?
ask DBA to run sp_who/sp_who2 during this operation and identify host \program and disable it there (on the host where from it runs) or check sql agent jobs - it could be scheduled sql server job..
--
you can use KILL spid# command if you this backup running
All these needs DBAs level sql server permissions and rights on remote host (if such detected)
usual suspect is tape backup software -- check with your sys admins
it can be some unauthorized activities as well ..
--
you can use KILL spid# command if you this backup running
All these needs DBAs level sql server permissions and rights on remote host (if such detected)
usual suspect is tape backup software -- check with your sys admins
it can be some unauthorized activities as well ..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"The only .exe that does the backup when you issue the BACKUP DATABASE command is sqlservr.exe which is the SQL Server Service. No external EXE performs the backup."
this is good answer.
so only remove the right from the rest of users should be enought ?
this is good answer.
so only remove the right from the rest of users should be enought ?
ASKER
so from the SQL server admin point of view, they should belongs to "diskadmin" or "sysadmin" ?
Sysadmin has rights to do all the system administration for the database server. This is the most privileges.
Diskadmin allows someone to add files or remove files from a database.
Which privileges you grant people is up to you. You need some pretty high permissions to back up a database (dbo on the database, or db_creator)
If the users do not have privileges to back up a database, then they will not be allowed to execute the BACKUP DATABASE statement.
I hope I understood what you meant. If not, let me know.
Diskadmin allows someone to add files or remove files from a database.
Which privileges you grant people is up to you. You need some pretty high permissions to back up a database (dbo on the database, or db_creator)
If the users do not have privileges to back up a database, then they will not be allowed to execute the BACKUP DATABASE statement.
I hope I understood what you meant. If not, let me know.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BACKUP DATABASE.
Any tool that helps you is just generating commands to send to SQL Server. There are many options in the BACKUP DATABASE command, but they all back up a database.