The Right Cred

I have a very long running update to a SQL database that I need to tame.  The problem is that the database to which this process writes is a production system, and the data just has to be there.  I need to make optimization changes and not have to worry about shutting down production if my efforts fail.

While the real answer is only downloading those records that have changed, I need to have a the database update fixed so we can use the data and so I can verify the correctness of the incremental approach when I finish.

I say all of that to say this.  I backed up the database using the

BACKUP DATABASE databasename TO DISK ='d:\sql\backup\databasename.bak'

I stored the backup on the network share, to free up the room on the server.  I attempted to restore the backup, to create a copy of the database, found that I couldn't do it.

It seems that the SQL Server services is running at local-only credentials.  The only way to restore the file is to copy the file to the local drives, or change the user that the service uses to a domain user.  You see, I assumed that since I was using the front end, it would be using my network credentials.  Not so.

So, I copied the file, and attempted to restore the file.  It didn't work.

Seems like I have to specify a new MDF file name for the restored file with a MOVE clause.

But, to use the MOVE clause, one must know what to move.  That's where this statement comes in.

RESTORE FILELISTONLY FROM DISK ='location of backup file' 

This command lists the logical and physical names of a database inside the backup file.

I combined all this knowledge to produce

RESTORE DATABASE newdatabasename
FROM DISK ='path to bak file'
WITH MOVE 'logical name of database' to 'path to the mdf file',
MOVE 'logical name of log file' to 'path to the ldf file' 

.