- 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@]- 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 '
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
If cann’t, find database backup (Full backup + Diff after full backup+ Log after full backup).
-set databse backup type
Use databasename
Alter database databasename
Set recovery simple/full;
- 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:
- 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’;
- 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