I have copied the .mdf and .ldf files from one db and I'd like to attach the copies to a new db on the same server. When I try to attach the copied files I get the error, "the physical file name may be incorrect". Original db filename is "MC_DISC_Data.mdf", I copied this and renamed it to, "MC_Data.mdf" in my sqldata folder. I can see the physical file name is still the original name when I'm trying to attach db using EM, how can I change it to the copied name?
Hi! I did: alter database mydb set single_user with rollback immediate; exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';
then I tried to copy files to new location on other drives, same server but got >>Cannot copy <myfile>: Access is denied Make sure the disk is not full or write-protected and that the file is not currently in use<<
I also tried rename of file without success. I also tried with db service stoppet (not preferred) without success.
How to find out, which process locks the files? Best regards
This might sound dumb, but BOL says you can use detach/attach to "MOVE" a database but can you detach and copy the files to a new server, leaving the files on the original and running the attach on the new and the old? (effectually a way to copy)? I know how to use data transformations/import,export was curious?
An attempt to attach an auto-named database for file C:WebApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. My web.config file, the connection strings <connectionStrings> <add name="POAdatabaseConnectionString1" connectionString="Data Source=sss;Persist Security Info=True;Initial Catalog=POAdatabase.mdf;Integrated Security=SSPI" providerName="System.Data.SqlClient" /> </connectionStrings> I cann't really find the statement for attaching aspnetdb.mdf. I don't where to find it. Is there any other web.config file in the C drive? Thanks for help!
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest Set Partner = N'TCP://SQL2.mycom.com:5022'; go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
I am attempting to use the copy wizard to copy databases from SQL Server 2005 to SQL Server 2008 R2 w/ FP1.
The copy fails with a login failure to SQL Server 2005. I have a user id & password under Windows for both servers. I have a user id and password under SQL security with the called for admin security rights.
The 2005 server has two instances, 20 databases, two dozen maintenance plans, and over a hundred users. I really would like to use the utility so I don't have to recreate everything manually.
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
I did a sp_detach_db of a database, then deleted the log file because it was too big. Now, when I try to sp_attach_db, or even sp_attach_single_file_db, it will not re-create the db.
When I tried to attach a database from Enterprise manager, I got "error 3624, location: scanrid.cpp:321 Expression: m_len != 0 spid 51 proseccid 436......" The files are all there, but it seems there are problems of activating files, or the files are crashed. Any one got idea? Actually, we just need one table in that database. Is there any way to save a table from mdf file or ndf file? Or repair files?
I have database DB1 on Box1 and I have the full backup of that database and also have data file and log file.If I want to restore the database on a diff box How can do that.
1.How can I restore the Database? 2.How ca I attach the data file to the database on new Box?
I received three Files .mdf .ldf .ndf and a text how to use attach but i always receive an error message "database is write protect ......." What I do wrong ?
I've created a database inside my ASP.net project, the database is in the App_Data folder as exepcted.I can't run the aspnet_regsql until I have attached it using SQL Server Management Studio, but when I try to attach the database it doesn't let me navigate through my folders properly. The database is in C:UsersWardieDocumentsFirstProjectApp_Data but SQL SMS only lets me navigate to the folder C:UsersWardie and no further for some reason, meaning I can't attach the database and can't use the aspnet_regsql service to create the membership tables.Does anyone know how to fix this?
Hi guys, I had user who would like attach trial database and de-attach database when he do development. I do not want him had any other permission except the database he attach. I give him database creator role. But it did not seems work. I have to give him database creator role and system admin role also. Any one have better ideas about this? Thanks for help Helen
For a test I detached a database, copied the ldf to a new location, and re-attached successfully. OK, now I want to use the orignial ldf, so I detach, and when I try to attach to the origninal ldf I get:
Server: Msg 3624, Level 20, State 1, Line1
Location: recovery.c:2693 Expression: seenCkptEnd SPID: 8 Process ID: 109
Connection Broken
Any Ideas on what happened and how to reattach to my original log file?
I know the chances of getting a positive answer on this are remote but here goes: I have a db that I need to move to a new server (that isnt on the same network !) Firstly tried, backing up the DB, copying the backup to various removable media, copied it onto the new server, then tried restoring. The restore almost completes, then comes up that backup is incomplete ! If I restore the DB on the original server from the same backup file it works fine !
Second method, detached the DB, copied the mdf file to a memory stick, copied onto the new server, attempted to attach the mdf file and get: I/O error (bad page ID) detected during read at offset 0x00000003f80000 in file ..........mdf Error: 823, Severity: 24, State: 2
If I re-attach the file on the original server this works !!!
My next step will be to get the two servers connected over the network, so that I can use DTS, but this is not ideal and would like to avoid this if possible.
If anybody has any suggestions, I would be eternally grateful.
p.s. I have tried restoring/attaching on other servers in case it was a problem with the new server, but get the same problems.
MSSQLSERVER VER : 2000 Using - Attach and Detach Database to move the database from one Server to another server. I did detached the database from Production and attached my test server, everything OK
Questions about - syslogins & sysdevices data dictionary tables 1. When I select * from sysdevices on production it shows all the database name size,phyname etc. After attaching to my test server I cannot see these things.. I know because it different master database but, how do I fix this. 2. syslogins - is there way to move all the syslogins after attaching to test server.
Please let me know if you guys have done anything on this.
hi , i want to use a database that someone created in SQL SERVER 2000/WINDOWS SERVER 2003.My system is SQL SERVER 2000/XP SP2 PRO .The problem is that the database is an unknown file .I mean ,it isn't ,as usually, an .mdf or .ldf file ,so i cannot attach it to the server. Is there a problem in compatibility between the different versions of windows?Or is missing something from the database?What can i do?
I want to attach a database that one of the developers had detach it. The LDF file was deleted after detach.
I tried to attach and rebuild the log file in SQL2005 using the following:
USE [master] GO CREATE DATABASE [Test] ON (FILENAME = N'G:MSSQLDataTest.mdf') FOR ATTACH_REBUILD_LOG GO
I get the following error:
File activation failure. The physical file name "D:MSSQLDATATest_log.LDF" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down. Msg 1813, Level 16, State 2, Line 1 Could not open new database 'Test'. CREATE DATABASE is aborted.
I used SSMSE to detach my database so that I could create a copy - no problem here.
But then in SSMSE I right-clicked Databases, chose attach and expected to see my original database. But there are no databases shown in the attach window!
I have tried to find the original database in SSMSE but nothing I do allows me to see it. However, Windows Explorer shows me that the files are still where they were when I detached them.
Hey gang,I've been trying to restore a DB from it's MDF and LDF all morning;the catch? The LDF appears to be corrupt.When I first started the SQL Server, the db in question was marked as"Suspect'. I did some research on this and it has caused me to attemptdetaching, backing up, deleting the LDF, using ATTACH DB and evensp_attach_single_file_db, and sp_add_data_file_recover_suspect_db.Nothing works.So, with an MDF and no LDF ... is it possible to recreate thisdatabase somehow? Can I attach the MDF to another (empty) database toretrieve it's contents? (Tried it, couldn't get it to work), can Iextract the contents of the MDF - even if it's just the objects andnot the data itself - in some capacity?I've read in various places about attaching an MDF with no LDF and thesystem will recreate the LDF as needed, but that doesn't work either.Ideas are most, most, most welcome.
I plan to move my database file to another folder, I detach it first (successfull), but I can't move (Cut&Paste) my database to another folder (Access is denied), And then I go back object explorer but error when I try to Attach
"Failed to retrieve data for this request (Microsoft.SQLServer.SmoEnum)...... CREATE FILE encountered operating system error 5 (Access is denied)"
please tell me how to attach my database, I really need the data..
I have a database on a server and want to put it my local laptop that also has sql express. I attached the db, I see it in Management Studio but it does not show up in
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData so my app is not working correctely. How do I get the db to show up in the MSSWLData folder. I'm a bit confused.
That's the error I get.Please help ' TITLE: Microsoft SQL Server Management Studio Express------------------------------Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)------------------------------C:Program FilesMicrosoft SQL ServerMSSQL.3MSSQLDataMyNewDB.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)
After having published the timetracker starterkit from visual studio 2005 to local wwwroot,
I try to access my homepage with ie.
But still get the same error message : An attempt to attach an auto-named database for file C:Documents and SettingsEmmanuelMy DocumentsVisual Studio 2005WebSitesGestWaresApp_DataTimeTracker.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I have a database that I developed using both Toad for SQL and SQL Server Managment Studio.
All was working fine. I could connect to the database with one of the above and also run two an applications I developed (one in C# and One in VB.NET using VS Express) using the standard SQLConnection class.
Now, for one reason or another I decided to create a new data source in my Visual Studio project and pointed it to the database fine.
At first it seemed ok as I could see all tables from teh database. However somethign strage has happened. I now can't access the database in TOAD or SQL Server Managment Studio. I also couldn't connect using the above string.
I finally managed to get the two appllications I developed to access database using the following connection string...
Data Source=.SQLEXPRESS;AttachDbFilename=C:Program FilesMicrosoft SQL ServerMSSQLDataCDEvolution.mdf;Integrated Security=True;Connect Timeout=6000;User Instance=True;
and the applications work fine, however I can access the database using the DB tools.
In SQL Server Managment Studio I remove the database and tried to attach it again but I keep getting the following error message...Could not find row in sysindexes for database ID 5, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Could not open new database 'C:PROGRAM FILESMICROSOFT SQL SERVERMSSQLDATACDEVOLUTION.MDF'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)
Can not attach database, Error message; The file you've specified is not a valid SQL Server database file.
My costumer HDD some files Deleted mybe elektric prblem -Elektric go,com,go,come- then windows not opened. insert corrupt hdd Another computer, then recovering (recover my files software) Recover mdf and ldf files. but I can not not attach mdf. file,
I am recovery .mdf file http://www.officerecovery.com/mssql/ some table ok. but my importand table is not ok.
if I can recover another recovery software , my database correct recover or same.? I am using RecoverMyFiles.
We are trying to attach a Database from SQL7 to SQL2K SP3.
On SQL7
The Database was detached and the MDF copied to the New Server.
On SQL2K
We used the attach Db (in Enterprise Manager).
We get a red 'x' as normal against the logfile and noramlly it would create a new one when u proceed, but this time we are getting an error after the dialogue box asking to create a new one:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'd:mssql7datadbname.ldf' maybe incorrect.
The path is where the logfile resided on the old server.
