Views On Attaching/Detaching Mdf Files

Apr 9, 2008

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??

Need comments and views. Thanks.

View 9 Replies


ADVERTISEMENT

Detaching And Attaching

Dec 10, 2006

Hi,
Im not new to SQL Server but I am new to v.2005. We have done a class project and to finish up I am creating schemas, logins and different user roles and permissions.
I now need to detach the DB and send it to our team leader for submission. I'm scared to do this because I don't know if all the logins and passwords I've created will appear in it's new location. Will they be there in the new server's security folder just as I created them or will they dissapear into the ether?
Signed:
Sweating very much


It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

View 7 Replies View Related

Attaching-detaching Db = Too Long

Jun 18, 2004

Why does it take time to detach or attach a Db sometimes ?

View 4 Replies View Related

Attaching And Detaching A Database

Jul 20, 2005

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

View 1 Replies View Related

Detaching And Attaching A Database For My Project

Mar 22, 2008

I hope this is the right forum for this question.

I am working with an asp.net project in Visual Web Developer 2005 Express. This is a starter kit project. There is a database that came with the project that I want to open up in the SQL Server 2005 Express management studio so that I can modify it.

I have detached the db in the VWD environment.But, I do not see this db in the db folder with in the SQL Server studio's IDE. I am assuming that I have to attach the database to the SQL Server management studio in order to work with it. Is this the correct way to do this type of thing? Is there a better way of doing it(a stored proc like mentioned below)?

I also have a question about the icons that pertain to this situation. I have noticed two variations of the same icon. I want to be sure I am interpretting these correctly. I am assuming that the icon that is a cylinder representing a db with the little
electrical plug coming out from behind it means that the db is attached. There is another version of this icon that has a red "x" over the plug. I am assuming that this means it is detached. Is this correct?

I have attached and detached dbs in Sql Server Express. Is the process the same for doing this with a project in Visual Web Developer? Is there a stored proc that I can use to detach from the SQL Server 2005 environment after I have made a database and attach this to my project in VWD in one step?

-thanks in advance

View 4 Replies View Related

Detaching DB From MSDE And Attaching To SQL Express Using SQLDMO.

Dec 19, 2007

All,

I wanted to to detach a DB say MyDB from MSDE and wanted to attach it to SQL Express.
It could able to Detach the DB from MSDE,but attachment to SQL 2005 is failing. When I verified MSDE Instance through Managment studio "MyDB" wasnot there ; means it got detached.I couldnt find the DB in SQL 2005. When I tried manually through command prompt I was able to attach "MyDB" to SQL2005.
Please help me out!!
Here is the code which I have written.

