Tag Info

Hot answers tagged

5

That's exactly why all RDBMS have log files, so you can recover from disasters like these. For Sql Server 2005 start here with a log file recovery process. And here is another resource. The starting snippet from that article: A coworker calls you in a panic because he accidentally deleted some production data, and he wants you to restore the lost ...


2

Try attaching the database with SQL Server Management Studio and see if the database upgrade still fails.


2

If you're working with SQL Express it's a named instance and by default it will install with dynamic ports configured. Check the IP Addresses tab of the TCP/IP properties dialog within SQL Configuration Manager. If you've got a 0 against your NIC or the All option at the bottom it's allocating a dynamic port (this is to make sure it doesn't clash with a ...


2

Try using a VM with a full installation of SQL Server. Restore the content database to that and fix it. Take the fixed and smaller database and restore back to production.


2

For a command line Backup of SQL Server , similar to mysqldump try this : BACKUP DATABASE SampleDatabase TO DISK = 'Z:\SQLServerBackups\SampleDatabase.bak' WITH FORMAT; GO save this in C:\backupMSSQL.SQL and then type this in command line : OSQL.EXE -U sa -P -i C:\backupMSSQL.SQL this should be work like mysqldump for mysql ...


2

You should do a backup and then a restore. These steps can be made by t-sql or by GUI, anyway read the manual about backup and restore.


2

Yes. You will need to install them as Named Instances (say \SQLEXPRESS and \SQL2k8) but they will work just fine.


2

Express only gives you the option of running server side traces for profiling, but I don't think it gives you free access to the SSMS tool? Nonetheless, there is a perfectly good open source version of the profiler tool that should work with your express edition. http://sites.google.com/site/sqlprofiler/


2

The logical filename is an identifier for SQL's internal purposes. It abstracts the actual physical filename so that you can relocate the physical file without having to change a bunch of things inside SQL. To change the logical name of a file do this: ALTER DATABASE <Database name> MODIFY FILE ( NAME = <current_logical_name>, NEWNAME = ...


2

I'm going to add this as an answer even though it's not a fix-all (unfortunately, you need to recover data that's not backed up). SCHEDULE REGULAR BACKUPS SqlExpress doesn't have all the nice features the full version does (including backup plans), however, ExpressMaint is a great solution that you wire-up to your task scheduler via command-line execution. ...


1

Thanks to the posts above to help me narrow the problem down (i don't have the reputation to up-vote them :( ) After a lot of messing with settings etc. I, just out of curiosity, changed the connection string for SQL server on the client machines from referencing the server using its hostname to using its IP. This immediatly solved the problem and ...


1

Could be that the clients attempt to connect first on a protocol that the server is not listening. For instance the clients attempt first net pipes over SMB and when that times out, they attempt TCP (this is the default behavior, btw). If the server is not listening on netpipes or the NetBIOS/SMB infrastructure at the client has issues, then the connections ...


1

Since you're saying that CPU and memory look good, I'd look at the wire and the queries the client is asking the server to perform. On the client I'd be game to give Wireshark a shot and see what the conversation between the client and the server looks like (latency, retransmits, etc). I don't think I'd narrow my conversation to only the client and the ...


1

Error code 0x5 is access denied. For some reason the machine (or the account which is running the SQL Server) is being denied access to the active directory database. Why are you trying to use the native database diagrams? They pretty much suck. Of the possible diagramming tools they are pretty much the worst. Try Visio instead.


1

Remember that you're dealing with SQL Server Management Studio Express, which does not assume you have a copy of the rest of the SQL Server client tools installed. Since this is SQL Server 2005, do a search for Profiler90.exe. That's the executable for Profiler. It is normally installed under C:\Program Files\Microsoft SQL Server\90\Tools\Binn.


1

As with the other question you asked about Profiler, the issue is you're using SQL Server Management Studio Express Edition, which is a pared down version of SQL Server Management Studio. The ability to run reports is available in the full version of SQL Server Management Studio (with SQL Server 2005 I believe you have to apply SP2 to the client tools). So ...


1

1) Delete data If you have 1M records and want to keep only 10K of them I suggest you select last 10K in a temporary table, truncate entire table with TRUNCATE command, then insert 10K records back (you may need to switch off constraints and identity before the operation and switch them on again after it) 2) Make database backup 3) Shrink database file ...


1

perfmon and dmv stats, http://blogs.msdn.com/b/jimmymay/archive/2008/10/30/drum-roll-please-the-debut-of-the-sql-dmv-all-stars-dream-team.aspx


1

Does this database already exist? If so you'll have to refer to it as dbo.Parkisins Or whatever user schema was used to create it.


1

Yes, you can even install SQL 2005 multiple times and multiple 2008 installs on the same computer. Since quite some version SQL Server instances are named (with the exception of the default instance), and the express version has a defined different name.


1

I would first try removing the web part (and solution) using STSADM commands rather than the web interface. If that fails - in the interest of gaining some space back - how about disabling (and therefore emptying) the recycle bin? That might get you enough space back that you can do some clean up work. (This of course means that you lose your entire recycle ...


1

You can't deny anything to 'sa' (or, more correctly, to members of the sysadmin role). Inside the database, you can't deny anything to 'dbo' (or, more correctly, to members of db_owner role). That's just the way things are and there is nothing you can do to circumvent it. If you want to audit a server you must upgrade to SQL 2008 and use the built-in audit ...


1

Yes you can, sql express will install as a named instance. However you can also install up to 16 instances of the full sql server (standard) on the same box. There is no development diference between sql server express and standard. The list of features supported by edition is here. I would probably reccomend sticking with just the full SQL server to cut ...


1

If you want to use a specific port like 1443, you have to set it manually. The default instance will use port 1433 by default, if TCP is enabled and the settings are left unchanged. A named instance will use a dynamic port by default. So the question really boils down to wether the Express instance is named or default (assuming the port number in the OP is ...


1

Follow the step by step guide from Encrypting Connections to SQL Server


1

SQL Server replication is usually very easy to set up, but you can not publish from a SQL Server express database (please see the article below). SQL Server books online. If your central database is close by (network speaking) to the terminals you could just connect directly to the central database using a thin/thick client. Merge replication works well, ...


1

Yes you can do that with SQL Express. You'll want to use SQL Server Replication to do that. You'll need to use a Workgroup or higher server as the middle server that talks to all the other ones. You'll probably want transactional replication or merge replication. Both are near real time.


1

Paul Randal has an excellent blog post on recovering a database by setting it into Emergency Mode and using CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. After you recover the database hopefully you can drop and restore it from the backup correctly. Hope this helps



Only top voted, non community-wiki answers of a minimum length are eligible