Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

cannot make SQL DB backup to disk

Posted on 2014-08-01
16
Medium Priority
?
239 Views
Last Modified: 2014-08-01
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 ?
0
Comment
Question by:Eprs_Admin
16 Comments
 
LVL 12

Assisted Solution

by:Harish Varghese
Harish Varghese earned 400 total points
ID: 40233625
Have you provided Full Permission to everyone on the shared folder? What is the error?
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40233626
What do you mean "it isn't working in the gui"? Do you get error messages?
0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 1200 total points
ID: 40233627
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Eprs_Admin
ID: 40233636
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40233639
Screenshot of the error would help.
0
 

Author Comment

by:Eprs_Admin
ID: 40233643
Now used the script, ans the backup was successful to a shared folder \\server\sql\
0
 
LVL 25

Assisted Solution

by:Mohammed Khawaja
Mohammed Khawaja earned 400 total points
ID: 40233647
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
 

Author Comment

by:Eprs_Admin
ID: 40233648
ok let me check this...
0
 

Author Comment

by:Eprs_Admin
ID: 40233652
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
 

Author Comment

by:Eprs_Admin
ID: 40233654
My test DB is 92GB and the script just backed up 15GB.
Something is wrong here.
Can you show me what ?
0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 1200 total points
ID: 40233661
If you have SQL enterprise it will compress the backup. It does a full backup. I use it regularly.
0
 

Author Comment

by:Eprs_Admin
ID: 40233662
The SQL Agent user belongs to domain-admins group and should have enough rights in the domain.
0
 

Author Comment

by:Eprs_Admin
ID: 40233663
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
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 1200 total points
ID: 40233666
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
 
LVL 36

Expert Comment

by:ste5an
ID: 40233762
See Backing Up to a File on a Network Share.

Take the Important seriously.
0
 

Author Comment

by:Eprs_Admin
ID: 40233766
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

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

577 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