Backup Possessive or Performance hog ???
-
When I try to create a directory on that drive I get an access denied error.
-
"name" sounds a little suspicious for a server name. Are you sure that's right?
Check whether SQL Server Agent is running under the system account or a domain account: In Enterprise Manager expand Management for the server, right click on SQL Server Agent. On the General tab, the "Service Start up Account" will tell you. If it is the system account, you need to get a domain account for SQL and Agent to run under. Make sure the account is a local administrator on the server. Once you have SQL Server Agent running under a domain account, request from the Network Administrator that YOU and the SQL account have WRITE access to your network drive. Once this is done, your job should be able to create a folder on the drive, and backup your database(s).
Steve
-
It sounds like your SQL Server and SQL Server Agent doesn't have write/create permissions on the distant directory. To prove it, log onto the SQL server as the SQL servers userid and attempt to write to the remote directory.
As a forward-looking view to security, instead of using a domain admin account for the userid, create a specialized userid that has local admin rights on the SQL Server and create a share on the remote directory that the specialized userid can see and avoid the C$. You can even put the $ on the share to hide it. Going that way the specialized userid can never really do damage to the network.
Note that when you change from the system or local user account to the domain account it does not go into effect until you stop the server and/or agent.
But my question is how are you creating the backup plan? Are you going into the DTS Wizard?
The quick and easy way to create a maint plan is to go in the EM and expand MS SQL Servers -> %servername% -> Management -> Database Maintenance Plans. The right-click in the right-hand pane and do New Maintenance Plan.
Note that your best bet in creating the plans is to do one that is for you System DB's. Note that system DB's don't like integrity checks before backups and generally only need a full backup not transaction log. Then create other maint plans based on the needs of the user databases on the server. One of our SQL servers has 2 databases that need point-in-time recovery. They are in full recovery mode. Three other DBs are in simple recovery mode and have a daily backup only.
In addition, we only keep 2-4 days backup files actually on the server. If we have to recover back any farther than that then we resort to the backup tapes.
I know this got long, but I'm throwing in my $0.02 from some good and bad experiences.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Hi.
We had some issues at the operating system level writing but those have been cleared up ... with the login for SQL Srver I can create folders and files on the drive I want to backup to. With this change I am not getting the error:
BACKUP failed to complete the command BACKUP DATABASE [DoD] TO DISK = N'I:\Directory\DB200406301640.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
I am a by default DBA ... by no means is this my primary skill ... just a skill I am dusting off. I am simply using backup code I pulled out of our big job and plugged int a special. Would you recommend I change my backup command line?
Use backup instead of what I am using now.
Thanks for all your replies!
-
There's nothing wrong with your backup command. I copied it to my machine, changed the path and db to match my system, and executed it without a problem.
If you're still having problems, it has to be with the SQL Agent account's access to the drive. You said earlier that your local machine doesn't have sufficient space to do a backup to a local drive. Does the network drive have sufficient space???
Steve
-
You can't backup the data/log files using Backup Express, ArcServe or other backup SW while the SQL Server is running. This is because the SQL Server keeps the files permanently open. There are some open file agents, but they cost extra money and I wouldn't trust them to backup my dog's doo-doo.
What is probably happening with your backup in this situation is the network latency. Try it again after major bus hrs and see what hapens.
Is there any way to isolate the local and desination server into it's own subnet/vlan to minimize traffic. Maybe look into local zip/usb hard drives or tape drives.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
The network drive is loaded Vroom Vroom.
I went into SQL Server Agent. On the General tab, the "Service Start up Account" was the one I am logging in with. I checked the account in Computer Management from windows and it is a member of administrators and usres groups. How do I tell if it is a domain account? From the the main SA told me I have write access to the drive (I could create a folder from explorer using my account that will run the job with the backup in it).
So much thanks for sticking with me on this.
-
Assuming your SQL server is not a DC......and if it is a/the DC this can be some very bad juju......Go to a domain controller -> Control Panel -> Active Directory Users and Computers. In there you should see the userid that you are using. Back at the SQL Server, look at the startup account for both services. It should be either "%DomainName%\userid" or "userid@%DomainName%". It it says "%Servername%\userid" or just "userid" then most likely you are using a local server account not a domain account.
This will of course need to change.
Suggestion to make life easier....use the same account for both the MSSQLServer service and the SQL Server Agent service. That way if someone does something to the one account it will blow things up equally, not indivdually.
Also suggest you look at this through the Comp Management -> Services. And as always the Server and agent will need to be stopped and restarted to be put into effect.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I'll try to backup again tonight but there is really no slow time. I'm not allowed to take the server from the users either to make the backup.
I don't know how to get into the domain controller ... Is it somewhere in START->PROGRAM->Admin Tools?
Thanks for hanging in with me ... If I don't get a back up ... it will cost me my job ... dba by default or not.
-
The domain controller is generally another server in the network.
Another thought....does the SQL Server have a second NIC? Maybe hang a second cable out to a spare switch/hub and a spare PC to act as a private network and drive space.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
A mapped drive is only available to the user who creates it. SQL Server runs under the system account and the mapping is not recognised by the system account(unless it is a share). UNC means the fully qualified path i.e.
\\servername\drive_letter$\directory
Can you try the below
sqlmaint -D Database -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\servername\I$\DIRECTORY" -CrBkSubDir -BkExt "BAK" -DelBkUps 3days
-
Since you aren't aware of where the Domain Controller is, I figure you aren't the network SysAdmin. Get in touch with the sysadmin, tell them the account that SQL Server Agent is running as and ask if that is a domain account with admin rights.
-SQLBill
-
To SA24:
Samll but important point
:By default, SQL Server does use the System account. But you can change it, and have to for several reasons, especially SQL Mail. SQL Server will also recognize mapped drives as long as the login to the server and the login to SQL Server and Agent all match. And it does not have to be a domain admin account. It does need to be a local admin and have access rights to whatever remote servers you are reading/writing to.
But I agree that UNC is a better method because it is too easy to login to the Win server with a different ID than SQL Server.
I suggested in another post that writing to a share with a userid that is not a domain admin will probably save grief down the line. When our auditors come we have to justify every user id that is in the Domain/Enterprise admin group.
I'm not trying to beat up on you
, just sounds like this guy was thrown in the deep end with a boat anchor, baling wire, bubble gum and duct tape. 
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Jim,
My apologies. I don't know if it was a cached session or something wrong with me that when i saw the thread, it was still in the initial steps where the author was still trying to figure out the syntax and i replied accordingly. Probably should have had a beer first.
-
I realize now that I dont' need to be a domain account but FYI: We do not have Active Directory. Myaccount is a NT Domain Admin account. None of the serves work as a Primary Domain Controller (PDC) or Backup Domain Controller (BDC).
I am 'not' Local admin, but I have been told that Domain Admin has more access so that should work ... what do you think? I have read/write access to the drive I believe with this accoutn because via explorer in windows I made a folder on the drive I am trying to backup to.
Can I run the backup outside a job?
Actually, this 'lady' was thrown into the middle of the Ocean with a boat anchor only.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply