Does SQL Server Agent need to be running in order to execute the
sp_attach_db stored procedure?
I am getting device activation errors while running this stored procedure.
Is there any other way to attach those files back to the SQL Server.
PLEASE HELP
Can anyone spread any light on the following.
I've created a database of 2 tables which exists over 2 drives. I had a 6 Gig log file on one drive and a 1 Gig log file on the other so I used sp_detach_db to detach it. I then deleted the log files and attempted to sp_attach_db with the following:
"sp_attach_db @dbname =N'cwc_load', @filename1 = N'f:cwc_loadcwc_load_data.mdf', @filename2 = N'e:cwc_loadcwc_load_data2.mdf'"
which are the locations of the .mdf files.
I get the error messages 5105 and 945 (which cannot be tracked down using either the Microsoft site or on-line help) but the messages are "Device Activation error. The physical file name 'F:cwc_loadcwc_load_log.ldf' may be incorrect"
"Database 'cwc_load' cannot be opened because some of the files cannot be activated"
which to me tends to suggest that the database is looking for the log files (now deleted).
I've tried forcing a new log file I created using the same locations for the mdfs. I've tried using create database cwc_load on primary (filename = 'etc) which gives the same error message, please tell me someone out there has a resolution for this.
As part of moving to SQL2K from SQL7 I'm doing sp_detach_db and sp_attach_db on the source server. For those databases that have multiple .ldf files do I need to add a separate filename= line to the sp_attach_db command for each .ldf, or only reference the original .ldf in a single filename?
Thanks,
AL
P.S. Given that I've done a few databases already, what's the effect of entering only the single filename line if multiple filename lines were required?
how do i use "sp_attach_db" properly so that i can make a new database with only the .mdf file?
View 2 Replies View RelatedI am trying to use sp_attach_db SP.
I have a dadafile and a log file at D:Asset
which I am trying to extract using
EXECUTE sp_attach_db 'Asset',
'D:AssetAsset_data.mdf',
'D:Assetasset_log.ldf'
can any one of u tell where i went wrong
I am getting this errror "Device activation error. The physical file name 'D:AssetaaAsset_data.mdf' may be incorrect."
I am trying to use the database provided from
http://www.trigonblue.com/sp_download.htm#Diagram
to practise stored procedures.
Thanks in advance
I had a SQL Server falure. I rebiuld Master and tried to attach my database
with sp_attach_db? but get an error
Location: pageref.cpp:3931
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 10
Process ID: 119
Connection Broken
Hi,
I'm doing some work for a client who has not been backing up
his databases. After a server crash he has been left with
the .MDF and .LDF files. I have managed to successfully
use sp_attach_db on all but two databases. When attempting
to use sp_attach_db I get the following error message :
1> sp_attach_db @dbname = N'CSGDEV', @filename1 = N'e:SQL_DataDirDataCSGDEV_Data.MDF', @filename2 = N'e:SQL_DataDirDataCSGDEV_Log.LDF'
2> go
Warning: sort order 51 in database differs from server sort order of 52.
Warning: Unicode comparison flags 196608 in database differs from server
Unicode comparison flags of 196609.
Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1
Could not attach the database because the character set, sort order, or Unicode
collation for the database differs from this server.
Warning: sort order 51 in database differs from server sort order of 52.
Warning: Unicode comparison flags 196608 in database differs from server
Unicode comparison flags of 196609.
Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1
Could not attach the database because the character set, sort order, or Unicode
collation for the database differs from this server.
I'd appreciate any ideas. I'm not a SQL Server expert (heck, I'm not even
a Windows expert...) as you can probably tell.
Thanks and cheers,
Deeran
I entered the following in the Query Analyzer and got the result shown:
EXEC sp_attach_db @dbname = N'DS2004',
@filename1 = N'd:ds2004_data.mdf',
@filename2 = N'd:ds2004_log.Ldf'
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'd:ds2004_data.MDF' is not a valid database file header. The PageAudit property is incorrect
I can find nothing about PageAudit property.
What is it and how do I need to modify the command?
Many thanks
HiWith the sql server on line help , The syntax for the sp_attach_db hasthe file name .It is aslo given that max of 16 files can be geven. Iattached a db with a single d.mdf .could u pls tell what r the 16 file types or how a db can be attachedwith 16 files.With thanks
View 1 Replies View RelatedHello,
We use local server windows groups to allow or disallow use of our SQL databases. We are now being asked to move off of our current server. My thoughts are to dettach and attach the database. What is going to happen to all my permissions based on the user groups of our old server? I intend to create the same group names on the new server but they will be newservergroupname as opposed to oldservergroupname. Is it just a case of scripting the database user, roles and object level permissions an manually changing oldserver to newserver?
I am attaching a database with 3 data files.When I execute "exe sp_attache_db..." I obtain this error:database 'POINT' cannot be opened because some of the files could not beactivated.I have deleted its LDF file.Usually I detach my db, then I delete transaction log, and reattach 3 datafiles...Now it doesn'work!!!!!!!!!!Someone can help me?Thanks.
View 1 Replies View Related
hi,
My problem is, I am going to ship MSDE with our product. Now the problem is that if some one copies the .MDF and .LDF files and uses "sp_attach_db" to attach the db files to his own server then my database will be exposed to him. I have tried this and it gets exosed. I cant deploy NT Authentication for the database, bcoz if the end user installes Enterprise manager then my database will be exposed to him. Is there any way that i can sequre the LDF and MDF files of my database so that no one can use that with sp_attach_db? I m using MSDE(Sql server 2000).
I successfully install SQL Express silently.
Then I try the following
-S .INSTANCE -Q "sp_attach_db 'ATTACH_AS_NAME', 'C:Program FilesAPP_NAMESQLDataAPP_DB.MDF'"
This works...sometimes. But Many times I wll get a permissions error on the folder "C:Program FilesAPP_NAME". If I go and give "Everyone" "Full Permission" to the folder...it works fine.
My questions are:
1) What system users need access (and at what level) to this folder to do an attach? Because obviously giving everyone full access is bad.
2) Is there a better method for deploying a populated database with my application that this?
Here's one. :)
I have Database A in Server 1 which contains objects (tables and SPs) that are owned by users other than dbo. Then I detach this database from Server 1 using sp_detach_db and attach it to Server2 (sp_attach_db). Even if the same logins exist or are created in Server 2, I still won't be able to access the objects in Database A using these logins. I know that this happens because of different SIDs for the same login names across the 2 servers. So given that I want to achieve this without having to use the Import Wizard or any other method, how do I go around this lil bit of a problem? Any thoughts? :)
Thanks!
Hi,all.
I tried to issue:
sp_attach_db pubs,'F:anthonypubs.mdf','F:anthonypubs.ldf'
where the pubs.mdf and pubs.ldf are on a remote mapped drive F:,
I got error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'f:anthonypubs.ldf' may
be incorrect.
Is attaching a database ONLY supported for local servers ??
What about creating database on remote drive? (I have same error msg back)
Thx
Anthony
Here is the error, I receive:
Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF' may be incorrect.
Device activation error. The physical file name 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF' may be incorrect.
Msg 5170, Level 16, State 1, Line 1
Cannot create file 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_log.LDF' because it already exists.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'TestDB'. CREATE DATABASE is aborted.
Here is my attach script:
USE [master]
GO
sp_attach_db @dbname= N'TestDB',
@filename1 = N'S:Program FilesMicrosoft SQL ServerMSSQLDataTestDB.MDF',
@filename2 = N'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF'
This server has a Raid 5 Disk Array partitioned into three Logic Drives C:, S:, L:. Not sure, why the server was configured this way. If I attach the database with the data and log files on the same logical drive, the DB attaches successfully. When I try to put the data and log files on separate logical drives, it fails. Issue seems to be with the drive partitioning. Any Ideas?