Backup Device Locations Help

I am a newbie in SQL Server Administration. I decided to make the move within my company that no one else wanted.

I have SQL Server 2000 running at this time; users are connecting and using it. My question I guess pertains to the backups.

I have two drives setup for data and transactions logs (D: E:)

My backups are running but backups are being sent to the d:systems data\mssql\backup folder. once backups are comlete I normally copy the backups to a network share that is being backed up nightly to tape.

My question is this:

How do i create a device location under backup properties to point to a network share? i would like for the backups to go straight to a network drive so i do not have to copy the backups every morning when i come in.Its not good idea to perform backup over network share.
Instead schedule the job to copy those backup files to the network share using norma DOS Copy commands.

Ensure SQLAgent has required privileges to carry on this task of copying files on the network share.|||Originally posted by Satya
Its not good idea to perform backup over network share.
Instead schedule the job to copy those backup files to the network share using norma DOS Copy commands.

Ensure SQLAgent has required privileges to carry on this task of copying files on the network share.

--------------------------
Can you explain or point me to an article on using the DOS Copy command and how to use it with SQL Server? It's not something that I have used.

And what required permissions should the SQLAgent have in order to perform this type of task? the account that I created for the server itself has domain admin privileges. Would this pertain to the SQLAgent and help it perfrom the above tasks?|||Originally posted by Arra2
--------------------------
Can you explain or point me to an article on using the DOS Copy command and how to use it with SQL Server? It's not something that I have used.

And what required permissions should the SQLAgent have in order to perform this type of task? the account that I created for the server itself has domain admin privileges. Would this pertain to the SQLAgent and help it perfrom the above tasks?

easiest thing to do is type xcopy /? or copy /? at a command prompt, which will give you the syntax and definitions of the options

put that in a batch file and schedule it with 'at' or the Task Scheduler, you can also add that step to your MSSQL job if you want|||Thanks. That helps.|||Originally posted by Satya
Its not good idea to perform backup over network share.
Instead schedule the job to copy those backup files to the network share using norma DOS Copy commands.

Ensure SQLAgent has required privileges to carry on this task of copying files on the network share.

I've never heard a valid argument against network backup devices. Here's BOL excerpt:

Add a network disk backup device
This example shows a remote disk backup device. The name under which SQL Server was started must have permissions to that remote file.

USE master
EXEC sp_addumpdevice 'disk', 'networkdevice',
'\\servername\sharename\path\filename.ext'|||Though the facility is provided to perform backups over network, in a real-time environment you will have troubles such as process will block other connections while performing backups.

I agree and allow for small databases which are below 1GB in size, if not I approach other way to copy the local backup file.

As far as my experience is concerned its not a good approach on a enterprise wide network hosted SQL databases.:) :rolleyes:|||Satya,

How will a backup process possibly block other connections? I certainly respect your experience, but it makes me wonder...

Here we have a dedicated file server with 1.5T disk space, that we use to store backup files for a 1-day retention. All 120 (!!!) servers are being backed up onto this server. The disk is organized into subdirectories with names corresponding to source SQL Server names. Once all the backups are complete the tape backup kicks in. The only problems we have occasionally (a couple of times a year) is when there is a network glitch. But the jobs a written in such a way that the backup job continues processing remaining databases after creating an exception job that would contain failed backups. At the end the job would report an error and DBA on-call gets notified and resolves the issue by running the exception job . In the past 5 years that exception job run failed only once, when the network team failed to inform us that the LAN would be down during the certain time frame. Once Change Management was implemented 4 years ago we never had any issues with this approach since!|||rdjabarov

Thanks for your explanation and my intention is not to contradict your statements, I'd delivered what was experienced and you had already facted that situation and if the network and infrastructure is well built then you can test the process and deploy it.

backup across network

SQL Server newbie question ~ how can I backup a database to a directory in mounted network drive. when I attempt, the server tells me the drive/device is inaccessible. but from the command line, no problem accessing the directory. i have done research on the web and MSN site -- and the info states that it may have to do with permissions. if I can see the drive from command line -- can SQL Server "NOT" see it also? please let me know where I'm falling off the brain wagon...tiaMaybe I'm missing the point, but I've been told not to specify logical drive letters (like D:), rather to use server names, for instance \\server\ etc.|||Before a couple of days I have to solve the same problem by massive google search on the web :)
The problem is that the account on which the SQL server runs hasn't got privileges to see the network. By default, when installed SQL server is executing under system account 'LocalSystem' which hasn't got rights to view the network. Even if you try to backup the database from Enterprise Manager and select a file the dialog just shows local hard drives without network.
So to make backup accross network the user that SQL Server runs onto has to be changed to someone who has the rights for seeing network - fixed user 'Administrator' or any account that access the computer with administrator rights. In Enterprise Manager right-click on SQL server and select properties from the context menu. In the Properties window go to Security tab page, and select radio button 'This account' at the bottom of the window. Next to the radio button appear textboxes for user name and password where name and password of user who can see the network is entered.
Also when specifying the path in 'backup database' command use the notation '//server_name/shared_directory/file_name'. Directory where the backup will be made have to be shared on the network and the computer where SQL server runs have to be able to access it (sharing to 'Everyone' group will do this).|||'//server_name/shared_directory/file_name'

You might be better off using \\servername\directory\filename. Just an fyi.

01010111011010000111100100100000011000010111001001 10010100100000011101000110100001100101011100110110 01010010000001110000011001010110111101110000011011 00011001010010000001110011011011110010000001110011 011101000111010101110000011010010110010000111111

Backup

Hello,
Newbie question.
How do I have have a complete backup of a database for one weeks time frame
and the backups append to the file name the date it was backup up? Anything
after a week the oldest backup will be deleted. Thank you.There is nothing in SQL Server that does exactly that for you. You can
accomplish the same thing with a bit of TSQL programming. Just one thing:
you cannot delete parts of a backup file. When you do a backup, it is either
all or nothing (append or overwrite).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"vpont" <anonymous@.discussions.microsoft.com> wrote in message
news:B4A8313C-1472-400D-BCD2-4EA7C4DEC18B@.microsoft.com...
> Hello,
> Newbie question.
> How do I have have a complete backup of a database for one weeks time
frame and the backups append to the file name the date it was backup up?
Anything after a week the oldest backup will be deleted. Thank you.

Backing Up the Database (Newbie Question)

I have a Database on a MS-SQL Server wich was at the beginning 55 MB (size).
Every time I Backup the database the size increases (50 mb each time) !!!
Now it is over 700 MB of size and I can not store it in a CD !!!
Somebody told me that the size wich is increasing is that of the LOG FILE...
What is this exactly and how can I empty it ?

Any solutions appreciated....
Thanks in advance

massiveTDM850@.hotmail.com
gkarm@.softhome.netGeorge (gkarm@.softhome.net) writes:
> I have a Database on a MS-SQL Server wich was at the beginning 55 MB
> (size). Every time I Backup the database the size increases (50 mb each
> time) !!! Now it is over 700 MB of size and I can not store it in a CD
> !!! Somebody told me that the size wich is increasing is that of the LOG
> FILE... What is this exactly and how can I empty it ?

First you need to decide whether you want up-to-the-point recovery or not.
If you are content with restoring from the last backup in case of a crash,
and thus lose work since the last backup, set the recovery mode for the
database to simple. SQL Server will then truncate the transaction log every
now and then.

If you want up-to-the-point recovery, you should run with full recovery
(or bulk-logged), and make a habit to also backup the transaction log
regularly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp