Hello. I am a brand-new user of SQL Server 2014, and I am having trouble restoring a .bak file to a new database. Let me begin by stating that I am not using this for my current job. I simply purchased the software and begun an SQL online course to familiarize myself with the software in the hope of bolstering my resume. The course I am currently taking provided a .bak file to restore that contained a sample database so the students can follow along with the lessons. However, I attempted to restore the file by right-clicking the newly created database, selecting Tasks, and selecting Restore Database. I receive the following message: Restore of database 'sample' failed. An error message pop-up appears that states: TITLE: Microsoft SQL Server Management Studio
Restore of database 'sample' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.SmoExtended)
I have absolutely no idea on how to resolve this as I have only been using SQL Server for the last two days. I asked for help on the course's forum, but have not received a reply. I figured I would seek assistance on this forum. Any help you can provide would be greatly appreciated. Thank you.
I would forget about the GUI and open a query window in SSMS.
Get the Logical file names and the PhysicalFileName, minus the path, from the BAK file
RESTORE FILELISTONLY
FROM DISK = 'YourPath\YourDB.bak';
and make a note of them. Let's say there are 2 file's:
YourDB - YourDB.mdf
YourDB_Log - YourDB_log.ldf
Now restore from the BAK file moving the physical files to where you want them on your machine.
I would suggest you high light the code between the GO' and press F5 to run each bit separately.
(You will need to replace YourDB, YourPath etc with the correct values)
If there are any problems you will then know where they occurred.
GO
USE master
GO
RESTORE DATABASE YourDB
FROM DISK = N'YourPath\YourDB.bak'
-- RECOVER the DB and REPLACE it if it is already there
WITH RECOVERY, REPLACE --, CHECKSUM
--MOVE the logical files where you want them. There may be more files
,MOVE 'YourDB' TO 'WhereYourWantTheFile\YourDB.mdf'
,MOVE 'YourDB_log' TO 'WhereYourWantTheFile\YourDB_log.ldf';
GO
-- Make sure the DB is multi user
ALTER DATABASE YourDB SET MULTI_USER;
-- Start by setting the owner of the DB to sa, you may want to change this later
ALTER AUTHORIZATION ON DATABASE::YourDB TO sa;
-- As this is a test DB, there is no point in letting the log grow
-- (You may want to change this later)
ALTER DATABASE YourDB SET RECOVERY SIMPLE;
GO
USE YourDB;
GO
-- Check the DB is okay
DBCC checkdb;
GO
-- When moving a DB to a different machine, always update the stats.
EXEC sp_updatestats;
GO
If your database is in recovery pending state and unable to run command on database in online state in such condition your SQL Database (.mdf) file may be corrupted and through the different error like
" Unable to open the physical file "C:\Data\MSSQL\Database.mdf". Operating system error 3: "3(The system cannot find the path specified.)"
"File activation failure. The physical file name "C:\Data\MSSQL\Data\Database_log.ldf" may be incorrect."
"Database 'Database' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details."
I think you may have the tick box selected to perform a tail log backup, when you start your restore.
In your restore GUI you should select the Options page and make sure, in this case, the Tail-Log backup option is not selected. As this is a brand new blank database you are restoring over there would be nothing to backup anyway.
Alternatively, if you perform a full backup on your source database then you will be able to perform the tail log backup when you set your restore going.