--1.备份数据库
backup database DataBase1
to disk = 'd:\DataBase1.bak'
with format
go
--2.查询逻辑名称
select name,physical_name
from sys.master_files
where name = 'DataBase1'
go
--3.还原
restore database DataBase_test
from disk = 'd:\DataBase1.bak'
with
move '逻辑文件' to 'd:\DataBase_test.mdf', --数据的逻辑名称->新的物理路径
move '逻辑日志文件' to 'd:\DataBase_test_log.log' --日志的逻辑名称->新的物理路径
backup database DataBase1
to disk = 'd:\DataBase1.bak'
with format
go
--2.查询逻辑名称
select name,physical_name
from sys.master_files
where name = 'DataBase1'
go
--3.还原
restore database DataBase_test
from disk = 'd:\DataBase1.bak'
with
move '逻辑文件' to 'd:\DataBase_test.mdf', --数据的逻辑名称->新的物理路径
move '逻辑日志文件' to 'd:\DataBase_test_log.log' --日志的逻辑名称->新的物理路径
--1.备份数据库
backup database DataBase1
to disk = 'd:\DataBase1.bak'
with format
go
--2.查询逻辑名称,比如返回了:
-- xxx 数据文件的逻辑名称
-- xxx_log 日志文件的逻辑名称
select name,physical_name
from sys.master_files
where name = 'DataBase1'
go
--3.还原
restore database DataBase_test
from disk = 'd:\DataBase1.bak'
with
move 'xxx' to 'd:\DataBase_test.mdf', --数据的逻辑名称->新的物理路径
move 'xxx_log' to 'd:\DataBase_test_log.log' --日志的逻辑名称->新的物理路径
--> 全界面操作,按提示设定复制数据的相关选项.. --> 下一步.. --> 完成.
ALTER PROC [dbo].[up_Backup]
@BakPath NVARCHAR(500),--必需是加上"\"的目录地址,e.g.C:\BackupFile\
@DB NVARCHAR(100),--需要备份的数据库名称
@BackupType SMALLINT=1 --备份的类型:1 全备,2 差备,3 日志备份
AS
DECLARE @BakSQL NVARCHAR(4000);
IF @BackupType=1
BEGIN
SET @BakSQL=N'BACKUP DATABASE '+@DB +N' TO DISK = '''+@BakPath+@DB+N'_'+replace(replace(replace(replace(CONVERT(char(16), getdate(), 120 ),'-',''),'',''),':',''),' ','')+N'_FULL.bak'' ';
SET @BakSQL=@BakSQL+' WITH NOFORMAT, NOINIT, NAME = '''+@DB+N' Full Backup'''+N',SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10';
END
ELSE IF @BackupType=2
BEGIN
SET @BakSQL=N'BACKUP DATABASE '+@DB +N' TO DISK = '''+@BakPath+@DB+N'_'+replace(replace(replace(replace(CONVERT(char(16), getdate(), 120 ),'-',''),'',''),':',''),' ','')+N'_DIFF.bak'' ';
SET @BakSQL=@BakSQL+' WITH DIFFERENTIAL ,NOFORMAT, NOINIT, NAME = '''+@DB+N' Differential Backup'''+N',SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10';
END
ELSE IF @BackupType=3
BEGIN
SET @BakSQL=N'BACKUP LOG '+@DB +N' TO DISK = '''+@BakPath+@DB+N'_'+replace(replace(replace(replace(CONVERT(char(16), getdate(), 120 ),'-',''),'',''),':',''),' ','')+N'_LOG.trn'' ';
SET @BakSQL=@BakSQL+' WITH NOFORMAT, NOINIT, NAME = '''+@DB+N' Transaction Backup'''+N',SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10';
END
EXEC (@BakSQL)
第二步:红框部分选一个路径和文件名,带上.bak后缀名,点确定即可备份,还原就是反操作而已