if SUCCEEDED(hr = CoCreateInstance (CLSID_SQLDMOServer, NULL, CLSCTX_INPROC_SERVER,
IID_ISQLDMOServer, (LPVOID*)&pSQLServer))
{

pSQLServer->SetLoginSecure (TRUE);
SQLDMO_LPCSTR DB = OLESTR("MyDB");
//Connect to MSDE First
if SUCCEEDED(hr = pSQLServer->Connect (szDbServer, szUserName, szPassword))
{

pSQLServer->DetachDB(DB,lpszResult); //Detach the DB
pSQLServer->DisConnect(); //Disconnect MSDE
LPBSTR lpszResult2 = NULL;
long lNumDB;
//Connect to SQL 2005
if SUCCEEDED(hr = pSQLServer->Connect (szSQLExpDbServer, szUserName, szPassword))
{
//LPCOLESTR lpo = A2COLE(sTemp);
//sTemp.operator LPCTSTR
pSQLServer->GetDatabaseCount (&lNumDB);
SQLDMO_LPCSTR files = OLESTR("C:\Program Files\Microsoft SQL Server\MSSQLData\MyDB.mdf,C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_log.ldf");
pSQLServer->AttachDB(DB,files,lpszResult2); // Attach the DB to SQL 2005

pSQLServer->DisConnect(); // Disconnect SQL 2005}

View 1 Replies View Related

Is There A Problem Detaching A Database From SQL2000 And Attaching It To SQL2005?

Dec 5, 2007

Hi

I have to migrate a SQL2000 database to SQL2005, I tried to use CopyDatabase Wizard (CDW) but invariably stopped all processes (may be for permissions, but I really I don€™t know) so I decided to detach de database, copy files to new server and attach it to SQL2005, it apparently works well, except for the fact that Schemas where created with the name of the users of SQL2000.
Is that correct?
Could it affect the performance or any other thing?
Is correct to do what I did?
Could be another problem in the recently attached database?
(Compatibility, data loss, corrupted structures, collation or something like that)

The server I am using is in a domain, it has windows server 2003 R2 SP2
@@Vesion returns

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

And in the source server with windows 2000 SP4
@@Vesion returns

Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


Thank you in advance

Any comment will be greatly appreciated

JAPonce

View 4 Replies View Related

Unable To Open User Instance Type Database After Attaching And Detaching In SQL Mgmt Studio

Apr 15, 2007

I've been using my DotNetNuke database for weeks on my local PC with SQL Server Express. It attaches as a "User Instance". Basically, I am using all the DotNetNuke defaults. Today I decided to attach the DotNetNuke database in SQL Server Management Studio Express and peek at the tables. I attached the database; then I detached it when I was done. This all went well. But when I tried to open my DotNetNuke web site again I now get a message that it cannot connect. Attaching and detaching the database in SQL Server Management Studio obviously changed something about the database, but I don't know what. My connection string to the database has not changed.



The specific error displayed in the browser is:
ERROR: Could not connect to database specified in connectionString for SqlDataProvider

In event viewer is this message:

Exception information:

Exception type: SqlException

Exception message: Cannot open user default database. Login failed.

Login failed for user 'NT AUTHORITYNETWORK SERVICE'.



My connection string is this:

<add name="SiteSqlServer" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;" providerName="System.Data.SqlClient" />



Any suggestions about how I can correct this?

View 3 Replies View Related

SQL Server Management Studio Express On Vista Home Premium (Error On Attaching And Detaching .mdf)

Sep 5, 2007



I have SQL Express 2005 SP2 installed in my Vista Home Premium. I am using SQL Server Management Studio Express to manage .mdf.

When I click "Add..." to attach .mdf database, I have this prompt error message :

The server principal "My-PCMyName" is not able to access the database "model" under the current security context. (Microsoft SQL Server, Error: 916)


I have elevated the rights, "Run as administrator", of my SQL Server Management Studio Express, but still having problem.

I also encounter security error when detaching the existing .mdf :
The EXECUTE permission was denied on the object 'sp_detach_db', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)


Please advice. Thanks.

View 8 Replies View Related

Attaching Files

Jun 21, 2005

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

View 1 Replies View Related

Error In Attaching .mdf Files.. How Can This Be Solved?

May 11, 2006

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?

View 1 Replies View Related

Recovery/attaching Database Files

Jun 2, 2006

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.

Any thoughts would be appreciated. thanks...

View 7 Replies View Related

Attaching Sql Express Files In Sql Server 2005

Apr 18, 2006

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.



any help here?

thank you,
K

View 1 Replies View Related

Attaching A Valid Database Fails Because Of Old Mdf-files

Sep 3, 2007



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?

Regards,
Michael

View 8 Replies View Related

SQL 2012 :: Attaching SSAS Database Files On A Different Server?

Jun 26, 2015

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.

View 2 Replies View Related

SQL Server Admin 2014 :: Attaching Received Files To New Database

Jun 25, 2014

I am actually very new to SQL databases, I have received an .MDF and .LDF for a database of size 50 GB...

I need to create or attach these files to a new database and extract some columns then convert them to .text or .csv...

View 5 Replies View Related

SQL Server Admin 2014 :: Cannot Access Path When Attaching Database With Data Files On SMB Share

Oct 1, 2014

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

View 0 Replies View Related

Is It Possible To Make An Installer Package(msi And Exe Files) For Stored Procedures,tables,views

Nov 9, 2007

Hi,

I've a doubt regarding deploying and creating package (MSI and EXE files) for SQL Stored procedures,views,tables,functions,triggers etc.,just like ASP.NET application by publishing and deploying..Is it possible in the same way for sql objects.If so,pls help me how to do it?

Thanks in advance.

View 1 Replies View Related

Reattaching And Detaching

Jan 29, 2002

Hi
I have tried to restore on db to another on different servers. Unfortunatly the sort orders dont match. Does anyone out there have any articles or know how to attach the the source db data and log files to the destination db. I would appreciate any assistance.
Thanks
D

View 1 Replies View Related

Reinstalling SQL 7 After Detaching Dbs

Dec 8, 1999

I understand that you must sp_detach_db and sp_attach_db to move databases to a new server. What if you want to reinstall SQL 7.0 to a different drive on the same server? My situation is this:

We have C:, D:, and E: drives. I accidentally installed SQL 7 on the C: drive instead of the D: drive. I need to reinstall on D: and I need the data. Any suggestions?

Alan

View 1 Replies View Related

Detaching Non-existing Db's

Jan 9, 2006

Hello all

From time to time I receive mssql server databases to export from our customers. Last time I deleted the database files without detaching them. How can I detached them, since I need to atach new ones with the same name?
I'm using MS SQL Server 2003 and the databases are 6.5.

Thank you all.

Regards.

View 7 Replies View Related

Detaching Database

Sep 28, 2007

I need to detach a db. When I right click and choose detach the popup box shows 0 connections however, I'm getting the error 3701 cannot detach because it's currently in use. How can I tell what is causing this or what is using the db?

View 3 Replies View Related

Detaching And Reattaching A Read-only DB

Nov 30, 2000

Anybody know if it's possible to re-attach a readonly database without making it operational? I'm using log shipping to keep my hot standby current, but would like to take the detached db and duplicate it elsewhere, I just cant re-attach it as read-only again when I'm done. Thanks!

View 2 Replies View Related

Detaching A Replication Databse

Mar 17, 2004

I am trying to detatch a database that is set up as a publisher. I have desabled the replication tasks on both the publisher and subscriber db and am still unable to detach the db. Any ideas?

The image below is an example of the message I receive. I have tried after hours so at that time the users are 0.

Thanks.

View 3 Replies View Related

Detaching A Replication Databse

Mar 17, 2004

I am trying to detatch a database that is set up as a publisher. I have desabled the replication tasks on both the publisher and subscriber db and am still unable to detach the db. Any ideas?

The image below is an example of the message I receive. I have tried after hours so at that time the users are 0.

Thanks.

View 2 Replies View Related

Detaching And Moving Databases

Nov 23, 2006

Not sure if this is the exact forum I need but here goes.

Having a bit of an issue 'moving' a database. I've only just installed SQL Server 2005 on a Development/Test box so I can learn and mess around with it without doing any serious damage to anything important.

One of the first things I've done is install the AdventureWorks database and, having installed it, decided I would move the files to their optimum locations. However having detached the database I can't seem to move or copy the .mdf or .ldf files - something still seems to have its tentacles around them - 'Access denied' error. Even rebooting the server doesn't work. So I've had to delete then re-attach the database. The question then is does Detach serve any useful purpose? Or am am doing something wrong? With SS2000 I could detach a database, relocate the physical files and re-attach in seconds. The same procedure with SS2005 has taken me an hour!

Regards,

Gordon F.

View 4 Replies View Related

Are Embedded Views (Views Within Views...) Evil And If So Why?

Apr 3, 2006

Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.

View 15 Replies View Related

Error 4064 After Detaching And Reattaching Database

Oct 8, 2007



Since the "Cannot Open User Default Database (error 4064)" thread is closed, and I had this same error, I'd thought I'd leave the solution that worked for me since I spent about 2 hours researching this problem. After detaching my database, then reattaching it, I couldn't log back in with my login ID. I could get in using the sa account. I could also log in with my login ID if I set the default database to master using SQL Server Management Studio by navigating to Security, Logins, right-clicking my login ID, then setting the default database to master. However, this isn't a solution, because I need my login ID to default to the database I reattached, not master.

One way of resolving this is to set the new Login ID as the owner. Right click the database under Databases in Management Studio, choose Properties, choose Files, then set the owner with the new login ID. I can then connect to the reattached database with my login ID. If I set the owner back to sa, I can't connect with my login ID, I have to use sa. If someone has a solution to relink my new login ID and the user with the same name so I can log in with sa set as the owner, I'd love to hear it. I can't make that work.

BTW, if I go under Logins, right click my new login ID, choose properties, then User Mapping, my reattached database is selected under Users mapped to this login. However, the Users and Default Schema columns are both set to dbo.

--Steve

View 1 Replies View Related

Whats The Difference Between Detaching And Taking A Db Offline?

Oct 22, 2007

what is the difference between these two db tasks in mgt studio?

View 7 Replies View Related

Large Views Vs Multiple Small Views

Sep 6, 2007

Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?

View 1 Replies View Related

Recompiling Views That Reference Other Views

Jun 28, 2007

Hello.

Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.



We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.

Recently we made changes to the secondary view.

After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.



Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.

My questions are:

1. Exactly why did this happen and is there a proper name for it when it does?

2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?



Thanks for any help on the matter. I greatly appreciate it.

View 3 Replies View Related

Quicky : Views Of Views Of Views

Feb 22, 2007

Hello,

to make a report easier I'm developing it using a view of joined views of joined views.

Is there any significant performance penalty as opposed to just having one big select?

Cheers.

View 1 Replies View Related

Re-Attaching DB

Aug 10, 2005

Forgive my ignorance, but I'm just working thru some examples, and I tried to reattach a db to another server, without the log file I created for the db. Having reattached it, it worked fine - without the log file??? Where does it make any log records? Why didn't it complain?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved