sql server数据库显示备用只读 sql server数据库备份语句

--创建备份设备
USE [master]
GO
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'beifen', @physicalname = N'D:\beifen.bak'
GO
--完整备份到备份设备中
BACKUP DATABASE [student] TO [beifen] WITH NOFORMAT, NOINIT, NAME = N'student-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--还原完整备份
RESTORE DATABASE [student] FROM [beifen] WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
--差异备份
BACKUP DATABASE [student] TO [beifen] WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'student-差异 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--事务日志备份
BACKUP LOG [student] TO [beifen] WITH NOFORMAT, NOINIT, NAME = N'student-事务日志 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--还原到最近状态
RESTORE DATABASE [student] FROM [beifen] WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [student] FROM [beifen] WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [student] FROM [beifen] WITH FILE = 3, NOUNLOAD, STATS = 10

GO

====================================================================================================================================

备份:
BACKUP DATABASE "mydb" TO DISK ='C:\mybak.db' with init
还原
use master
RESTORE DATABASE "mydb" FROM DISK='C:\mybak.db';
use mydb
[补充]
这两个命令都得指定一个文件名字啊。
你看我的例子都是'C:\mybak.db。
你指定了一个目录肯定不行的啊,还得要指定一个文件名字才成。
这两个是sqlserver的自定义的存储过程,其实也能手工在sqlserver的企业管理器中看到,, 选中库,右键--所有任务--备份还原数据库那。

============================================================================

--------------------完整备份默认追加到现有的文件---------------
backup 
database
NorthWind
To
disk=
'd:\backup\NorthWindCS-Full-2010-11-23.bak'
--------完整备份，覆盖现有的文件
Backup 
database
NorthWind
To
disk=
'd:\backup\NorthWindCS-Full-2010-11-23.bak'
With
init
---覆盖现有文件代码
--------差异备份（上次一完整备份以来改变的数据页）
backup 
database
NorthWind
To
Disk=
'd:\backup\NorthWindCS-Full-2010-11-23.bak'
-----事物日志备份，会自动截断日志(默认会阶段日志)
backup log NorthWind
To
Disk=
'd:\backup\NorthWindCS-log-2010-11-23'
-----事物日志备份，不截断日志(默认会阶段日志)
backup log NorthWind
To
Disk=
'd:\backup\NorthWindCS-log-2010-11-23'
With
No_Truncate
-----不备份直接阶段日志，在SQL SERVER2008中不再支持。
backup log NorthWind 
With
No_Log
backup log NorthWind 
With
Tuancate_only
-----SQL SERVER 2008 替代的截断日志方法
alter
database
NorthWind 
set
Recovery Simple
exec
sp_helpdb NorthWInd
use NorthWind 
dbcc shrinkfile(
'NorthWind_log'
)
alter
database
NorthWind 
set
Recovery 
Full
----超大型数据库的文件和文件组备份
Exec
sp_helpdb NorthWind
backup 
database
NorthWind File=
'NorthWind_Current'
to
disk=
'h:\backup\NorthwindCS_Full_2010031.bak'
backup 
database
NorthWind FileGroup=
'Current'
to
disk=
'h:\backup\NorthwindCS_FG_2010031.bak'
---仅复制备份，不影响现有的备份序列
backup 
database
NorthWind
To
disk=
'h:\backup\NorthwindCS_Full_2010031.bak'
With
Copy_only
--尾部日志备份,备份完成后数据库不再提供访问
use master
go
backup log NorthWind
to
disk=
'h:\backup\Northwind-taillog-20101031.bak'
With
NoRecovery
--回复数据库提供访问
Restore databse NorthWind 
with
Recovery
--分割备份到多个目标文件
backup 
database
NorthWind 
to
disk=
'h:\backup\Northwind-part1.bak'
,
disk=
'h:\backup\NorthwindCS-part2.bak'
--镜像备份，需要加入With Format
backup 
database
NorthWind
to
disk=
'h:\backup\NorthwindCS-Mirror1.bak'
Mirror 
to
disk=
'h:\backup\NorthwindCS-Mirror2.bak'
----Mirror镜像
With
Format
--备份到远程服务器
--使用SQL SERVER 的服务启动账号访问远程共享可写文件夹
backup 
database
Northwind
to
disk=
'\\192.168.3.20\backup\nw-yourname.bak'
--备份到远程服务器,指定访问远程服务器的账号和密码
Exec
sp_configure
Exec
Sp_COnfigure 
'show advanced options'
,1
Reconfigure 
with
Overrid
Exec
sp_configure 
'xp_cmdshell'
,1
Reconfigure 
with
override
Exec
xp_cmdshell
'net use \\192.168.10.101'
/
user
:administrator 
password
'
backup database Northwind 
to disk='
\\192.168.10.101\backup\nw-fy.bak
'
Exec sp_configure '
xp_cmdshell
',0
Reconfigure with override
--------------------------------------
--备份压缩
--------------------------------------
Backup Database AdventureWorks
To disk='
h:\backup\adv不压缩备份.bak
'
--132MB  花费 7.789 秒(16.877 MB/秒)。
--备份到NTFS目录
Backup Database AdventureWorks
To disk='
H:\backup\test\advNTFS压缩备份.bak
'
--60MB     花费 11.871 秒(11.073 MB/秒)。
Backup Database AdventureWorks
To disk='
h:\backup\adv压缩备份.bak
'
With Compression
--132MB  花费 7.789 秒(16.877 MB/秒)。
--34MB    花费 3.775 秒(34.820 MB/秒)。
--启动默认备份压缩
EXEC sp_configure '
backup compression 
default
', '
1'
RECONFIGURE 
WITH
OVERRIDE
GO