Restoring a user database from a server folder in SQL 2005

[toon10]

Hi

I’m new to SQL so please bear with me!

I know how to create a backup device called ASSETMAN and then make a backup of a database manually. I can then restore using the following.

“USE master
RESTORE DATABASE LDVTest
FROM ASSETMAN
WITH RECOVERY”

This is OK for a one off backup.

I have created a maintenance plan which automatically does a full backup of my database LDVTest every 3 hours. This saves to another sever \\BackupServer\c$\DataBaseBackups\LDVTest\

Within this folder, the database is backed up and given the name “LDVTest_backup_” followed by the date time stamp then “.bak”. This works OK.

My problem is that I don’t know how to restore the last backup from \\BackupServer
I want something like the following but because the maintenance plan doesn’t use a named disk like “ASSETMAN” it doesn’t work.

“USE master
RESTORE DATABASE LDVTest
FROM \\BackupServer\c$\DatabaseBackups\LDVTest\LDVTest_backup_200612121430.bak
WITH RECOVERY”

Does anybody know how I would restore from my latest back on \\BackupServer?

Thanks

 

[Catadmin]

You need to look into the WITH MOVE and identify what drive locations you're restoring your data and log files to. Books Online should have this information under Restore Database.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[toon10]

OK thanks for the pointer. I'll take a look.

Andrew

 

[mrdenny]

You also need to specify that you are restoring from disk. Your command was close.

USE master
RESTORE DATABASE LDVTest
FROM [RED]DISK=[/red]\\BackupServer\c$\DatabaseBackups\LDVTest\LDVTest_backup_200612121430.bak
WITH RECOVERY[RED],
MOVE '{FileName}' to 'c:\filename.mdf'...[red]

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com