sqlserver2012数据库备份与还原

小鱼儿 2023-10-05 09:31 10阅读 0赞

一、备份

（1）完整备份

--1：完整备份
    declare @dbname varchar(100)
    declare @sql nvarchar(max)
    set @dbname = 'DataBaseName'
    set @sql = '
    --'+@dbname+'_full 
    BACKUP DATABASE ['+@dbname+'] 
    TO  DISK = ''D:\DBBackup\'+@dbname+'_full.bak''
    WITH NOFORMAT, NOINIT,  NAME = '''+@dbname+'-完整数据库备份'', 
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO'
    print @sql

修改DataBaseName为你自己的数据库名称，运行之后会生成一个sql脚本，运行这个脚本即可。

（2）完整备份还原

--2：完整备份还原
    declare @dbname varchar(100)
    declare @sql nvarchar(max)
    set @dbname = 'DataBaseName'
    set @sql = '
    --RESTORE '+@dbname+'_full
    RESTORE DATABASE ['+@dbname+'] 
    FROM  DISK = ''D:\DBBackup\'+@dbname+'_full.bak''  WITH  FILE = 1,  
    MOVE N''DataBase_Name'' TO N''D:\DataBase\'+@dbname+'.mdf'',  
    MOVE N''DataBase_Name_log'' TO N''D:\DataBase\'+@dbname+'_log.ldf'',  
    NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
    GO'
    print @sql

与备份时一样，DataBaseName(数据库名称)要对应一致，同样也是会生成一个脚本，直接运行这个脚本就可以

（3）差异备份

--3：差异备份
    declare @dbname varchar(100)
    declare @sql nvarchar(max)
    set @dbname = 'DataBaseName'
    set @sql = '
    --'+@dbname+'_diff
    BACKUP DATABASE ['+@dbname+'] 
    TO  DISK = N''D:\DBBackup\'+@dbname+'_diff.bak''
    WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'''+@dbname+'-差异数据库备份'', 
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO
    '
    print @sql

（4）差异备份还原

--4：差异备份还原
    declare @dbname varchar(100)
    declare @sql nvarchar(max)
    set @dbname = 'DataBaseName'
    set @sql = '
    --RESTORE '+@dbname+'_full
    RESTORE DATABASE ['+@dbname+'] 
    FROM  DISK = ''D:\DBBackup\'+@dbname+'_diff.bak''  WITH  FILE = 1,  
    NOUNLOAD,  STATS = 10
    GO'
    print @sql

操作方式与完整备份一样。

二、可能遇到的错误

（1）

![20200903134533730.png][]

问题：逻辑文件名称不对应

解决方法：查找该数据的逻辑文件名，通过下面的语句即可查出，再修改还原语句中的名称即可。

restore filelistonly from disk='F:\sqlserver\shlpgcmCM_full.bak'

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwNjUzNjMx_size_16_color_FFFFFF_t_70][]

参考链接：[https://www.cnblogs.com/gaizai/archive/2011/03/30/2000175.html][https_www.cnblogs.com_gaizai_archive_2011_03_30_2000175.html]

[https://www.cnblogs.com/ggll611928/p/6377545.html][https_www.cnblogs.com_ggll611928_p_6377545.html]

[20200903134533730.png]: https://img-blog.csdnimg.cn/20200903134533730.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwNjUzNjMx_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200903134806290.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwNjUzNjMx,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_gaizai_archive_2011_03_30_2000175.html]: https://www.cnblogs.com/gaizai/archive/2011/03/30/2000175.html
[https_www.cnblogs.com_ggll611928_p_6377545.html]: https://www.cnblogs.com/ggll611928/p/6377545.html