MS SQL Server

[ Home ]

Negative Process ID in SQL Server

If you run sp_who2 to find out who is locking what process on SQL Server, and you see a negative values for the process ID, you will have to run:

select req_*UAW from syslockinfo where req_spid = -2

to get name to kill. Then, run

kill [name you get above]

and the lock will be cleared.

How to Restore a SQL Server Database from a Backup File

Let us pretend you have been sent a database backup file called mydatabase.bak.

Open a SQL Query Analyzer window.

First, verify that the backup file is not corrupted, by executing this command:

restore verifyonly from disk='C:\downloads\mydatabase.bak'

Next, list the files that are inside this backup file (apparently, backup files are like zip or tar or jar; they contain other files).

restore filelistonly from disk='C:\downloads\mydatabase.bak'

The above command produces a list like this:

mydevelopmentdatabase_Data	D:\Program Files\Microsoft SQL Server\MSSQL\data\mydatabase_data.MDF	D	PRIMARY 3080192	35184372080640	1	0	0 00000000-0000-0000-0000-000000000000	0	0	0	512	1	NULL 109000000080700003 F5793BAE-5730-46C8-9B5A-20BDA28D8746	0	1
mydevelopmentdatabase_Log	D:\Program Files\Microsoft SQL Server\MSSQL\data\mydatabase_Log.LDF	L	NULL	30212096 35184372080640	2	0	0 00000000-0000-0000-0000-000000000000	0	0	0	512	0	NULL 0	00000000-0000-0000-0000-000000000000	0	0

Quite handy, really!

On your system, you would restore (but overwrite! --- note the replace keyword) the database with the following command:

restore database mydatabase from
    disk='C:\downloads\mydatabase.bak'
    with replace, recovery,
    move 'mydevelopmentdatabase_Data' 
    to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase_data.MDF',
    move 'mydevelopmentdatabase_Log' 
    to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase_Log.LDF'

Notice in the command above, with the move clause, we can move the location of either file in the original backup to where those files would be more appropriately kept in our own system.

So, more generically, the command is:

restore database <name of database> from
    disk='<where ever your backup file is>'
    with replace, recovery,
    move '<name of database>_Data' 
    to '<location you want to store DB file>\<name of your database>_Data.MDF',
    move '<name of database>_Log' 
    to '<location you want to store DB file>\<name of your database>mydatabase_Log.LDF'

Fields in an SQL Server table

 select 
 so.name as table_Name,
 sc.name as Field_Name,
 st.name as Field_Type,
 sc.length as Field_Length
 
   from sysobjects so inner join syscolumns sc
 	on so.id = sc.id
 	inner join systypes st on
 	sc.xtype = st.xusertype
 
 where so.xtype = 'U' and so.name in
 ('passprof','passloc')
 order by 1, 2