SQL “How To’s” Part 2

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_MACHINESoftwareMicrosoftMSSQLServerMSSQLServerParameters.

»        
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_MACHINESoftwareMicrosoftMSSQLServerMSSQLServerParameters.

»        
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:mssql7northwnd.mdf’,
‘c:mssql7northwnd.ldf’

b) BACKUP
and RESTORE using WITH MOVE

»        
Backup :-

Backup Database Northwind To Disk =
‘C:mssql7backupnwind.bak’

Go

»        
Restore :-

USE Master

Go

RESTORE Database northwind from DISK
= ‘c:mssql7backupnwind.bak’

WITH MOVE ‘Northwind’ TO
‘c:mssql7Northwnd.mdf’,

MOVE ‘Northwind_log’ TO
‘c:mssql7Northwnd.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:mssql7tempdb.mdf')

ALTER DATABASE Tempdb MODIFY FILE (NAME = Templog, FILENAME = 'c:mssql7templog.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:programfilesMSSQL7dataPubs_Data.MDF’,

@filename2 = N’d:program filesMSSQL7dataPubs_log.ldf’

Any comments or suggestions are welcome at sumitdig@hotmail.com.

»


See All Articles by Columnist Sumit Dhingra