SQL Server Version and Internal Database Version Number

Copied from web source.

Checking the sql server installed.

Select @@version

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Compatibility Level

The currently available compatibility levels are:
60 = SQL 6.0
65 = SQL 6.5
70 = SQL 7.0
80 = SQL 2000
90 = SQL 2005
100 = SQL 2008

Internal Database Version

The internal database versions for SQL aren't documented in the Books Online.However, thanks to Paul Randal (@PaulRandal) and his Q&A article in TechNet we have the following list:
SQL Server 7.0 databases have version number 515
SQL Server 2000 databases have version number 539
SQL Server 2005 databases have version number 611/612
SQL Server 2008 databases have version number 655

Database information can be viewed by these means:

1. via dbcc
DBCC TRACEON (3604);
GO
DBCC DBINFO
GO
DBCC TRACEOFF (3604);
or
You can also read the page header by using DBCC PAGE: DBCC TRACEON (3604);
GO
DBCC PAGE (ExampleDB, 1, 9, 3);
GO
DBCC TRACEOFF (3604);

2. viaDATABASEPROPERTY() , DATABASEPROPERTYEXfunction
select DATABASEPROPERTY('db','version')

Backup File Information
Backup file information can be viewed by restore command.

restore labelonly from disk=N'db.bak'
restore headeronly from disk=N'db.bak'
restore filelistonly from disk=N'db.bak'

Restore SQL Server database

I will update this gradually.

Senario 1, restore full database from a backup file.

1. check the file list in the backup file
restore filelistonly from disk=N'backupfile'

Note: checking what're in the backup file
--check the version of sql server that makes the backup, using this command:
restore headeronly from disk=N'backupfile'
--check media information
restore labelonly form ...

2. restore the database with move xx to

restore database ppc_db
from disk=N'xxx.bak'
with move 'xxx_dat' to N'xxx.mdf',
move 'xxx_log' to N'xxx_log.ldf'


MISC:
==view schema and its owners
select * from sys.schemas where name='xxx'

==view objects in a schema
select * from sys.objects o join sys.schemas s on o.schema_id=s.schema_id where s.name='aSchema'

==view database options:
sp_dboption db

==view database users
select * from sys.database_principals where type='S'

==take a look between login and db users
select l.loginname as [login name],u.name as [DB user name]
from sys.database_principals u full join master..syslogins l
on u.sid=l.sid
where u.type ='S' or u.type is null

==view database user's permissions assigned directly

select class_desc,u.name userName,permission_name,OBJECT_NAME(major_id) as objectName
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id=u.principal_id
where u.type='S'
order by u.name,p.class

==generate scripts that grant the permissions according to the permission assigned to db users

select N'grant '+convert(nvarchar,p.permission_name)+case when OBJECT_NAME(major_id)is null then '' else ' on '+convert(nvarchar,OBJECT_NAME(major_id)) end+N' to '+convert(nvarchar,u.name) COLLATE Latin1_General_CI_AS
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id=u.principal_id
where u.type='S'
order by u.name,p.class

==list all the jobs. copied from Internet.

select job.name, case job.enabled when 1 then 'Enabled' else 'Disabled' end [Enabled],
stp.step_name, stp.subsystem, stp.database_name,
ss.name as scheduleName,
case ss.freq_type when 1 then 'One Time'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly - relative'
when 64 then 'When Agent Starts'
when 128 then 'When Computer is idle' else 'Invalid' end Freq_Type,
stp.command
from msdb..sysjobs job left outer join msdb..sysjobsteps stp on (job.job_id = stp.job_id )
left outer join msdb..sysjobschedules sjc on ( job.job_id = sjc.job_id )
left outer join msdb..sysschedules ss on (sjc.schedule_id = ss.schedule_id)