I need to upgrade the currect db to sqlserver2005, and the current db used multiple filegroup and file
I first detach the db, and I want to make sure if I need to attach each db file after upgrade to sqlserver2005
Thanks
This is a reoccurring error in one of our Domain Controllers event logs…
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 7/24/2007
Time: 6:23:05 AM
User: N/A
Computer: DC2
Description:
There are multiple accounts with name MSSQLSvc/dbsrvr-02.olntv.local:1433 of type DS_SERVICE_PRINCIPAL_NAME.
the Server dbsrvr-02 (sql server 2000 standard edition sp3a) has a default instance and it looksl ike 2 named instances installed that we are not using. Ive searched this error and cannot find a thing.. should this concern me??? anyone ???
I am getting a bit concerned with the size of my log file and my understanding of backups and how the log file should be getting reduced in size. I have a production database that is 12 GB and the log file is 275 GB. The database file is set to autogrow at 1 MB and unrestricted file growth. The log file is set to 10% file growth and restricted to 2,097,152 MB file growth. I perform a full database backup each night. I had thought that all transactions in the log file would be rolled into the database file and then the log file auto-truncated in size during the backup process. I have never seen a log file stay larger than the database file. Please advise how I may keep the log file size (growth) down. Thanks!
Hi all,
We are using SQL Server 2005. The auto update statistics and auto create statistics for a database is set to ON. This database has a very heavy work load. When I checked the individual statitics , still the last updated statistics is in a old date value (few months ago).
Is it necessary to manually update the statistics for the same database? Or can we rely upon "auto update statistics" itself ?
Usually in what frequency the manual UPDATE STATISTICS should be run on production system which has heavy transactions ?
Thanks & Regards,
Hariarul
Hello,Our company has developed several Access applications for our clientsand we wish to expand to use a database server. We wish to use Accessas the front end application and SQL Server 2005 as the back endDatabes Server. Making a Server-Client application database.I'm very new to SQL Server so my concern is how the licensing worksfor a developer like us. I am aware that we can purchase the Developeredition for $49 USD. Our clients will then have to purchase a theirrespective licensing option.We do not have an issue with this, but i would like a betterunderstanding of how this works.1) The developer edition (DE) is the same as the Enterprise edition.Would our clients have to buy the Enterprise edition ($24,999) if wedevelop the SQL server in the DE? Or can they purchase the Standardor Workgroup edition if the workload allows it?2) If the client already has the Enterprise Edition on the Server wewish to use, will they have to buy the license again if we create theDatabase? Can they import our Database to thier SQL Server, and ourapplication connects to thiers?3) Is there other options that would require a cheaper option for ourclients?4) With just the DE, can we develope applications and use theappropriate edition for our clients whatever it is?5) To test our products we thought of using the Trial version tosimulate a multi-user test environment? Since thats just a 180 daytrial, perhaps the Worgroup Edition or the Light weight Edition mightwork. But these editions might not be compatible with our clients'requirements. The DE doesn't seem to handle multi-user environments,so we cant effectively test how the application will perform in the"real world". Any advices here?Our clients range from small bussiness to nation-wide to multi-national companies.Thanks,- GL
View 14 Replies View RelatedI just started looking into writing queries/SPs in C#. A simple SP looks like this.
public class ContactCode
{
[SqlProcedure]
public static void GetContactNames(string lastName)
{
SqlCommand cmd = €¦€¦. €¦€¦
cmd.CommandText = "SELECT * FROM Person.Contact WHERE LastName = " + lastName;
SqlDataReader rdr = cmd.ExecuteReader();
SqlPipe sp = €¦€¦€¦€¦..;
sp.Send(rdr);
}
}
Since it's concatenating a dynamic SQL in the code, is SQL injection a concern or the CLR integration knows to take care of the input sanity? I know good programming practice is to validate input before it gets to this but it's necessary for CLR SPs to have the same robustness as normal T-SQL SPs when it comes to input parameter handling.
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 would like to eliminate all hardcoded login ids and passwords and would like to know what other companies are doing to address this issue. I know a job scheduling software package is the ideal solution, but until dollars become available I need a short-term solution. We've tossed around the idea of using system variables.
Any recommendations?
Thanks, Dave
Hi all,
I have setup a new SQL 2000 SP4 and internal auditor query about revoke permission from Public role and remove guest from all databases.
1. Can I revoke all default permissions (select on system tables in all DBs) from "Public" role? I am concern any error after such action.
2. I found that guest account in DB -- master, tempdb and msdb. According to Microsoft documents. The account should not remove and can't from master and tempdb. How about msdb?
Thanks,
Regards,
Edwin
When attemping to install SQL 2005 Express using the following command line arguments
/qb UPGRADE=SQL_Engine INSTANCENAME=MY_INSTANCE
or
/qb UPGRADE=SQL_Engine INSTANCENAME=MY_INSTANCE SECURITYMODE=SQL SAPWD=MY_PASSWORD
the following error occurs.
Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
The following version and editions have been verified.
1. .NET 2.0 installed
2. Windows XP SP2
3. MSDE 8.00.2039(SP4)
4. all MSDE databases are owned by sa
5. Instance and SQLAgent running under user that is member of Administrators
What are the possible reasons this error is occurring?
We have a server instance on SQL Server 2005 SP2 build 3042. We have a 32 bit x86 server. We attempted to upgrade to SP2 build 3054 KB934458. And we got the following error as stated in the Summary.txt file.
Code Snippet
**********************************************************************************
Product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log
Error Number : 29538
Error Description : MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.
----------------------------------------------------------------------------------
The client tools and workstation components were successfully installed. The server is still reporting build 3042.
Here is the portions of the HotFix.log file.
Code Snippet
05/12/2008 09:19:09.041 Copy Engine: Creating MSP install log file at: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log
05/12/2008 09:19:09.072 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
05/12/2008 09:19:09.103 Registry: Cannot read registry key value "Debug"
05/12/2008 09:21:29.382 MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.
05/12/2008 09:22:33.678 MSP returned 1603: A fatal error occurred during installation.
05/12/2008 09:22:33.724 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
Any help would be appreciated.
Hi,
We are performing a SQL 2005 SP1 upgrade from SQL 2000 on our Windows 2003 SP1 Standard Edition.
When we run the upgrade, we got a error "Upgrade Advisor Return -1" as a pop-up window.
When we run the Upgrade Advisor separately, we get this error :
===================================
Common Language Runtime detected an invalid program.
===================================
Common Language Runtime detected an invalid program. (System.Xml)
------------------------------
Program Location:
at System.Xml.Schema.SchemaInfo..ctor()
at System.Xml.Schema.XmlSchemaSet..ctor(XmlNameTable nameTable)
at System.Xml.XmlReaderSettings.get_Schemas()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReportController.LoadAndValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReport.ValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAIssueReport.Refresh()
at Microsoft.SqlServer.UpgradeAdvisor.ReportPanel.OpenReport(String reportFile)
We are in a lost for solutions. We do hope to hear from anyone asap...
Greatly appreciated for any kind assistance.
Thanks.
Hi,
We are running a test upgrade form sql 2000 standard edition 32 bit to sql 2005 developer edition 32bit. Followed through all the steps and specified the account(SA priveleges and currently used by the 2000 version) and is the local admin on the box for the services and the setup seemed to move fine, except for when it got to the point of installing database services - This is the error message that I got:
MSSQLServer could not be started. Verify you have sufficient priveleges to start system services. The was no choice but to hit cancel and then the set up progressed with a final message of 'SEtup failed'.
Here is a portion of the error log:
Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
Database Mirroring Transport is disabled in the endpoint configuration.
Starting up database 'master'.
Converting database 'master' from version 589 to the current version 611.
Database 'master' running the upgrade step from version 589 to version 590.
Error: 946, Severity: 14, State: 1.
Cannot open database 'master' version 589. Upgrade the database to the latest version.
System Task Aggregate global counters produced an error that was not handled. Major: 9, Minor: 46, Severity:14, State:1
Error: 928, Severity: 20, State: 1.
During upgrade, database raised exception 15151, severity 16, state 1, address 01C4C50B. Use the exception number to determine the cause.
Error: 15151, Severity: 16, State: 1.
Cannot find the endpoint 'TSQL Local Machine', because it does not exist or you do not have permission.
Any help would be greatly appreciated
Thanks KR
Greetings,
I have a client whose DBA upgraded their SQL 2000 database to SQL 2005. They still have it running in 8.0 compatibility mode, for various reasons, and would like me to help them develop a migration strategy to get them to full 9.0 compatibility.
My question is: can I run the Upgrade Advisor on a SQL 2005 engine database running in 8.0 compatibility mode and get anything useful? I already know there will be some issues with existing DTS packages but it would be nice to catch any other issues in advance.
I know that the Upgrade Advisor was run before the upgrade took place but to my knowledge the report was not saved and none of the recommendations were followed.
Any help would be greatly appreciated.
Cheers,
Lon
Help!
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?
View 1 Replies View RelatedI 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?
Thanks.
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 ?
bye
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?
View 3 Replies View RelatedHi 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?
Rob
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.
Regards
Tom
Hello,
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.
Thanks.
Regards,
K.
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?
View 2 Replies View RelatedBasically says chain linkage mismatch
Any way to fix 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?
Thanx in advanced
How I can detach from DB and validate it?
(I mean check if it is detached or not)
Is there any why to return a value from sp_detach_db in C++?
(may a cross posting, please check under developer posts)
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.
What should I do?
Thanks,
Canada DBA
I feel that I have done something stupid here.
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.
Please help - this is driving me crazy.
Thanks
Chris
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.
View 1 Replies View RelatedI 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..
thx..
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)