I'm using SQL Server 2019 in Windows 10 Enterprise and Administrator user I have an MDF file and I want to attach it, and it has full access in the form of Everyone Full Control , when I try to attach it I get this error : full message : =================================== Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) ------------------------------ For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476 ------------------------------ Program Location: at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.GetOriginalDatabaseName(String primaryFilePath) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.PopulatePrimaryFileData(String primaryFilePath, Boolean isXIPath) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile..ctor(SqlManagementUserControl parent, CDataContainer dc, String fullPath, String databaseOwner, ServerConnection connectionInfo, Boolean isXIPath) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.AddPrimaryFile(String fullPath, String fileName, Boolean isXIPath) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.buttonBrowse_Click(Object sender, EventArgs e) =================================== An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Program Location: at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command) at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command) at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query) at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result) at Microsoft.SqlServer.Management.Smo.PrimaryFile.GetData(EnumResult erParent) at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData() at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci) at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request) at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request) =================================== H:\GHAEM\neginsql.mdf is not a primary database file. (.Net SqlClient Data Provider) ------------------------------ For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5171-database-engine-error ------------------------------ Server Name: SERVERX Error Number: 5171 Severity: 16 State: 1 Line Number: 1 ------------------------------ Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command) also I tried with the SQL Server 2008R2 , same error !

I'm trying to attach a database with full text catalog and filestream, but I'm having some issues with the file stream part. Here's the SQL I'm using (according to this article , this should work):

use master
go


create database [Demov2] ON
(FILENAME = N'D:\Demo\Demov2.MDF'),
(FILENAME = N'D:\Demo\SQLFullTextCatalog\DemoV2_FullTextCatalog.ndf'),
(FILENAME = N'D:\Demo\LogsSQLSRA\Demov2_1.LDF'),
FILEGROUP [FileStreamGroup] contains filestream default
(Name = N'Demo_Files', FILENAME = N'D:\Demo\SQLFilestream')
for attach
go

This results in a error:

Msg 5120, Level 16, State 101, Line 5
Unable to open the physical file "D:\Demo\SQLFilestream". Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 7, Line 5
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I've tried running this as sa and as a windows user but the result is always the same. I've also checked the folder permissions and I've added my windows account and SQL Server's service account with full control to the D:\Demo folder and made sure that it was propagated to its children folder: Btw, I'm using an instance running this version od SQL

Microsoft SQL Server 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64)   
Jan 23 2023 17:02:42   
Copyright (C) 2022 Microsoft Corporation  
Developer Edition (64-bit) on Windows 10 Enterprise 10.0  (Build 22621: ) (Hypervisor)

It's clearly a permissions issue, but can anyone tell me what I'm missing? thanks.

I am trying to change the location of mdf and ldf file from one disk drive to other disk drive due to storage limit. Currently, the database that I am trying to move to other drive is in "Standby / Read-Only" mode. But, my understanding is that, in order move mdf file and attach database, I need to change the mode to "regular" before moving. Also, my understanding is that if I change the mode from "Standby / Read-Only" mode to something else, I cannot go back to restoring without restoring the backups again. How do I go about doing it (attaching the "Standby / Read-Only" database after detaching)?

(I reviewed the comments from this post , but I was not able to resolve my issue). I am trying to attach a mdf file that I placed in other drive, and I got this error: I am aware of the permission issue of the folder that I am pointing the file (to attach). I gave the full control on that folder for both myself and SQL Server account (NT Service\MSSQL$TEST1). I am not sure this extra screenshot would help: What can I do to fix this?

I had a database Coderr that I no longer wanted. Unfortunately, I detached it instead of dropping it. Now I'm seeing traces of it pop up in SSMS dialogs: I'd like to remove this (and any others that may be lurking). I tried DROP DATABASE [Coderr], but I got this error: > Cannot drop the database 'coderr', because it does not exist or you do not have permission I found this Q&A , but the T-SQL over there is a bit over my head and I'm nervous running it since I don't know whether it applies to my scenario. How can I permanently clean all references to this old, unwanted database from my server? Will it be possible to do so, or am I stuck with the consequences of my goof-up?

