【www.bbyears.com--Mssql】
例1
代码如下use master
GO
--断开所有连接
ALTER DATABASE [数据库] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER database [数据库] set online
GO
--还原数据库
RESTORE DATABASE [数据库]
FROM DISK = "[bak路径]"
WITH MOVE "[数据库]" TO "[mdf保存路径]",
MOVE "[数据库]_log" TO "[log保存路径]",
STATS = 10, REPLACE--每完成10%输出
GO
例2
sqlserver2005数据库备份文件,在management studio中使用restore总是提示异常
只好尝试用sql脚本进行还原,成功!
RESTORE DATABASE IAC
FROM DISK = "l:IACSystemDb071218.bak"
WITH MOVE "IACSystemDb" TO "D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataIAC.mdf",
MOVE "IACSystemDb_Log" TO "D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataIAC_log.ldf",
STATS = 10, REPLACE
注:move后面的文件名是原有数据库的 data 文件名,以及data日志文件名
如果出现错误可参考
在management studio中使用restore时,生成的脚本如下:
RESTORE DATABASE IAC FILE = N"IAC"
FROM DISK = "l:IACSystemDb071218.bak" WITH FILE = 1,
WITH MOVE "IACSystemDb" TO "D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataIAC.mdf",
MOVE "IACSystemDb_Log" TO "D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataIAC_log.ldf",
STATS = 10, REPLACE
错误提示bak包含了不止一个文件,所以去掉FILE = N"IAC"和WITH FILE = 1
脚本是从网上搜索的一些方法
事务日志是可以基于时间点恢复的,必须在full或bulk_logged模式下
代码如下Alter database [DBName] set recover bulk_logged, then the following operation will not be logged:
*SELECT INTO
*BULK COPY and Bulk Copy Program (BCP)
*CREATE INDEX
*特定文字操作
差异备份的数据文件不和数据备份的文件用一个文件,尽管可以
每一种备份模式下,备份的同时要备份master和msdb数据库
数据备份和清空日志没有关系,但清空日志要发生在事务日志备份之后,在这个之间
模式设置:
代码如下 alter database CACDB_S1000 set recovery bulk_logged数据备份:
代码如下 backup database CACDB_S1000 to disk="E:backupdataCACDB_S1000_200801031245.data"差异备份:
代码如下 backup database CACDB_S1000 to disk=" E:backupdiffCACDB_S1000_200801031245.diff" with DIFFERENTIAL清空日志:
代码如下 DUMP TRANSACTION CACDB_S1000 WITH NO_LOGBACKUP LOG CACDB_S1000 WITH NO_LOG
DBCC SHRINKDATABASE (CACDB_S1000)
事务日志备份:
代码如下 BACKUP LOG CACDB_S1000 to disk = " E:backuplogCACDB_S1000_200801031245.log"还原:
代码如下 RESTORE DATABASE CACDB_S1000 FROM DISK = "E:backupdataCACDB_S1000_200801031245.data" with NORECOVERYRESTORE LOG CACDB_S1000 from disk = " E:backuplogCACDB_S1000_200801031250.log"