shaileshmonani
asked on
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?
Ketan.
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.
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
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
ASKER
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\ASCF ABS_201008 13.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.
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\ASCF
Server: Msg 3013, Level 16, State 1, Line 22
BACKUP DATABASE is terminating abnormally.
Ketan.
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\ASCFA BS_2010081 3.BAK' folder
If it is working ok on your server, then ivestigate write access and user rights for the \\switin\sksqlbackup\ASCFA
ASKER
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 ?
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?
ASKER
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.
I dont see any way to grant rights to the user (SERVER\Administrator) on the machine SWITIN. Please help me out here.
Ketan.
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
Add the "SERVER\Administrator" in "Group or user names"
give the rights needed in the Permissions
ASKER
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.
SWITIN machine is part of the domain still SERVER\ADMINISTRATOR cannot be added.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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),GETDAT
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','
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.