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)

Mysql transaction log restoration

almost all the documents you can find on the Net are telling you to use mysqlbinlog to read the binary logs and pipe the output to mysql client, but this method simply will fail on windows system if the database contains binary data such as blob and binvarchar, due to escaping and piping problem in mysqlbinlog utility. the mysql utility simply can not read the file or output stream created by mysqlbinlog.

here's a workaround though, we can tell mysql to use --execute "source xx" option to parse the log files from mysqlbinlog.

E.g.
C:\> mysqlbinlog binary_log_file --result-file=/tmp/bin.sql
C:\> mysql --user=root --execute "source /tmp/bin.sql"

ref 1: http://bugs.mysql.com/bug.php?id=33048
ref 2: http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/windows-vs-unix.html

Backup and Restore MySQL Database

1. Configure mysql to generate binlog.
example:
in my.ini, define these two lines
log-bin=c:\mysql_blog\mysqlblog
max_binlog_size=10m

2.make scheduled task that runs script to make full backup specified database.
the command used can be mysqldump

3. make scheduled task that runs script to make back up of binlog files. an easy way to back up only the newly created bin log files is to remove the binlog files have been backed up.

the backup scripts can rotate after a certain period.

upon the time to restore the database from disaster such as hard disk failure, the following steps can be followed.

1. restore the database from recent full database backup
mysql -u -p db 2. restore all the bin logs since the recent full database backup
mysqlbinlog binlogfiles binlogfile2 ... >onebig file
mysql -u -p db <onebig file

IO redirection can also be used here