We are working on a data warehouse using IBM DB2 and we wanted to load data by partition exchange. That means we prepare a temporary table with the data we want to load into the target table and then use that entire table as a data partition in the target table. If there was previous data we just discard the old partition. Basically you just do "ALTER TABLE target_table ATTACH PARTITION pname [starting and ending clauses] FROM temp_table". It works wonderfully, but only for one operation at a time. If we do multiple loads in parallel or try to attach multiple partitions to the same table it's raining deadlock errors from the database. From what I understand, the problem isn't necessarily with parallel access to the target table itself (locking it changes nothing), but accesses to system catalog tables in the background. I have combed through the DB2 documentation but the only reference to the topic of concurrent DDL statements I found at all was to avoid doing them. The answer to this question, can't be to simply not attempt it? Does anyone know a way to deal with this problem? I tried to have a global, single synchronization table to lock if you want to attach any partitions, but it didn't help either. Either I'm missing something (implicit commits somewhere?) or some of the data catalog updates even happen asynchronously, which makes the whole problem much worse. If that is the case, is there are any chance at all to query if the attach is safe to perform at any given moment?

Sql server attach prerequisite states that a db must be detached before trying to attach it to another instance (https://learn.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver15#Prerequisites) However this migration link (https://learn.microsoft.com/en-us/sql/database-engine/install-windows/choose-a-database-engine-upgrade-method?view=sql-server-ver15#migrate-to-a-new-installation) recommends an attach without any mention about detach. Example: What is the implication at attempting an attach without detach?

I have got two files, test.mdf and test.ldf, with sizes around 200 Mb and 200 MB. I restore the database using "Attach Databases" options, all is ok, but I found that there are a lot of missing rows in some tables. What can I do? I have all files from previous windows installation, I read that mdf has got uncommited changes, where are those changes in the files?

Is attaching the database supported only by SQLite and MS SQL Server? All other DBMSes can only have one database per connection? Scenario: Let's say there is an archived database with some historical data. The application needs to compare with the current data for the same period. The database in use is not the two mentioned above. How do people solve that? EDIT: The plain SQL supports the syntax of catalog.schema.table which produces fully qualified table name to the query. However _I believe_ that in order to use that syntax I have to have a connection to the second database. Its very easy if 2 databases (2 database files) are running on the same server. Then, in case of SQL Server, for example you connect to the server, issue use db1 and then just use the appropriate syntax. However if those 2 databases are physically located on 2 different servers, I think _I need to create a connection to the second database_ in order to use fully qualified name. Am I right? I think I am because otherwise the DB engine will not know anything about the second DB file(s). Now, _my understanding is_ that there should be only one connection in order to use the syntax as SELECT catalog1.schema1.table1, catalog2.schema2.table2 FROM catalog1.schema1, catalog2.schema2 WHERE ....;. Am I right? I don't know for sure, as I'm not a DB administrator - just a software developer trying to work with different DBMSes. Now in the answer below I was told that the way to have this one connection to 2 different DBMSes - for PostgreSQL and mySQL. I'm sure the same mechanism exists for Oracle/SAP/Sybase/DB2. TIA for confirming or deniying my suspicions.

I have received a database file and the instructions for loading it is to install SQL Server 2005 and then attach it using SQL Server Management Studio. After installing everything, I tried to attach the MDF file but then it tells me: >the directory lookup for the file "D:\{folderName}\{filename}.LDF" failed with the operating system error 21 (error not found) An LDF file did not come with the database, so presumably it should be generated automatically. Now, D drive is where my CD drive is, so it's not going to find anything there. Nor is it going to have any luck trying to create anything there. 1. Why is it trying to look for a log file at a specific path? Why not where the database file is? 2. How can I attach this database? I realized that when I select a database to attach, three entries appear under "database details". an MDF, NDF, and LDF. The LDF's "current file path" points to the D drive path above, so I removed it. This time, when I hit "OK", I get a different error message: >Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery. >File activation Failure. The physical name D:\{folder}\{file}.LDF may be incorrect. New log file "..." was created. (Microsoft SQL Server, Error: 3415) So now it creates a new log file in the same folder as the database file, which is great, but it seems like there are security issues. Additional information: * The instructions require me to use the login name "sa", which appears to be the sysadmin account. I am connected to my SQL Server instance using that login. * I have checked the file properties that it is not read-only. The directory is not read-only either. All ACLs are allowed. * I am unable to attach the database. When I try to attach it, it throws an error message with "attach database failed". * Closing SSMS and reopening it as an Administrator made no difference. * select SERVERPROPERTY('ProductVersion') returns 9.00.4035.00. There's another file that comes with the database called "dbdata.ini" which says "IsSql2000=1" so presumably it is meant to be loaded in SQL Server 2000. I'll see if I can get it working on 2000.

I migrated a database in a test environment from one server running SQL Server Express 2008 R2 to another running the full version of SQL Server 2012. I did that by detaching the database from the first server, copying over the .mdf and .ldf files, and attaching those to the new server. That worked well, except I see some performance losses, hence I want to do some comparisons in terms of speed and maybe execution plans and here starts my problem: **I cannot reattach the original database I left on the old SQL Server Express instance. I did not do anything to it since it was detached and it worked like a charm until I did.** But now, when I want to attach it to the original SQL Server Express instance, I get this message: > CREATE DATABASE or ALTER DATABASE failed because the resulting > cumulative database size would exceed your licensed limit of 10240 MB > per database. (.Net SqlClient Data Provider) Sad thing is, this is party true, the database is 10,911,616 KB in size (1,024 KB for logs), which, honestly, stumps me, since I didn't do anything with it after detaching and it worked up until then. However I am very certain, there is lots of free space inside that database (something around 9GB) So this is a three-part-question: 1. **Can someone point me to the circumstances that could lead to this problem** (database working on the very same server, then detached, not changed (at least by me), then not being able to re-attach? 2. **Is there a way to free up space inside that .mdf file without actually attaching it?** 3. Or **could it be feasible to take the database I already attached to the new (full) SQL Server instance, shrink that in size and then attach it to SQL Server Express instance?** (or would attaching to the full SQL Server instance change the format of said .mdf file to be unusable by SQL Server Express?) Additional Server information: - New Database Server has version Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) - Old Database Server has version Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) - Database is running in compatibility level SQL Server 2008 (100)

In a SQL Server 2008 R2 instance two databases (the second one being a restore/copy of the first one) have been setup against the same .mdf and .ldf files. How would I go about either deleting the second one or copying/moving the files for the second one without affecting the first (production!) db? Update: Indeed one of them is Standby/Read-Only.

I have the following database command: CREATE DATABASE [test1] ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test1.mdf'), (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test1_log.ldf') FOR ATTACH When I do it via C# it returns me more than one value. Here is the C# code: public void RunQuery(string query, bool longRunning = false) { using (var cmd = new SqlCommand(query, Connection)) { cmd.CommandTimeout = longRunning ? _longRunningCommandTimeout : _commandTimeout; cmd.ExecuteNonQuery(); } } This results in the following error from SQL Server: > Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , >= or when the subquery is used as an expression.
The statement has been terminated And here is the stack trace from the exception: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() The database attach succeeds, even though an error is raised. When I execute the same query (under the same user) via Management Studio it gets me just the following: > Commands completed successfully. Is it possible that the CREATE DATABASE statement returns more than one result?

