May 18, 2015 at 11:55 am
I ran xp_fixeddrives and saw:
drive | MB free
C | 45177
D | 314619
but how do I get to those?
\\belmont\C$ ? that doesn't work
\\belmont\D$ ? that doesn't work
\\belmont\ ? that doesn't work
May 18, 2015 at 11:58 am
briancampbellmcad (5/18/2015)
I ran xp_fixeddrives and saw:drive | MB free
C | 45177
D | 314619
but how do I get to those?
\\belmont\C$ ? that doesn't work
\\belmont\D$ ? that doesn't work
\\belmont\ ? that doesn't work
RESTORE DATABASE BCCrestore
FROM DISK = 'C:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BCC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BCC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf';
GO
Of course, this will only work if the directories in the RESTORE command exist.
May 18, 2015 at 1:23 pm
I verified that those objects existed in the paths I used below and still got an error:
RESTORE DATABASE BCCrestore
FROM DISK = 'Z:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BCC' TO 'Z:\Databases\Data\BCCrestore.mdf',
MOVE 'BCC_log' TO 'Z:\Databases\Data\BCCrestore_log.ldf';
GO
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'Z:\BCC_DB_backup_201505020017.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
May 18, 2015 at 1:34 pm
briancampbellmcad (5/18/2015)
I verified that those objects existed in the paths I used below and still got an error:RESTORE DATABASE BCCrestore
FROM DISK = 'Z:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BCC' TO 'Z:\Databases\Data\BCCrestore.mdf',
MOVE 'BCC_log' TO 'Z:\Databases\Data\BCCrestore_log.ldf';
GO
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'Z:\BCC_DB_backup_201505020017.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Where are you getting this Z: drive?
Going to be totally honest here, you should be working with your system administrator on figuring this out. We can't see what you or s/he see.
May 18, 2015 at 1:42 pm
OK, I'll make a guess...
When you mapped the location to copy the files there, you mapped it as your Z: drive. The SQL Server doesn't see it as the Z: drive, it sees that location as its D: drive. If you substitute D: for Z:, what happens?
May 18, 2015 at 1:42 pm
My system administrator, whom I've just spoken with this morning, does not understand the questions raised on these posts. He suggested mapping the \\Belmont\ server locally to my Z:\ drive.
May 18, 2015 at 1:44 pm
RESTORE DATABASE BCCrestore
FROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BCC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BCC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf';
GO
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'BCCrestore' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
May 18, 2015 at 1:45 pm
I think you're misunderstanding the way this works.
Imagine that when you run the backup command, it's not you, but that the SQL Server database engine service account does this:
- Logs into Windows on the server console (local, actual Windows install).
- Opens Explorer and must access the backup file on the local machine, or with a UNC path. This is from the server, not from your laptop/desktop with ssms. The account must have rights to read the file.
- must be able to find the valid paths from the WITH MOVE statement on the local machine.
- Must have Windows access to those paths and to create the .mdf/.ldf/.ndf files
- Executes the backup.
My guess is that you have access to the backup file from your laptop, but don't know how to "see" the backup file from the server's perspective. You've somewhat figured out the local paths for the restore and creation of the mdf/ldf files.
May 18, 2015 at 1:47 pm
Questions:
1) Are you running SSMS on your local, desktop system?
2) Are you connecting to a remote server named Belmont?
If the answers to these questions are yes, when you run the RESTOE database on Belmont it is running on Belmont, not your desktop (or laptop) system. The drives it is looking for a local to the server. The same ones you saw here:
briancampbellmcad (5/18/2015)
I ran xp_fixeddrives and saw:drive | MB free
C | 45177
D | 314619
but how do I get to those?
\\belmont\C$ ? that doesn't work
\\belmont\D$ ? that doesn't work
\\belmont\ ? that doesn't work
You need to use the C: and D: drives in the RESTORE command just like I provided.
May 18, 2015 at 1:53 pm
Can you add 'WITH REPLACE' and try it again?
Regardless of whether we can talk you through this restore, you really need to read through the Books Online documentation on backing up and restoring databases. There has been a lot of confusion here about how the backup process works and how the SQL Server acts in the process. Spending a little time going over this will save you a lot of future frustration if you're going to be doing this on a regular basis.
May 18, 2015 at 1:53 pm
Run the following and post the results:
restore headeronly from DISK = 'D:\BCC_DB_backup_201505020017.bak'
restore filelistonly from DISK = 'D:\BCC_DB_backup_201505020017.bak'
May 18, 2015 at 2:04 pm
This is not something I will be doing on a regular basis as I am just filling in for a DBA on sick-leave. I just need to get irate users seeing the correct data sometime tomorrow. Is the below what you mean wth 'replace'?:
RESTORE DATABASE BCCrestore
FROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH REPLACE 'BCC' TO 'D:\Databases\Data\BCCrestore.mdf',
REPLACE 'BCC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf';
GO
Msg 155, Level 15, State 3, Line 3
'REPLACE' is not a recognized RESTORE option.
May 18, 2015 at 2:06 pm
OK, let's try this:
RESTORE DATABASE BCCrestore
FROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BCC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BCC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf'
WITH REPLACE;
GO
May 18, 2015 at 2:07 pm
I want to restore to BCCrestore, not my production database. Are these safe to run?:
restore headeronly from DISK = 'D:\BCC_DB_backup_201505020017.bak'
restore filelistonly from DISK = 'D:\BCC_DB_backup_201505020017.bak'
May 18, 2015 at 2:08 pm
briancampbellmcad (5/18/2015)
I want to restore to BCCrestore, not my production database. Are these safe to run?:restore headeronly from DISK = 'D:\BCC_DB_backup_201505020017.bak'
restore filelistonly from DISK = 'D:\BCC_DB_backup_201505020017.bak'
Yes, these don't restore a database.
Viewing 15 posts - 31 through 45 (of 65 total)
You must be logged in to reply to this topic. Login to reply