Hi all, I'm trying to connect to a MSDE database on a remote system. I have no problems connecting locally. I have changed the security to mixed mode, and i have made a NAT entry that forwards port 1433 to the internal server with the MSDE database on it. (just for the record, the server is running five different MSDE instances) I wanted to verify that the instance i need access to is at port 1433, so i tried opening SVRNETCN.exe but it just gives me the following error message "The specified module could not be found" (The title of the alert window is "126") So i went to the registration database trying to confirm it there - however the only related key i could find was:
understand the process of backing up a SQL (MSDE) database. There are several 3rd party tools on the market that include a backup function, but are dependent on first establishing a connection to the server. My specific questions are: 1) is it possible to merely copy (using a DOS or windows copy command) the DB file for backup purposes? If so, is there any trick to restoring the files? 2) what is the advantage (or requirement) of performing the backup through a connection to the server? What is the process for creating such a backup -- does it depend on T-SQL statements? My goal is to create a backup/restore routine within my application so that the process is very easy for my end-users to manage and also something that could be set up on an automated schedule. Appreciate
Can anyone tell me how I can backup / restore individual tables? At the moment I have been backing up the msde database on the development machine and restoring it onto the web server in order to transfer data. But I have now reached the point where the data on the web server no longer matches the data on the development machine. The data on the web server is good but I have some structural changes to some tables on the development machine. Is there a method by which I can update only some of the tables on my web server? To date
trying to connect to remote MSDE database to manage itWhat is the error returned when you try to connect? Are you set up for SQL login or Integrated?The error is the standard "Server does not exist or is currently unavailable, or username / password is incorrect.." none of which are correct local connection works so it is set up for sql loginI have struggled with the same problem for weeks. I am looking for a description of how to configure every detail in the connection string. There are so many levels of user ids, passwords, etc. Exactly where in the computer can I setup every relevant account and permssion, step by step? I have setup new accounts in Windows and the MSDE, but still, nothing works. For me, it seems to be a guessing game. I would have liked
sp_resetstatus, but it it neither gives me an error or a confirmation that the status has been reset. Restarting the sharepoint service does not solve the problem. I'm not familiar with SQL Server, MSDE or the technical admin of sharepoint. The OS is Small Business Server 2003 Premium Edition.hi Dave, resetting the database status usually does not help, as this task is used along with successive tasks to check database integrity, torn pages, and data integrity if you have a valid database backup you should go with that instead of "hacking" the current corrupted one you'll loosing far more less time :)Hi Andrea Thanks for reply. We don't have a decent backup as we are not currently using sharepoint, so sorting this has not been a high
.bak" For the day after tomorrow, "SJDB 09092005.bak" And so on I hope that makes sense! Andrea, once again thank you for your service to the community and sharing your hard work with others. :)hi Tom, :) ok, you have to "edit" the backup statement of the generated job's step assuming you are backing up pubs database to C:\ (replace C:\ with the existing folder you want to backup to), you can provide the file name to include the current date casting ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp ) GETDATE() function result as required.. the final statement will look like: DECLARE @FileName nvarchar(25) SET @FileName = 'C:\' + 'pubs ' + CONVERT
LOG TRUNCATE_ONLY statement (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp).. BOL reports: " Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms. After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE. " if your database has been set to simple recovery model, your problem means that the log file size is to little to accomplish the transaction batch you are dealing with, and you need to expand it, via the ALTER
Disk Directory (in the DB Maintenance Plan Wizard), the drop-down box for specifying the remove files older than is blank, and the default timeframe is 0, and the Backup file extension is blank. On other SQL servers, by default these locations show the drop-down as Week(s), the timeframe as 4, and the extention as BAK. If I continue setting up the maintenance plan, when I get to the Specify Transaction Log Backup Disk Directory, I'll run into the same problem as the Database (blank and 0 for the same fields). In any event it will allow me to setup the plans, however I'd like to be able to set the remove feature and I can't Any ideas?hi, this is a long nightmare with Maintenance Plan withard (which actually is very buggy) please have a look at http://groups
: 1015 Date: 6/14/2005 User: domain\user Computer: servername Description: Failed to connect to server. Error: 0x800401F0 I am part of the system administrators role so should be able to contact the database. I did stop all of the services prior to beginning the install so that I would not have to reboot in order for the upgrade to go through. This is a production machine and I can KewlhandI too am experiencing this issue. I've found that on two systems running XP SP2 that I can connect to the SQL server using SQLDMO, but not using Enterprise Manager over a remote TCP/IP connection. I also am seeing this on both systems' Application Event Logs when MSDE starts: SuperSocket info: ConnectionListen(Shared-Memory (LPC)) : Error 5. and SuperSocket info: (SpnRegister)
fine. Any ideas? Billhi Bill, WMSDE should be managed via SharePoint only, as it is a dedicated version of MSDE modified for it's uses only as regard the exception you get, this is a general MDAC error that can be troubleshooted via http://support.microsoft.com/default.aspx?scid=kb;en-us;328306&Product=sql hints..So you are suggesting that Sequel tools should not be used to manage wmsde? How else can you back the database up? I believe the help file suggests using sequel tools.hi Bill, no, I'm not suggesting you have not to use SQL Server Client Tools, but you have to be carefull AFAIK, as http://www.microsoft.com/resources/documentation/wss/2/all/adminguide/en-us/stsglos.mspx states, you must be running SQL Server 2000 and not WMSDE to back
presented as part of the query analyser. I can see the remote SQL Server instance in the drop down box and am entering my loginID and password. I am assuming that my login ID should be "sa" (default) and my password should match that which I set when I installed the MSDE SQL Server on the remote machine. When I go to connect a dialogue box pops up for about 30 secs informing that the computer is attenmpting connection before , http://support.microsoft.com/default.aspx?kbid=841249I have a similar problem but I have enabled network protocols during MSDE installation, on the remote PC which is Win2000. But now when I try to connect to a database through VS.net server explorer i get the error message "Login failed for user '(null)'. Reason: Not
Need an affordable solution to compare two databases or check the database change released.I not sure what your definition of affordable is but Red Gate has a very good comparison tool. I use it daily and swear by it. http://www.red-gate.com Jim
I TRY TO CONNECT TO MSDE BUT I GOT A FAILED ALTHOUGHT I OPENED 1433 PORT AND I CONFIGURED FIREWALL HOW CAN I SOLVE .I M SURE THAT MSDE IS LISTENING ON TCP 1433 PORT I M USING THIRT PARTY PROGRAM TO CONFIGURE FOR MSDE WHEN I CONNECT LOCAL MSDE DATABASE FROM OUT OF OFFICE THE PROGRAM SAID THAT : DATABASE DOES NOT EXIST OR ACCES DEINED TO LOGIN I AM USING USER:SA PASS NULLI'm having the same problem remotely connecting to a MSDE sql server. Is windows only authentication should be changed to sql server authentication to connect remotely? -Harihi, nope remote connection is available for both authentication types..
. When you stop the server processes the clean-up routines are not performed to the extent that a detach does. The idea being the server can continue where it left off when it comes back on-line. In my experience most backup regimes involve stopping the server processes, backing up all MDF & LDF database files required and then restarting the server processes. This tends to be a faster process than scheduling backups to tape etc and causes the databases to be off-line for the shortest periods. Don't get me wrong, you can do on-line backups as well via Enterprise Manager but many DBA's I know won't trust the scheduling as it can get broken. Some backup software like Veritas can perform on-line backups as well
we have a pllication that uses MSDE databases, we send out updates we detach copy new file in and reattach. Problem: I have a couple of machine the MSDE server want allow the mdb files to attach. WE remove the current log files after the detatch. Almost like the machine does't know the SQL server exists anymore.Hi, Are you facing the issue to attach the database only with MDF? If yes then try to attach the database using below command:- EXEC sp_attach_single_file_db @dbname = 'DBNAME', @physname = 'd:\MSSQL\Data\DBNAME.mdf' Hari SQL Server MVPThe databases are attached when the program starts I get the following errors when the program tries to attach -2147024769 automation error- the specified proceedure not found
Hi, I'm deciding whether to use WMSDE or the full SQL Server for the back-end of WSUS. Can you tell me whether it is possible, in any way, to take a backup of the WMSDE database? It doesn't have to be remote, but must be automated, and able to be performed with the database online. This is just so that we can restore the db quickly from a backup file should it be necessary.Backing up databases in MSDE is easy. It can backup to a disk file. You can either use osql to backup, or any of the miriad of utilities out there. The backup can be automated for any schedule you choose. It can be done while the db is online, but when you restore, all users must be logged out. of backup automated, should
years old, it's limping along without too many problems. The company that wrote the software didn't survive the .com implosion, so I have very little information on the product nor anyone to ask. If his server was to die, he'd be SOL. I want to start 'backing up' the data in a format I can use to import into potential replacement software (I have no way to recreate the existing environment). Is this possible? Without any information on the database (names, passwords, field, layout, etc), is it possible to create a flat file that contains the basic membership data (names, address, status, membership type, etc). (the existing software does not have this capability) I'm assuming that the future software will allow me to import such a file. I this too
SQL data using Microsoft SQL Server Management Studio Express. When I try backing up to the destination I get the error message: "Property backupdirectory is not availabe for Settings 'Microsoft.SQLServer.Management.Smo.Settings' This property may not exist for this object, or may not be retrievable due to inssuficient access rights." Can anyone provide a explaination for this? JamesHi James, for some reasons I do not know, a registry value resolving this setting is sometime not written.. All you have to do is add a string value to the
Hi, I want to back up my database. I found in the books online where it said backups could be done by simply copying the .mdf and .ldf files. Its that enough? I don't have to run sqlmaint to produce the .bak files? and then copy those? Is there going t be a new newsgroup for SQL Server 2005 Express? Brianhi Brian, you can just simply copy your database and log files, but the database must not be in use in order to allow that so consider a "standard" stategy where you perform Transact-SQL BACKUP DATABASE statements, that's to say the "natural" backup option for SQL Server databases.. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp you can perform that via the native sqlcmd.exe or oSql.
I have heard the answer before, but I do not remember and I cannot find it on Microsoft's website. Is the 2 GB limit per database or per server? Can I have multiple databases reach 2 GB on one MSDE server, or is the maximum for all databases on the server 2 GB? Thank you, MarcMarc, The limit is per database. Lars Broberg Elbe-Data AB http://www.elbe-data.se Remove "nothing." when replying to private e-mail!
What can I do if I failed to attach a database by EM? If the database is corrupted, any method to recover the database?hi Tony, if you have a clean backup, this usually is the best solution to go but, if the database can be saved, perhaps the advices by Jasper as per http://tinyurl.com/3pxhv can be helpfullHi, What is the error you are getting while attaching? Best option after a corrruption will be restoring from a good backup. If you do not have the backup try below:- 1. Execute DBCC CHECKDB('db_name','REPAIR_REBUILD') , this will corrupt minor issues. If you do not have any options try with Jaspers suggestion http://www.
I am using the SQL express manager to try to backup a remote database and it tells me the path for the backup is no good, but it will not let me change it, any ideas. Rickhi Rick, try using SQL Server Management Studio Express you can download from http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6053c6f8-82c8-479c-b25b-9aca13141c9e, instead of SQLExpress Manager..I tried that with same results, If I remove the backup device and try to add another one, I get the error Property Backupdirectory is not available for
files. I hope this makes sense.I am running an application (Microsoft Retail Manager) that uses MSDE Server and files are .mdf. My customer's computer died, they had not been backing up since Oct. 08. When they reloaded windows and I reloaded Server, I can no longer access the old database. I saw the following in the newsgroup and wonder if you can help us. I don't think I want something like a self-contained, I just want to recover the mdf. database that was copied from old computer.Hey Richard, I Have a small windows application that use the sqlserver express engine with windows authentication. Can i use the setup project of the Visual Studio 8 to install my application and the sqlserverExpress [as a prerequisit ]? if not, how can make an msi that will first
is placed. -- Create a logical backup device for the full MyNwind backup. USE master EXEC sp_addumpdevice 'disk', 'MyNwind_1', DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat' -- Back up the full MyNwind database. BACKUP DATABASE MyNwind TO MyNwind_1 B. Back up the database and log This example creates both a full database and log backup. The database is backed up to a logical backup device called MyNwind_2, and then the log is backed up to a logical backup device called MyNwindLog1. Note Creating a logical backup device needs to be done only once. -- Create the backup device for the full MyNwind backup. USE master EXEC sp_addumpdevice 'disk', 'MyNwind_2', &
I have msde 2000 installed by one of my applications and every time i access my pc via Remote Desktop, the msde initializes? When I'm at my pc locally, it is not an issue. What is Remote Desktop doing that is causing this behaviour and how can i get it to stop? joHi Joann, If you have installed SP3 on MSDE, the network access is disabled by default (libraries are disabled by default) Windows XP SP2 firewall also blocks any MSSQL services from listening on the network. It is mentioned as DISABLENETWORKPROTOCOLS switch in the readme file. You have to enable net libraries. See these articles: http://support.microsoft.com/default.aspx?scid=kb;en-us;841249 http://support.microsoft.com/default.aspx?scid=kb;en-us;814130 Magy
must be executed by a sysadmin member and you have to resolve both temp media name and position as long as final position on the running file system.. you have later to check for eventual "orphaned users" (but you can even resolve this before backing up the db by cleaning not required database users mapped to standard SQL Server logins.) as described in http://www.sqlservercentral.com/columnists/nboyle/fixingbrokenlogins.asp personally I do not like this way to "install" a database on user's destination server, and I already pointed out "my point of view" :D.. the best method I've read about in public articles is http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/, and is very similar to
I have a small application that I've developed using MSDE (2K). I'm going to put this in to production by setting up a new computer and it seems that Server 2005 Express edition will be adequate for our needs. I want to check that the size of the databases I'm using are less than 4GB. How do I do that? I've looked around in SQL Server Enterprise Manager but I don't see file sizes shown anywhere.In Query Analyzer, try this: EXECUTE sp_spaceused In Enterprise Mangler, right click on the database, choose [Properties]. The file size is in the middle of the page on the [General] tab, Also, you can use Windows Explorer to view the database file. (It's not a precise measure, but it's close.)Thanks, Arnie - I don't know why I couldn't
Hi Everyone, I've got Windows SharePoint Services installed and it uses a database called STS_svr-apps-1_1414639615 I'm trying to back up this database from the QSQL command line tool. However when I try to run the backup, I get the following error: 1> BACKUP DATABASE STS_svr-apps-1_1414639615 TO DISK 'D:\ShareBack\Backup.bak' 2> GO Msg 170, Level 15, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1 Line 1: Incorrect syntax near '-'. 1> Thinking it might be an issue with the name I enclosed it in single quotes: 1> BACKUP DATABASE 'STS_svr-apps-1_1414639615' TO DISK 'D:\ShareBack\Backup.bak' 2> GO Msg 170, Level 15, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1 Line 1: Incorrect syntax near 'STS_svr-apps-1_1414639615'. 1&
. Can anybody explain to me the pros/cons of using one of the other? TIA Jim KHi Jim, This depends upon the type of client in my opinion. A scheduled backup to a file (for example) is great for small sites (followed by an operating system backup of that file) but ONLY if they're going to check that the file date/time keeps changing. If the SQL Agent stops (for example), they'll blissfully keep backing up the same file and not actually have a backup (apart from an old one). Maintenance plans have the same issues. If that's a problem, I'd rather have an option in the program because you are more likely to have those sorts of places carry out a "procedure" than to look for something more subtle. HTH,
a way to install service packs as well 1> You mean the clients which use my app.. right. 2> Which are the dependent dlls' of dmo 3> This gives me an opportunity to post about one other issue, imagine a small office setup with 4 users who want the database on 5th machine. Will I need to install MSDE on all five machines or just on 4 user machines (and just attach on them the database which resides on the 5th machine). devhi dev, yep ; not licensed by redist.txt but available after installation of MDAC2.6 ..\WINDOWS\SYSTEM\odbcbcp.dll; DestDir: WinSys ; sharedfile ; not licensed by redist.txt but available after installation of MDAC2.6 ..\WINDOWS\SYSTEM\sqlwoa.dll ; DestDir: WinSys ; not licensed by redist.txt but available after installation of MDAC2
I've seen conflicting advice: Some say it's the *.MDF file AND the *LDF file combined that equal the 2GB limit, so I have to monitor both and sum them to calculate my database size. Still others say that those files can individually reach 2GB. Which is it? My goal in the long run is automate backing up, deleting records, then compacting the database when it reaches 90%-100% of capacity. Kirkhi Kirk, only datafiles contribute to 2gb database limit that's to say the primary data file (.Mdf) and all secondary datafiles (.Ndf) if present..Andrea's correct, as usual. :-) You can look at the Maximum Capacity Specifications chart within SQL Server 2000 Books Online (downloadable here: http://www.microsoft.com/downloads/details.aspx?FamilyID=
Hi How can i stop msde with a command in prompt? I need to stop this to let my backup software to work.hi, at command prompt level you can execute c:\>NET STOP MSSQLSERVER to stop the default instance, or c:\>NET STOP MSSQL$instance_name to stop a named instance does your "backup" to be intende as just backing up the database/t-log files? this is a poor design, as you can take advantage of native SQL Server backup facilities and eventually double check it "backing up" the "backups" them selves..
Manager or any other utility. Thanks in advance and happy new year!hi, it is not possible to password protect single databases.. as you already probably know, the security architecture of SQL Server is not implemented that way you have a 2 phases authentication mechanism of logging to SQL Server, where it is evaluated wheter or not the specified login (both WinNT and SQL Server) can access the server instance, and then whether or not a valid database user is available in your db for the corresponding login.. if the database user "Guest" is available and no other db user is mapped to the corresponding login, that "Guest" db user will be used and applied, while no db access is permitted otherway but the method is instance centric and not
restore only from SQL Server backups and not from os files as you tried http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_94oj.asp you usually need to first perform a full backup like BACKUP DATABASE cpts TO DISK = 'c:\ \ctps.bak'; you can then select to perform differential BACKUP DATABASE cpts TO DISK = 'c:\ \ctps.dif' WITH DIFFERENTIAL; and/or log backups BACKUP LOG cpts TO DISK = 'c:\ \ctps.trn' WITH NO_TRUNCATE; if you specify the WITH NO_TRUNCATE options you are backing up the tail of the database in case of corruption to get latest transactions without truncating the inactive portion of the transaction log.. (Full or Bulk Logged recovery model must be enabled for the
well) is a "natural" state of life for data and databases and the "case" of your scenario is even wors as SQL Server is not involved at all, shutting down the service ad overwriting the database's files is performed at NTFS level (so adeguate permissions in this area is required) but, again, SQL Server is not involved.. it is later involved when restarting the service and starting the replaced database.. if something goes wrong in the NTFS operation, SQL Server could mark the database as suspect, but this is another story.. on the other hand, SQL Server 2005 introduces DDL triggers, http://msdn.microsoft.com/en-us/library/ms175941.aspx, but, again, the NTFS operation is out of control here as well..Andrea is right. SQL Server connot
Hi, I've enabled the TCP/IP protocol on my MSDE(ver.2000), but I cannot connect the MSDE instance from other machines. Do you know how to solve this problem?hi, in addition to Helmut, did you provide the required exception to the wirewall, if present? for instance WinXP sp2 firewall installs by default disabling all ports required for remote network connections have a look at http://support.microsoft.com/kb/841251/en-us