SQL Server General DBA
(Note: Please replace the - [hyphen] with an _ [underscore] wherever xp-cmdshell is mentioned in this FAQ.) As part of Microsoft’s desire to increase the out-of-the-box security of SQL Server 2005, it has turned off the xp-cmdshell extended stored procedure by default. If you try to use xp-cmdshell without manually enabling it, you will get an error message […]
Is it possible to set the database name in advance when deploying client database with an installer option?
Question: We have a requirement from our clients to deploy the database and set the database name in advance when installating the SQL Express or MSDE with the auto-install option. Answer: It is possible to create the database dynamically using SP_EXECUTESQL to build the SQL statements during runtime. You can then use EXECUTE to run […]
Question: How do I get the taskpad view in SQL Server Management Studio as it was in SQL Server 2000 version in order to see database data & log files usage? Answer: SQL Server 2000 Enterprise Manager has a feature to display database data & transaction log file usage in a taskpad view when a […]
SQL Server 2005 has many key new features including a production-ready version of database mirroring from Service Pack1 installation, in which the primary production server is mirrored at all times by a standby server. “This allows for automated, seamless failover between primary and standby server, if the primary server needs to come down”. SP1 also includes […]
When a user attempts to execute a DTS package that has ‘spaces’ this will fail with the error “Cannot find specified package in the storage location specified”. The text of the error looks like a problem with the path, which is partially correct. Also, the DBA must check the privileges for SQLAgent login context if […]
Perfmon/Sysmon utility is a handy utility for Database Administrators to invoke for data to be collected on system resources usage. The logging function of this tool is useful for logging details that can be used for further analysis from the trace. The SQL Server Develoment team has made a major contribution to combining this facility with […]
Question There’s a debate going on within our company about whether or not we should automatically reboot our SQL Servers on a weekly basis. We are not experiencing any current problems, but some people are saying that by regularly rebooting, we will prevent future issues. Will doing so help performance or reliability of SQL Server? […]
How do you get rid of users that are already in the database on the backup server when a restore starts?
QuestionI’m trying to implement log shipping as described in your log shipping article, with a few slight modifications. The question I have is how do you get rid of users that are already in the database on the backup server when a restore starts? If someone is using the read-only capabilities when the restore starts, […]
Can you provide the scripts that you use to transfer (copy) logins from a primary server to a secondary server?
Here’s the script I use to move logins, along with the script I use to resynch login IDs and database user IDs after moving the logins. I did not write either one, but I have made some modifications to both of them for my own purposes. I would give credit to the original authors of […]
No, at least not using sp_detach_db, sp_attached_db; or backup and restore. But you can recreate the database schema in a database on a different version of the database and then use DTS or SSIS to move the data.