Solved

The native MS SQL backup command

Posted on 2013-01-20
9
282 Views
Last Modified: 2013-02-03
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?
0
Comment
Question by:marrowyung
  • 3
  • 3
  • 3
9 Comments
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38799792
In SQL Server there is only one command to back up a database. It is

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.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38799848
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\AdvWorksData.bak'
   WITH FORMAT;
GO
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38800012
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?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38801096
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 ..
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 350 total points
ID: 38801574
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.

Furthermore, the backup is always performed under the permissions (on the file system level) of the SQL Server Service account.  So if you have SYSTEM user as the service login account, the service account needs to have access to the folder you put the backup file in.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38804028
"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 ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38804031
so from the SQL server admin point of view, they should belongs to "diskadmin" or "sysadmin" ?
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38804073
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.
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 150 total points
ID: 38807831
so only remove the right from the rest of users should be enought ?

unless - this process is using sa login (for example)

so start tracing activities and check again above posts
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now