Disaster Plan

1. Check database and log file of (Master, HowdyDatabase, Btell, BlackPebble, OCE), to use sp_attach_db.

- exec sp_attach_db @dbname = N’databasename’

@filename1 = N ‘c:backups databasename.mdf’

@filename1 = N ‘c:backups databasename_log.ldf’

- exec sp_detach_db databasename

- sp_attach_single_file_db, to find out the last checkpoint. If cann’t, go to next step

[@more@]

2. If there is no .ldf, create a same name database, then stop sql server, use the .mdf file to cover the name database file, then rebuild log file, set emergency mode (sysdatabases status = 32768), but need to remember the original sysdatabases status value:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
Use databasename
Go

update sysdatabases set status = 32768 where name = ''

DBCC with REPAIR_ALLOW_DATA_LOSS option
sp_dboption '', 'single user', 'true'
DBCC CHECKDB('')

- Is the result of DBCC is fine then change database status back:

update sysdatabases set status = 28 where name = ''

- Or use sp_resetstatus
sp_resetstatus “db_name”
go
sp_configure 'allow updates', 0 reconfigure with override
Go

- Use DBCC CHECKTABLE to check each table, all the table can be found in sysobjects, also check OBJECTPROPERTY is IsTable

If still got error, use select into to make a new table to check again

- Rebuild all the view, index, trigger and stored procedure with DBCC DBREINDEX

3. If cann’t, find database backup (Full backup + Diff after full backup+ Log after full backup).

-check database recovery type: Select databasepropertyex (‘databasename’, ‘recovery’)

-set databse backup type

Use databasename

Alter database databasename

Set recovery simple/full;

4. Backup database (2 ways)

Exec sp_addumpdevice ‘disk’, ‘devicename’, ‘c:backups databasename.bak’;

Use databasename
Backup database databasename To devicename

Or

Use databasename
Backup database databasename To disk=‘c:backups databasename.bak’

-If want to cover the excited backup file

Use databasename
Backup database databasename To disk=‘c:backups databasename.bak’
With INIT; (if don’t want to cover then use With NOINIT)

-diff backup

Use databasename
Backup database databasename To disk=‘c:backups databasename.bak’
With INIT, Differential;

CHECK BACKUP INFO

Use msdb
Select backup_start_date, type, physical_device_name, backup_set_id from backupset bs inner join backupmediafamily bm
ON bs.media_set_id = bm.media_set_id
WHERE database_name = ‘databsename’
ORDER BY backup_start_date desc (*Where I = Diff backup, D = full backup)

- find what did the restore effected

Select filegroup_name, logical_name, physical_name from msdb..backupfile
Where backup_set_id = the number you want

OR

- find what did the restore effected to CHECK RESTORE INFO

Restore HEADERONLY from disk = ‘c:backups databasename.bak’

Restore FILELISTONLY from disk = ‘c:backups databasename.bak’

-If the db file doesn’t work, we still can backup those actions after last db back, below:

5. RESTORE FROM LOG

Backup LOG databasename To disk = ‘c:backups databasename_log.bak’
With INIT, NO_TRUNCATE;
(*must use NO_TRUNCATE to back all actions actions after last db back)

- To restore db from full/diff backup

Restore dababase databasename From disk = ‘c:backups databasename.bak’
With replace, norecovery;
Restore LOG databasename From disk = ‘c:backups databasename_log1.bak’
With norecovery;
Restore LOG databasename From disk = ‘c:backups databasename_log.bak’;

6. Restore system database:

- When system database (master, model, msdb) went to down,

1. Go to SQL server confituration manager to stop all services

2. Cmd à go to MSSQLbinn>sqlserver –m --into single user mode

3. Another Cmd à go to MSSQLbinn>sqlcmd –e

1> Restore dababase master
2>From disk = ‘c:backups master.bak’
3>go

4. Go to SQL server confituration manager to ONLY restart
MSSQLSERVER service

5. Open SQL server management studio to restore model and msdb db

6. Restore dababase msdb From disk = ‘c:backups msdb.bak’;
Restore dababase model From disk = ‘c:backups model.bak’ With replace;

7. Go to SQL server confituration manager to restart all other services

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1697933/viewspace-887839/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1697933/viewspace-887839/