hello, (this was originally posted in Administration Forum)
I am helping out with a disaster recovery of a database and i was given 4 files. 2 MDF and 2 LDF files. When I try to attach the files to SQL Server I recieve this err msg:
Server: Msg 823, Level 24, State 6, Line 1
I/O error (torn page) detected during read at offset 0000000000000000 in file 'E:chivirtualGPS0Dat.mdf'.
Connection Broken
I then tried many other combinations hoping something would work --> tried individual files, just data files, tried to create a new DB and then switching/replacing out with the files i needed...etc, nothing seems to work for me. The people I am trying to help out do NOT have backups.
I have a database that consists of 4 files (1 MDF, 2 NDF's and 1 LDF-file). I can confirm that by running:
select name from sys.database_files
The result is: Adam_Data Adam_Log Adam_FullText Adam_History
About a year ago this database had 3 more NDF-files, but we moved all the objects that were stored in these secondary datafiles to the other datafiles and removed them completely from the database. This operation worked correctly otherwise the sys.database_files view would have returned more files I assume.
Everything works perfectly with this database until I try to detach and reattach it. If I detach this database and immediately try to attach it again on the same server using Sql Server Management Studio it wants me to attach 7 database files! The 4 database files that are still being used by this database (which is perfectly valid), but it also wants me to attach the 3 old database files which obviously don't exist anymore and that aren't even reported by the sys.database_files view. Is there anything I can do to make sure that an attach of this database will work again? How can I completely remove these old database files?
I can detach/attach SSAS database.But I have a software that protects(backs up) the files of the SSAS Database.
What the customer needs is to be able to take these backed up files and port it to a different server and attach it there.But the new server complains that these files have no corresponding detach-log files.
The customer doesn't want to backup and restore the SSAS databases.
I have a Windows Server 2012 R2 2 node cluster with SQL Server 2014 FCI installed. Data files are on a separate Windows Server 2012 R2 file server. Data files share has been permissioned to the SQL Server service and SQL Server Agent service accounts as Full Control. NTFS Permissions are Full Control.
When I try to attach a database CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf') FOR ATTACHI get this error: Msg 5120, Level 16, State 101, Line 4 Unable to open the physical file "apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf". Operating system error 5: "5(Access is denied.)".
If I log into the file server (called APRICOT) and look at the NTFS permissions they all look good. I have also reapplied the NTFS permissions from the root folder down.
EDIT If I log on to one of the nodes in the cluster as the SQL Server service account and navigate to apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATA and copy and paste the data file, it works fine.
EDIT2: If I log on to the file server and Enable Inheritance at the root level, then Replace all child objects with inheritable permission entries from this object, I get this error:
User Account Control settings on all nodes and the file server are set to Never notify
I need to move files for a lot of databases that are all part of an AG. I've used the method at the bottom of this link with success on a small test DB.
I have just started using ASP.net.I have to develop a system that attaches word and PDF files to form. I also need to be able to do a full text search on the documents attached.I'm currently using SQL server 2000 as my backend DBMS, but I have never used it to attach files. My mates recommend using BLOGS but I'm not sure how to do this and I'm not sure if I can do a full text search using this solution.Could some body please recommend a solution to get me started.Thanks in advancedPaddy
We've tried attaching the mdf files given to us by some customers using the script they've provided, too.
I created the database with the corresponding names, and tried to run their script. However, I get the message "The media family on device 'D:DatabaseDVVAD01.mdf' is incorrectly formed. SQL Server cannot process this media family."
What could possibly have gone wrong? Is it also possible that the file was not properly detached, or what?
I am currently having a problem in the implementation of a Database for my C#.net winform application.
Look at the 2 diagrams below:
Diagram 1 : A typical databse design where it usees only 1 mdf file.
Diagram 2: A new proposed approach to implement the Database with multiple mdf files of the same schema!!
In the new proposal of the DB, each mdf files is kept separately and an additional module is required to attach and detach these databases as and when required. The purpose: the performance of all actions on the small data is not affected by the size of the large data
How does this 2 methods measure up in terms of resource usage, performance, etc??
Hello, I am using the full version of sql server 2005 management studio to attempt to attach sql express mdf files to sql server 2005.
I was using the databases from an ASP.NET commerce starter kit in sql express. I want to update to sql server 2005, so i detached the dbs. i deleted the sql express management studio ctp and installed sql server 2005 from the free 6 month trial available online.
I then attempted to attach the sql express files to sql server 2005 and get the following error:
TITLE: Microsoft SQL Server Management Studio ------------------------------
Attach database failed for Server '<servername>'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------
Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Could not open new database 'C:INETPUBWWWROOT<location>ASPNET.MDF'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
I also tried with the original mdf files i had downloaded without making any changes.
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
I've a serious problem. After a crash of one of my disk I loose all the data about the SQL Server db but I've the mdf and ldf files because were placed on another hard disk different from the SQL Serve Installation disk. I reinstalled the Sql Server (in a new path, a new hard disk letter) but I don't know how to rebuild the db starting from the mdf/ldf files. Is it possible?
I have an old sharepoint db (SQL Server 2000 Standard edition). What I need to try to recover is the data in log backup files from the docs table. I need to see if there is a way to get any data in the log backups from columns dirname, leafname and content. The column content is an image type which old documents (word, excel...). I don't have a full log chain since the last full restore so any data I can get out of the logs is better than nothing. I really just need to get the image files stored in the column content and place them in a shared directory for the users and let them see what they can use.
I have two databases on a Production Server that I want to Log Ship to a Test Server. According to the sys.master files the physical File Location is on an E drive. Early attempts at Log Shipping these two files error'd out due to space issues on the E drive (one Log Shipped and then one err'd out). I was subsequently informed from the server group that they would prefer that I Log Ship these two database files over to the M Drive where more space is available. In fact, they modified the Server Properties / Database Settings / Database Default Locations (for Data and Log) to the larger M drive (I'm not really sure why they just don't increase the E drive space but there is proabably a good reason).
Okay, so now my problems have been solved. Easy enough. Now I deleted the successful Log Shipped database and started from scratch. However, as before, one db restored and one failed (due to space issues). Apparently, both db are pointing towards the E drive. How is that possible?
So here I am with one successful database and the normal sys databases pointing to the E drive. What is the best way of approaching this move to the larger and preferred M drive?
I have a client with no backup of MASTERdb or Help_rev_devices, all I have is a couple of .dmp's of the production data. In order to run DISK REINIT I need to know the size of the database, is there any way of finding out the size of the database device, if all you have to go with is a backup dump file?
The situation : I am making mass changes to my program and database. The goal is to not make changes to the database at the customer sites, but to clone their current database, and work with that one.
The solution: I wrote code and queries to detach the database, copy it to another directory on the same computer with a different name, then attach it with the new name. The process is: run : sp_detach_db "' + aDatabase + '", ''false'';' (aDatabase is the original database name) Then, in code, delete the original .ldf file, and copy the database.mdf file to new directory with new name. then run (for both the new and the original databases):
This works, up to a point. The problem : When I inspect the properties of the newly attached database, with the new name and new directory, the logical name of the database is still the original name.
but it returns(even though I have deleted the .ldf files for the original dataset) : Could not open new database 'Test62'. CREATE DATABASE is aborted. Cannot create file 'E:Data6Test62Test62_log.LDF' because it already exists. Change the file path or the file name, and retry the operation. File activation failure. The physical file name "E:Data6TestTest_log.LDF" may be incorrect.
(original database name = data6 est est, new database name is data6 est62 est62) Is there a way to do this? Or do I just need to give it up?
hello i am having a problem attaching database. The Problem :
I have created a database using sqlserver express edition.now i have installed enterprise edition ,so i am attaching the database which is created in sqlserver express edition into the enterprise edition.it is showing error
TITLE: Microsoft SQL Server Management Studio ------------------------------ Attach database failed for Server 'SRINIVAS'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Could not find row in sysindexes for database ID 13, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Could not open new database 'FMS'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ Can anyone Help me
TITLE: Microsoft SQL Server Management Studio Express------------------------------ Attach database failed for Server 'srvsqlsrv'. (Microsoft.SqlServer.Express.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476 ------------------------------ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo) ------------------------------ One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error: 5173) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=5173&LinkId=20476 ------------------------------BUTTONS: OK------------------------------
I wanted to create a removable SQL Server 7.0 database. So I created the database using the sp_create_removable system stored procedure, imported objects and data, ensured that the database is configured properly for distribution on removable media (using sp_certify_removable system stored procedure), and detached it from original SQL Server. So far, so good. The database consists of 3 files: MYDB_SYS, MYDB_DATA and MYDB_LOG. I copied MYDB_DATA on a CD, and MYDB_SYS and MYDB_LOG on hard disk of a different server. If I execute the following statement:
Server: Msg 5105, Level 16, State 4, Line 1 Device activation error. The physical file name 'E:MYDB_DATA.NDF' may be incorrect. Server: Msg 945, Level 14, State 1, Line 1 Database 'MYDB' cannot be opened because some of the files could not be activated. Server: Msg 1813, Level 16, State 1, Line 1 Could not open new database 'MYDB'. CREATE DATABASE is aborted.
I can successfully execute the above statement if I make MYDB_SYS.MDF file read only, but then MYDB is created as read only, and this option can not be changed.
Hi all, I am facing a problem during attaching a database (.mdf and .ldf files are copied fro another server). the message I am getting as follows:
messages was from 'Microsoft SQL Server Management Studio Express':
===================================
Attach database failed for Server 'CTPL-1'. (Microsoft.SqlServer.Express.Smo)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exceptio nTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Smo.Server.AttachDa tabase(String name, StringCollection files, String owner) at Microsoft.SqlServer.Management.SqlManagerUI.Attach DatabaseData.PrimaryFile.Attach() at Microsoft.SqlServer.Management.SqlManagerUI.Attach Database.SendDataToServer()
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries) at Microsoft.SqlServer.Management.Smo.Server.AttachDa tabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions) at Microsoft.SqlServer.Management.Smo.Server.AttachDa tabase(String name, StringCollection files, String owner)
===================================
Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Could not open new database 'mci'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------ Server Name: (local) Error Number: 602 Severity: 21 State: 50 Line Number: 1
------------------------------ Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
===============================================
********************************
the followings are the command and mesage from Query Analyzer:
Server: Msg 602, Level 21, State 50, Line 1 Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Connection Broken
*******************************************
I am new to MS SQL Server and unable to solve the problem. Can anyone help me in this metter.
there was a failure due to power shutdown,and the .mdf and .ldf files on the DB is not longer available. there is a backup done 5days ago with the title nice_cls.BAK. How do i restore or reattach the .bak file on the NICE-CLS database.
My current server going nuts it restaring every one hour, I have 200+GB (80+ files) database size. The network guyes don't have backup. This is what I am trying to do. I have detach my database. I have copied all the file to new server. Now i am trying to attach using create, I can't just use attach because 16+ files. When I run following on my server it gives me an error.
Any help will be highly appreciated.
please email me the answer if you have.
Thanks.
Samir
CREATE DATABASE KEYFILE ON PRIMARY (NAME= 'KEYFILE001' FILENAME='F:KEYFILEDBKEYFILE001.MDF') ON SECONDARY (NAME='KEYFILE002' FILENAME='F:KEYFILEDBKEYFILE002.NDF') ON SECONDARY (NAME='KEYFILE003' FILENAME='F:KEYFILEDBKEYFILE003.NDF') ON SECONDARY (NAME='KEYFILE004' FILENAME='F:KEYFILEDBKEYFILE004.NDF') ON SECONDARY (NAME='KEYFILE005' FILENAME='F:KEYFILEDBKEYFILE005.NDF') ON SECONDARY (NAME='KEYFILE006' FILENAME='F:KEYFILEDBKEYFILE006.NDF') ON SECONDARY (NAME='KEYFILE007' FILENAME='F:KEYFILEDBKEYFILE007.NDF') ON SECONDARY (NAME='KEYFILE008' FILENAME='F:KEYFILEDBKEYFILE008.NDF') LOG ON SECONDARY (NAME= 'KEYFILE_LOG' FILENAME='F:KEYFILEDBKEYFILE_LOG.LDF')
hi all i am working on sql server 2005 When i am trying to attach database in my server i am getting following error. Plz help me to fix this error. the error as as follows :
Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Could not open new database 'ReportServer'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
hi all i am working on sql server 2005 When i am trying to attach database in my server i am getting following error. Plz help me to fix this error. the error as as follows :
Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Could not open new database 'ReportServer'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
Hi,I am tring to attached a database in SQL using the following command.Can any one please help. Basically I have a MDF and LDF files which Ineed to open in SQL 7.0, these files are from a diff SQL7.0 machinewhich is crashed.PLEASE HELP. There are many valuable info in the database.Command :EXEC sp_attach_db @dbname = N'Digitalindia',@filename1= N'C:SqlMaster.MDF',@filename2= N'C:SqlMaster.LDF'Error :Server: Msg 5172, Level 16, State 15, Line 1The header for file 'C:SqlMaster.MDF' is not a valid database fileheader. The PageAudit property is incorrect.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hiis it possible to attach a database when a log file is missing?My database had one .MDF file and 2 .LDFs. I lost one of the .LDFs. Is itpossible to recover any data?Tegie
Q1.)In SQL Server 2000, is it always possible to use copies of thedata and transaction log files of a database from one server toreattach to a new database on another server, or even to the sameserver, without first detaching from the existing database?Books Online says that detaching a database closes the data and logfiles cleanly. I read some where that some times, it is possible toattach without detaching, if the data and log file are in stablestate.This is what I tried - Tried to copy Northwind.mdf and ldf files usingwindows explorer and it gave an error message that the files are inuse. I shutdown the server and now I could easily copy them. Afterthat I renamed these files and successfully attached them as a newdatabase.Q2.)Is it possible to do the same with large production databases?Q3.) Please explain what goes on during detaching /attaching process.Thanks in advanceRavi
I then wanted to download and install the Adventure Works database located here: http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
Since I am running Vista 64-bit, I downloaded and installed AdventureWorksDB_x64. It wouldn't install. I got "Access is denied" and CREATE FILE error plus several others.
I then uninstalled it and downloaded AdventureWorksDB - this is the X86 version. It installed fine.
I've had a look at the white paper on use instances and also stopped using Management Studio, as you suggested. The problem is that I can run my application from VWD quite happily, until for no reason the application can't connect to the database. This happens ramdomly and is very frustrating. I have worked out a way to correct this problem with the following method (I don't know if this is correct but it's the only work around I know.
1) I close down VWD and open Management Studio,
2) I right click to attach a database, and then navigate to the .mdf file for the database, and click to attach it. I then delete the existing icon for that database (which I can't acess anyway, it only shows me the main icon but not the tables)
3) I rename the new instance of the database to the correct name (minus path info etc)
4) I create an SQL script as follows:
EXEC sp_grantlogin 'jonASPNET'
(This line executes correctly),
Then I enter these lines:
USE BalloonShop *name of database *
EXEC sp_grantdbaccess 'jonASPNET'
(At this stage when I execute the query, Management Studio informs me that this name already exists in the database and so doesn't execute)
5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.
I know I've got some configuration settings wrong somewhere but I find the whole user instance thing confusing. For the record I'm running the appllication through VWD's built in web server, I did originally have it through IIS but I moved it (this will explain the confusion of locations from my first post, I gave you the original location, sorry about that).
Any advice you can give would be gratefully recieved, ie is there an easy way to reconfigure my settings to make everything work as expected or have I got to start the database again from scratch (not a pleasent thought). Also is there an idiots guide to SQL Server Express anywhere that I can read.
Jon
ps I've just realised that I get the 'can't connect to database problem when running the application after I've made a change to the database from VWD ie adding a new stored proceedure, Don't know if this helps, but better you get the full picture.