I am trying to move data from one SQL Server server to another (located on another computer). I know of the detach/attach method, but I don't want to disturb the prod environment (***detaching a prod database is out of the question***). Can't I just copy/paste the MDF and LDF files IF nothing is being actively written to/read from the prod? What if there are processes that are reading or writing to the database, what are my options to create an "attach" file without performing detach? Thank you very much. I read on two questions related to this but neither one applies here. One suggests using backup/restore, but that seems a bit difficult.

I have detached a database and had the systems team rename the drive letter (from E: to P:). When I try to attach the database again, it's looking for the original drive. I don't have a backup of the database. Is there any way I can change the system tables where the actual physical locations of the files (.mdf) are stored? Are there any alternative solutions now? Just to be clear: It's my mistake that I approved the systems team to change the drive letter.

SQL express 2016 How to attach database (picture)??

Does anyone have a TSQL script that can automatically generate the code necessary to attach ALL existing user databases using the CREATE DATABASE FOR ATTACH syntax? example: CREATE DATABASE [mydatabase] ON (FILENAME=N'E:\MSSQL\Data\mydatabase.mdf'), (FILENAME=N'D:\MSSQL.1\MSSQL\Data\mydatabase_log.ldf'), (FILENAME=N'E:\MSSQL\Data\mydatabase_ndf.ndf') FOR ATTACH I have found many examples using sp_attach_db, but none with the CREATE DATABASE FOR ATTACH syntax. Thanks, Craig

I always see this option called attach database in SQL Server. Why would someone attach a database? Why not just restore a database from the .BAK file? I guess I don't see the purpose of it. What would be the result of attaching it? It's quicker than a restore, but what is the technical result? Can you still alter the database? Add data to it? The preferred method or not recommend it? I have seen https://dba.stackexchange.com/q/11177 but nothing in there explains the purpose of attaching or detaching. It basically doesn't even touch on it.

I've a database in a production server (SQL Server 2012) and I've a development server (SQL Server 2014). I need to attach the database in the development server but I've realized that once attached it's automatically upgraded to the version 2014 so I can't attach it again in the production server. Is it possible to attach the database in the development server without upgrading it? We are planning to make changes in dev, detach, and attach it back to production.