SQL Server 2000 Backup on a XP machine in the network.

Hi Experts,

We have a Windows 2003 Enterprise Server SP 2 with Sql server 2000 SP3 installed on it. Requirement is to schedule automatic backups of databases on to a XP computer in the network. Which method is best to achieve this?

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.

TomMicrosoft ISV PartnerCommented:
run this type of script:
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'yourXP PC on server\Backup\'  


FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name  

CLOSE db_cursor  
DEALLOCATE db_cursor


Change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Check out the Maintanace Plan Stuff - it's the easiest way to create a backup:


shaileshmonaniAuthor Commented:

I get the bleow msg on execution of the script from QM

Server: Msg 3201, Level 16, State 1, Line 22
Cannot open backup device '\\switin\sksqlbackup\ASCFABS_20100813.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 22
BACKUP DATABASE is terminating abnormally.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TomMicrosoft ISV PartnerCommented:
Try first to save the backup locally, to elimate the script that is the problem.
If it is working ok on your server, then ivestigate write access and user rights for the \\switin\sksqlbackup\ASCFABS_20100813.BAK'  folder
shaileshmonaniAuthor Commented:
The script works locally,  We have created a specific user on machine switin which has full rights to the shared folder. how do i pass the authentication info through sql ?
TomMicrosoft ISV PartnerCommented:
it is the logged in user in sql who must have the rights on the xp PC. Check your AD on the server what for rights the user have. Do you use windows autentication?
shaileshmonaniAuthor Commented:
We use Mixed Mode Authentication on SQL Server.

I dont see any way to grant rights to the user (SERVER\Administrator) on the machine SWITIN. Please help me out here.

TomMicrosoft ISV PartnerCommented:
Right click the backup folder on the SWITIN PC - properties - click the security tab
Add the "SERVER\Administrator" in  "Group or user names"
give the rights needed in the Permissions
shaileshmonaniAuthor Commented:
Tried to add the Server\Administrator but this is not successful - error - Location Server not found.
SWITIN machine is part of the domain still SERVER\ADMINISTRATOR cannot be added.

TomMicrosoft ISV PartnerCommented:
sounds like something basic is missing here.
have a look  at this instuction how to add a computer to a domain

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

From novice to tech pro — start learning today.