cannot make SQL DB backup to disk

Hi Experts,

I want to make a backup of my DB to DISK.
The SQL Server is 2008 on WIN 2008.
I have a share on another server to pu the backup there. The folder is shared vor everyone.
But the backup with the GUI is not working.
Can you help me please ?
Eprs_AdminSystem ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harish VargheseProject LeaderCommented:
Have you provided Full Permission to everyone on the shared folder? What is the error?
0
Lee SavidgeCommented:
What do you mean "it isn't working in the gui"? Do you get error messages?
0
Lee SavidgeCommented:
I use a script to backup a database.

declare @db nvarchar(100), 
        @datestring nvarchar(8), 
        @path nvarchar(100), 
        @file nvarchar(100), 
        @filesuffix nvarchar(100), 
        @name nvarchar(100), 
        @pathandfile nvarchar(100) 

select @db = 'InsertDatabaseNameHere', 
       @datestring = cast(year(getdate()) as nvarchar(4)) + right('0' + cast(month(getdate()) as nvarchar(2)), 2) + cast(right(day(getdate()), 2) as nvarchar(2)), 
       @path = 'D:\DBBackUp\', 
       @filesuffix = 'Live.bak', 
       @file = @datestring + @db + @filesuffix, 
       @name = @db + replace(@filesuffix, '.bak', ''), 
       @pathandfile = @path + @file 

BACKUP DATABASE @db TO  DISK = @pathandfile 
WITH NOFORMAT, INIT,  NAME = @name, SKIP, NOREWIND, NOUNLOAD,  STATS = 5 
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Eprs_AdminSystem ArchitectAuthor Commented:
The permission is set to everyone and full.
The security the same.

The error is in german.

Is it possible to make a backup to another server in a share with management Studio ?
0
Lee SavidgeCommented:
Screenshot of the error would help.
0
Eprs_AdminSystem ArchitectAuthor Commented:
Now used the script, ans the backup was successful to a shared folder \\server\sql\
0
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Check and see what service is the SQL Agent and server running as.  If it is running as Network Services or any built-in then it won't work as it cannot write to the destination due to permissions issue.  What you need to do is start with changing SQL Agent account to a domain account with permissions to the share and see if that works.  I don't believe we have to change SQL Server service account also but I could be wrong.
0
Eprs_AdminSystem ArchitectAuthor Commented:
ok let me check this...
0
Eprs_AdminSystem ArchitectAuthor Commented:
the backup via script, is it a full backup of the DB ?
I have to make a full backup, which I can restore easily with the Management Studio GUI.
0
Eprs_AdminSystem ArchitectAuthor Commented:
My test DB is 92GB and the script just backed up 15GB.
Something is wrong here.
Can you show me what ?
0
Lee SavidgeCommented:
If you have SQL enterprise it will compress the backup. It does a full backup. I use it regularly.
0
Eprs_AdminSystem ArchitectAuthor Commented:
The SQL Agent user belongs to domain-admins group and should have enough rights in the domain.
0
Eprs_AdminSystem ArchitectAuthor Commented:
Yes it is a ENterprise version in a cluster. :-)

Just for my understanding, can you explain the command in detail ?
BACKUP DATABASE @db TO  DISK = @pathandfile
WITH NOFORMAT, INIT,  NAME = @name, SKIP, NOREWIND, NOUNLOAD,  STATS = 5
0
Lee SavidgeCommented:
That part of the script was generated by SQL Backup. When I went through the backup using the GUI, there is a Script button at the top. I clicked that to get the code which I have modified to meet my needs. The bit I modified was to allow it to have a slightly better configurable file name and location for the output.

Syntax description here:

http://technet.microsoft.com/en-us/library/ms191304%28v=sql.105%29.aspx

That link is for SQL 2008 R2.
0
ste5anSenior DeveloperCommented:
See Backing Up to a File on a Network Share.

Take the Important seriously.
0
Eprs_AdminSystem ArchitectAuthor Commented:
ok now it works also from the GUI.
The problem wasn´t the share. It was the backup file name and then I pressed browse.
But the file was not created yet and there the error poped up, but when I just click ok, the backup starts.
Sorry for that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.