Restoring a database...

[DBAWinnipeg]

OK here's a fun little challenge

I have a production environment and take nightly full backups.

I have a reporting server that I would like to restore the production database backup to.

I shared the folder that the production backups are held (to everyone for now until I get it working)... and when I terminal service or PC Anywhere onto the reporting box I am able to map a drive to that location and can do whatever I want with the backup file

BUT

When I try and restore the file I get to the setting the device location

( \\CRMSQL01\data\BACKUPFILENAME.BAK)

and it is unable to verify the location.

So the troubleshooting I went through is

a) can I through START -> RUN go to the share - YES
b) can I rename the backup file (to ensure the file isn't locked by another user) - YES
c) can I restore the database onto the same server under a different name - YES

So I'm assuming it's something to do with SQL's ability to connect to the share... but I was under the assumtion that with the security being (SQL Server and Windows) that if I can map to the drive through START -> RUN and the path that I should be able to map to the share and get SQL to do a restore.

Any ideas??



Thanks in advance!!!

Colin in da 'Peg

 

[mrdenny]

Is the SQL Server running under the System account or a domain account? If a system account it won't be able to connect to another machine. Set it to log on as a domain account, restart the service, and then you should be able to access the remote file.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLBill]

To add on to Denny's post, when you are manually trying to 'see' the file you are doing so under your login/permissions.

But when you run RESTORE DATABASE, it is the SQLSERVERAGENT service that is running the script. That service needs to have DOMAIN ADMIN permissions and access to the file.

-SQLBill