SQL Server Tips

1) How to start SQLServer in Single User Mode

At times you might want to start SQL server in a single user mode to perform some maintenance work on the server or to change server configurations or to recover a damaged database. You can accomplish this in any of the three ways given below :-

a) From Command Prompt :-

» sqlservr -m

b) From Startup Options :-

» Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager.
» Under the 'General' tab, click on 'Startup Parameters'.
» Enter a value of -m in the Parameter.

c) From Registry :-

» Go to HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters.
» Add new string value.
» Specify the 'Name' as SQLArg(n) & 'Data' as -m.
Where n is the argument number in the list of arguments.

Caution: Be careful while editing the Registry. Incorrectly setting up Registry values can cause unpredictable behavior.

2) How to start SQL Server in Minimal Configuration Mode

Sometimes a bad configuration value can prevent SQL Server from starting. Then it won't even let you connect to SQL Server using Enterprise Manager and correct the configuration values. The only option is to start SQL Server in a minimum configuration mode and then correct the configuration values and restart the SQL Server in normal mode. Here's how you can start the SQL Server in a minimal configuration mode :-

a) From Command Prompt :-

» sqlservr -f

b) From Startup Options :-

» Go to SQL Server Properties by right-clicking on the Server name in the Enterprise manager.
» Under the 'General' tab, click on 'Startup Parameters'.
» Enter a value of -f in the Parameter.

c) From Registry :-

» Go to HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters.
» Add new string value.
» Specify the 'Name' as SQLArg(n) & 'Data' as -f.
Where n is the argument number in the list of arguments.

Caution: Be careful while editing the Registry. Incorrectly setting up Registry values can cause unpredictable behavior.

3) How to get Output parameter value from Dynamic execution of a Query

Sometimes you have to execute a Query dynamically using Exec(@Sql). This method works fine as long as you don't want any output values from the @Sql query. But there's another method (sp_Executesql) that allows you to execute queries dynamically as well as get their output values.

The syntax :-

EXECUTE sp_executesql
 @SQLString,
 @ParmDefinition,
 @parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

Example :-

DECLARE @IntVariable INT
DECLARE @SQLString nVARCHAR(500)
DECLARE @ParmDefinition nVARCHAR(500)
DECLARE @Lastlname nVARCHAR(30)

SET @SQLString = 'SELECT @LastlnameOUT = max(lname) FROM
pubs.dbo.employee WHERE job_lvl = @level'

SET @ParmDefinition = '@level tinyint, @LastlnameOUT
varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level
= @IntVariable, @LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname

Note: sp_Executesql is a Extended Stored Procedure.

4) How to move Database/Transaction Log files

You can move Database & Transaction log files to a different location in 2 ways.

a) sp_detach_db & then sp_attach_db

» Make sure that no user is using the database.
» Exec sp_detach_db northwind
» Move the Data & log files to a different location
» EXEC sp_attach_db 'Northwind', 'c:\mssql7\northwnd.mdf', 'c:\mssql7\northwnd.ldf'

b) BACKUP and RESTORE using WITH MOVE

» Backup :-
Backup Database Northwind To Disk = 'C:\mssql7\backup\nwind.bak'
Go

» Restore :-
USE Master
Go

RESTORE Database northwind from DISK = 'c:\mssql7\backup\nwind.bak'
WITH MOVE 'Northwind' TO 'c:\mssql7\Northwnd.mdf',
MOVE 'Northwind_log' TO 'c:\mssql7\Northwnd.ldf'
Go

c) Can be used only for moving Tempdb files.

» Use ALTER Database statement to specify a different Path for the filename.

ALTER DATABASE Tempdb MODIFY FILE (NAME = Tempdev, FILENAME = 'c:\mssql7\tempdb.mdf')

ALTER DATABASE Tempdb MODIFY FILE (NAME = Templog, FILENAME = 'c:\mssql7\templog.ldf')

» Restart SQL Server and delete the old files.

5) How to Rename a Database

a) Using sp_rename

» Make sure that no user is using the database.
» Make the database in the single user mode. You can do this by using sp_dboption.

sp_dboption 'Pubs', 'single user', true

» sp_rename Pubs, Library, Database

Note : For renaming the database, you can also use sp_renamedb.

sp_renamedb Pubs, Library

» Bring back the database in multiuser mode

sp_dboption 'Library', 'single user', false

b) Using Detach & Attach

sp_detach_db @dbname = N'Pubs'
sp_attach_db @dbname = N'Library',
@filename1 = N'd:\programfiles\MSSQL7\data\Pubs_Data.MDF',
@filename2 = N'd:\program files\MSSQL7\data\Pubs_log.ldf'