May 15, 2015 at 9:29 am
I am trying to restore a database called BCC_DB (I'm actually just needing one table opened to find some data in one column) so I created a blank database called BCCrestore. I have a .bak file called BCC_DB_backup_201505020017.bak.
I placed it in my local drive C:\BCC_DB_backup_201505020017.bak and tried the SQL:
RESTORE DATABASE BCCrestore FROM DISK = 'C:\BCC_DB_backup_201505020017.bak'
GO
I get an error:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\BCC_DB_backup_201505020017.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Any ideas? Thanks!
May 15, 2015 at 9:56 am
is the instance you are restoring to on your machine? If not the backup command will be looking on the c drive of the server the instance lives on, rather than the c drive on your machine.
May 15, 2015 at 10:57 am
No the SQL Server instance is not on my local machine. I'm not sure how to discover the physical location. Would it help to recode my restore query with the IP address of my local machine?
May 15, 2015 at 11:02 am
No.
You'll need to move the backup file to the server that the SQL instance is running on. Speak to your DBA or sysadmin if you don't have access to the server in question.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2015 at 12:33 pm
I found the actual drive needed so I try to run: RESTORE DATABASE BCCrestore FROM DISK = '\\belmont\c$\BCC_DB_backup_201505030017.bak'
GO
But I get an error:
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 15, 2015 at 12:53 pm
The database BCCrestore already exists. Use a different name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2015 at 12:58 pm
Yes or get rid of bccrestore before you run the restore script
May 15, 2015 at 1:02 pm
I tried changing it to 'BCCrestored' but it didn't create that DB and multiple errors popped up:
RESTORE DATABASE BCCrestored FROM DISK = '\\Belmont\c$\BCC_DB_backup_201505030017.bak'
GO
Msg 1834, Level 16, State 1, Line 1
The file 'D:\Databases\Data\BCC.mdf' cannot be overwritten. It is being used by database 'BCC_DB'.
Msg 3156, Level 16, State 4, Line 1
File 'BCC' cannot be restored to 'D:\Databases\Data\BCC.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'D:\Databases\Data\BCC_log.ldf' cannot be overwritten. It is being used by database 'BCC_DB'.
Msg 3156, Level 16, State 4, Line 1
File 'BCC_log' cannot be restored to 'D:\Databases\Data\BCC_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
May 15, 2015 at 1:17 pm
Yes even though you have changed the db name, sql will try and use the same db filenames (mdf and ldf) as the db you are restoring. you need to specify that you want to restore the database files as different names. Easiest way is to do this via the gui. right click databases and select restore files and filegroups. Insert the name of the db you want to restore as, select the backup file, and on the other tab specify what you want the mdf and ldf files to be called And the path you want to restore them to. Do not select overwrite, as this saves you from overwriting the original db if you have made a mistake.
May 15, 2015 at 1:36 pm
I used the GUI method and got the error "The backup set holds a backup of a database other than the existing BCCrestore database."
May 15, 2015 at 1:39 pm
You either need to get rid bccrestore which is just any empty db, or use a different db name for the restored database.
May 15, 2015 at 1:42 pm
Try this:
RESTORE DATABASE BCCrestore
FROM DISK = 'C:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BBC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BBC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf';
GO
If that doesn't work because BBCrestore exists, try this:
RESTORE DATABASE BCCrestore
FROM DISK = 'C:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BBC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BBC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf',
REPLACE,
STATS = 10;
GO
May 15, 2015 at 1:46 pm
Yes that's the syntax. Good page here explaining it
https://technet.microsoft.com/en-us/library/ms190447%28v=sql.105%29.aspx
May 15, 2015 at 2:05 pm
With the first block of code I get this:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\belmont\c$\BCC_DB_backup_201505030017'. Operating system error 2(The system cannot find the file specified.).
plus for the second block of code... I dont have needed files:
RESTORE DATABASE BCCrestore
FROM DISK = '\\belmont\c$\BCC_DB_backup_201505030017.bak'
WITH MOVE 'BBC' TO '\\belmont\c$\BCCrestore.mdf',
MOVE 'BBC_log' TO '\\belmont\c$\BCCrestore_log.ldf',
REPLACE,
STATS = 10;
GO
Msg 3234, Level 16, State 2, Line 1
Logical file 'BBC' is not part of database 'BCCrestore'. Use RESTORE FILELISTONLY to list the logical file names.
May 15, 2015 at 2:12 pm
You could do with knowing what the logical filenames are. They are likely to be bcc and bcc_log rather than bbc.you should be able to find out by viewing the properties of the source database
Viewing 15 posts - 1 through 15 (of 65 total)
You must be logged in to reply to this topic. Login to reply