Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-08-13
10
Medium Priority
?
346 Views
Last Modified: 2012-05-10
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?

Ketan.
0
Comment
Question by:shaileshmonani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 10

Expert Comment

by:Tom
ID: 33428404
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\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR  
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

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

       FETCH NEXT FROM db_cursor INTO @name  
END  

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.
0
 
LVL 1

Expert Comment

by:frfr177
ID: 33428453
Hi,

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

http://www.databasejournal.com/features/mssql/article.php/3530486/Working-with-the-SQL-Server-2000-Maintenance-Plan-Wizard.htm

frfr177
0
 

Author Comment

by:shaileshmonani
ID: 33428737
tru3533

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.

Ketan.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 10

Expert Comment

by:Tom
ID: 33437955
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
0
 

Author Comment

by:shaileshmonani
ID: 33443337
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 ?
0
 
LVL 10

Expert Comment

by:Tom
ID: 33445745
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?
0
 

Author Comment

by:shaileshmonani
ID: 33472593
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.

Ketan.
0
 
LVL 10

Expert Comment

by:Tom
ID: 33473389
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
0
 

Author Comment

by:shaileshmonani
ID: 33594881
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.

0
 
LVL 10

Accepted Solution

by:
Tom earned 500 total points
ID: 33614096
sounds like something basic is missing here.
have a look  at this instuction how to add a computer to a domain
http://www.visualwin.com/AD-XP/
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 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