When I try to I get the following error in SQL Server Management Studio:
TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'AHAKEEM'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Unable to open the physical file "C:\SQL Server 2000 Sample Databases\northwnd.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)". (Microsoft SQL Server, Error: 5120)
This is a fresh version of Northwinds mdf
which just came from Microsoft's installer.
Error 5120 is a sharing violation on the file you're opening. Try starting SQL Management Studio as Administrator and make sure that the db isn't attached already.
The error occurs when the mdf or ldf file is missing, if its an ldf we can recreate the same using the below listed scripts:
Method 1: To recreate all the log files
EXECUTE sp_attach_single_file_db @dbname = 'SAMPLEDB',
@physname = N'D:\MSSQL\DATA\SAMPLEDB.mdf'
GO
Method 2: If one or more log files are missing, they are recreated again.
CREATE DATABASE SAMPLEDB ON
(FILENAME = N'D:\MSSQL\DATA\SAMPLEDB.mdf')
FOR ATTACH_REBUILD_LOG
GO
Method 3: If only one file is missing, they are recreated again.
CREATE DATABASE SAMPLEDB ON
( FILENAME = N'D:\MSSQL\DATA\SAMPLEDB.mdf')
FOR ATTACH
GO
I tried to install Northwind and pubs Sample Databases for SQL Server 2000 and attach both databases in SQL Server 2014, and gave me an error because they were compatible version.
These are the steps to successful install the Sample Database in your SQL Server 2014:
Could not find stored procedure ‘sp_dboption’.
exec sp_dboption 'Northwind','trunc. log on chkpt.','true' exec sp_dboption 'Northwind','select into/bulkcopy','true'
alter database Northwind set recovery simple
To attach 'pubs' database, repeat the step 2 above and this time open instpubs.sql file. Replace the sp_dboption as you did before with:
alter database Pubs set recovery simple
Note: the folder location for your sample database by default is "C:\SQL Server 2000 Sample Databases"