Solved

cannot make SQL DB backup to disk

Posted on 2014-08-01
16
217 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 100 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 300 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
 

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 24

Assisted Solution

by:Mohammed Khawaja
Mohammed Khawaja earned 100 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 300 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 300 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 32

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

OfficeMate Freezes on login or does not load after login credentials are input.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
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…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

758 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

17 Experts available now in Live!

Get 1:1 Help Now