Hi ,
we have a requirement to create a database of our application in client's system. and we assume that already MS Sql server is installed.
we have a .mdf file of our database. Is there any way I can attach .mdf file to the database using command line. so that I can write a batch file and run that batch to create the database in our client's system.
-regards
GRK
How do I reattached a SQL server database if the log file was not backed up and none is available. Our server went belly up and the backups were only copying the *.MDF files. The log files were not being included in the backup for some reason.
I know in version 7.0 you could reattach with out the log file but I am having a more difficult time figuring out how to get around thin in SQL 2000. Anybody got any ideas???
Thanks!!!
Hello
Please can anyone help me
I've been given a .mdf file from sql server 2000 which i need to attach but we do not have the .ldf file
I am unable to recover the old .ldf file
I have tried the attach single file command but obvioulsy this still looks for the old .ldf file path which no longer exists
any help would be mostly appriciated
many thanks
Martin
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies View RelatedHi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Thanks,
Sankar R
Hi,
I have made a backup of a sql 2000 database that was on the server at work and and I would like to use it in sql 2005 express on my home laptop.
Can anyone help? I can't seem to work out how to do it. Do I restore it or re-attach it? Is it possible?
Sorry if these are basic questions.
Mark
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
Thanks,
Ben"
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.
Is this possible to attach a database from a SQL 2005 server to a Server runnning SQL 2000?
I have searched but came up empty.
hi !!
I want to attach database to SQL Server, but I'hv only .mdf file.
I lost .ldf file, how should i attach this DB to server ?
Thanks
SP
Hi,
I m going to attach my databse file to newly installed sqlserver 2000 personel eddition. But when I click the ok button (right click on the database> alltasks> attachdatabase> ). I found the error like
Error 602: Could not find row in sysindexes for database ID 7, Object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Can anyone tell me how can I do this to attach database.
Navi
Dear all,
I recive a error when i attaching a database in sqlserver 2000. The error is :-
An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)
I am trying to attach to the SQL Server 2000 sample Northwind database using SQL Server 2005. When I do the Database-->Attach using the GUI everything seems alright. I can open the Northwind database and see the usual listing of stuff.
However, when I click on the Database Diagrams, I get an error message stating "Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database support objects."
I have tried repeatedly to get the ALTER AUTHORIZATION statement to work, all to no avail. I have restarted my machine, and the properties of the database is now saying that the owner is the same as the other databases I have, but I keep getting the error message.What am I doing wrong here? How can I fix this?
Any help would be greatly appreciated.
Thank you.
Hi guys,
we lost a drive that was holding our log files for a database. I have the .mdf file from the database. So I'm trying to attach the database using just the mdf file but it fails because its still looking for the log file.
How do i work around this?
Bit of a SQL newbie, having taken over a support role for a piece of software that my firm sells. It is basically a DB that runs on MSDE or SQL Server in either desktop or enterprise scenarios.
We do sell our software to clients who may or may not already have SQL on their machines. During installation, it searches for sqlservr.exe for existing SQL installations and if it finds nothing, will then install MSDE 1.0 to house the db files.
My question is this -
I can manually create a db or manually attach an existing db through sql query analyser where there is a full-blown SQL installation. Is there any way I can do the same where there is only MSDE installed? The simple solution, I guess, would be to download one of the freeware or shareware MSDE "Enterprise Managers", but this won't always be suitable for a client installation.
Many thanks for your assistance all.
:cool:
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.
View 10 Replies View RelatedHi there,
I'm trying to attach a DB using a single MDF file. The file was not properly detached as our server had crashed suddenly neither was the a proper backup done..When I try to attach it says
"the file you've specified is not a valid sql server database file" . But another database was restored using a MDF file without any problems ?
Please do get back if anyone may get what the problem likely is ? It'd be of great help. Let me know if my problem was clear or if I've to put it in a different fashion.
Cheers
Ganesh
Attemping to reattach a recovered .mdf and associated .ldf from a crashed disk drive and I am getting the error below. I attempted to create a new db, stop the service, replace the new data files with the recovered db files and restarted the SQL service so that I could run a dbcc checkdb while the db was in suspect mode, however was unable to do this. This error leads me to belive that the log file needs to be rebuild, but that the mdf files could be a decent shape. Any help would be apprecaited. Thanks!
===================================
Attach database failed for Server 'AUTOBASEAutobase'. (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
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files, String owner)
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.Attach()
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SendDataToServer()
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions)
at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files, String owner)
===================================
An error occurred while processing the log for database 'ABSystem7'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Could not open new database 'ABSystem7'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=9004&LinkId=20476
------------------------------
Server Name: .Autobase
Error Number: 9004
Severity: 21
State: 1
Line Number: 1
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
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
View 1 Replies View RelatedI have a VBExpress 2008 application that uses LINQ to SQL to manipulate data in an SQLExpress 2005 database.
I successfully instatiate a DataContext object and examine its metadata through its .Mapping methods and properties. But when I attempt to set the DataSource for a BindingSource control to one of the tables, I get a run-time error.
Specifically:
Dim dc As New DiabetesData(My.Settings.DiabetesData_dbPath)
For Each mt As MetaTable In dc.Mapping.GetTables
...(examine some properties)...
Next
frmEZView.bsPrimary.DataSource = dc.GetTable(Of BGTest)()
generates the error:
System.Data.SqlClient.SqlException was unhandled
Class=14
ErrorCode=-2146232060
LineNumber=65536
Message="An attempt to attach an auto-named database for file SbearMedicalDiabetesDiabetesData2.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
Number=15350
Procedure=""
Server="\.pipe8280AA0D-38A6-41 sqlquery"
Source=".Net SqlClient Data Provider"
State=1
From the Immediate window: ?My.Settings.DiabetesData_dbPath
"SbearMedicalDiabetesDiabetesData2.mdf"
bsPrimary is a BindingSource on a form named frmEZView.
Public Class DiabetesData includes a property Public BGTests As Table(Of BGTest) and a subsequent definition of BGTest.
Can anyone tell me what I'm doing wrong?
Thanks,
--Mable
Hi all,
I have deleted the .ldf file now i am having only the .mdf file. Is it possible to attach the database without shutdown the sql server.
Thanks.
I just upgraded a large SQL 2000 database server to SQL 2005, and I have 2 databases that won't attach to the 2005 Server. They are both very small, about 90MB in size, and when I attach them to 2005, the process alternates between running and spinloop status. It can sit forever, and will never complete. I restored backups before the upgrade to a SQL 2000 Server, and reran DBCC CheckDB on both, and got no consistency errors, Updated Stats and indexes, then detached and moved the data files to 2005, and same thing.
View 3 Replies View RelatedHello,
I've seen threads concerning this problem. I have followed the prescribed steps to fix the problem. I've set the db owner to sa, I've set compatibility to 2005. Still I can't get the diagram to function.
Can anyone out there help me?
Thanks, Bill
I had taken back up on employee from my office. Office server name is UMASHANKAR. My server name is XYZ. I had attached that back up file BY RIGHT CLICKING :: SERVICE OBJECT-->BACKUP DEVICES-->NEW BACK UP DEVICE.Now i create database of EMPLOYEE my server XYZ. I had tried to restore EMPLOYEE back file by right clicking EMPLOYEE database-->TASKS-->RESTORE-->FILE OR FILEGROUP-->To Database: EmployeeSource of Restore:From Device: Employee.bak(I had selected from file by browsing)i had selected recent back up.But server name in back up is UmashankarMy server name is XYZwhen i am clicking ok then i am getting this error.Restore failed for Server 'XYZ'.(Microsoft.SqlServer.Smo)Additional information:|--> An exception occured while executing a Transact-SQL statement or batch. | --> The media set has 2 media families but only 1 are provided. All members must be provided. All members must be provided. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3132)Plz solve this problem. I need it urgently
View 6 Replies View RelatedHi,
can any one help me , i am facing an issue on SSIS mail task, i want to attach a file of size 2 MB. it's getting error :
[Send Mail Task] Error: An error occurred with the following error message: "The operation has timed out.".
thanks,
sun.
Hi All,
I have a dataflow task that creates a text file and posts it in a FTP share.
In need to rename the file attaching datestamp(current date of package execution) at the end of filename.
For ex: My dataflowtask creates a file called 'Samplename.txt'
I need to rename it to : 'Samplename20080225.txt.
I think it can be done using the File task...I see an option to rename the file but not sure of how to configure the task to attach current datestamp.
I see some suggestions online, but complete steps on how to implement the above would be highly appreciated.
Thanks,
How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?
View 3 Replies View Related
Hi Friedns
How can i wil go command line Utility in SSIS Packages any command is there plz help me
Hi,
How does one install MSSQL 2000 on a machine that's already running MSDE 2000? Do i need to unintstall MSDE, or both can be installed seperately??
Is there a way to simply upgrade MSDE into MSSQL 2000?
Thanks!
Hi,
I am trying to run sql scripts from Commandline, i am getting the error "the database doesnot exist use sp_helpdb to show available databases".
I am running this sql query to create database table, appreciate your help.
Thanks
So, I read this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=416220&SiteID=1, which suggested that I could call SQLEXPR32.EXE (the SP2 download) with the relevant commandline args in exactly the same way I could call setup.exe. Namely, if I were to call:
Code Snippetsetup.exe /qb /settings "c:/setup.ini"
then I should be able to use
Code SnippetSQLEXPR32.exe /qb /settings "c:/setup.ini"
where my setup.ini contains
Code Snippet[Options]
ADDLOCAL=SQL_Data_Files,SQL_Replication,Connectivity
INSTANCENAME=AuctionI
SAPWD=auctioni_000
SECURITYMODE=SQL
However, this doesn't seem to be working at all - the commandline params to SQLEXPR32.EXE are completely ignored, and it runs as if they were not specified.
Would be great to get some help on this. I'm equally happy with the set of commands I need to call in order to extract SQLEXPR32 and then run setup, but SQLEXPR32 seems to extract to a random GUID dir, and automatically start setup, neither of which is condusive to automating it.
Thanks,
Dan
SQLCMD works in the Studio if SQLCMD Mode option is chosen.
OSQL and SQLCMD does not work from commandline and produces this constant burnt in message.
Sqlcmd:Error:Microsoft SQL Native Client:Login timeout expired.
Cleint and Server have the same set of protocols
Studio has been added to the list of those permitted by Firewall
Tried to connect through tcp, lpc
Remote has been enabled.
SQLNativeClient does not have a property or method by name 'timeout'.
Let me know if something else is needed?