ITPub博客

首页 > 数据库 > SQL Server > sqlserver2008 全备+还原

sqlserver2008 全备+还原

原创 SQL Server 作者:zuoqi_71 时间:2014-02-27 14:41:08 0 删除 编辑

要配合开发进行新项目上线,过程是

1、开发库的全备

2、开发库导入整合后的数据的全备

3、将第二步备份后导入到生产库


思路清晰2次备份一次恢复,能不能更简单呢,一个字懒呗:)

把备份和恢复写成脚本,三次双击完成上线是不是很爽呀。


sklu_full_backup_skwmsdb.bat

点击(此处)折叠或打开


  1.  
  2. set date=%date:~10,4%%date:~4,2%%date:~7,2%
    set time=%time:~0,2%%time:~3,2%
    if %TIME:~0,2% leq 9 (set time=0%time:~1,1%%time:~2,2%)else set TIME=%time:~0,2%%time:~2,2%
    set logFile=U:\\BACKUP_BAT\\BAKUP_%date%.log

  3. echo \"DATE : \"%date%%time%
    set file_path=U:\\BACKUP_BAT\\%date%
    mkdir %file_path%
    cd %file_path%
    U:

  4. sqlcmd -S XXXX.XXXX.XXXX.XXXX -U sa -P XXXX -i D:\\sqlcmd\\backup\\sklu_full_backup_skwmsdb.sql >> %logFile%


sklu_full_backup_skwmsdb.sql

 

点击(此处)折叠或打开

  1. print \'*******************************************    \'
    print \'                         \'
    print \'sklu sqlserver full backup            \'
    print \'master,lbc,lwms,lwmszone,sklab,tjlube,tjoil     \'
    print \'********************************************    \'

  2. --full backup
  3. declare
    @Database_SK_WMS_DB     nvarchar(256),
    @Path nvarchar(2048)

  4. select
    @Database_SK_WMS_DB     =N\'SK_WMS_DB\',
    @Path            =\'$(file_path)\\\'

  5. declare
    @sql_SK_WMS_DB     nvarchar(max)
    select @sql_SK_WMS_DB     =N\'BACKUP DATABASE \'+@Database_SK_WMS_DB +N\' TO DISK = \'\'\'+@Path+@Database_SK_WMS_DB +N\'_Full_114_\'+REPLACE(REPLACE(REPLACE(convert(nvarchar(30),getdate(),126),\'-\',\'_\'),\':\',\'_\'),\'.\',\'_\')+N\'.bak\'\'         WITH compression,INIT, NAME = N\'\'Full Database Backup\'\', SKIP\'

  6. exec (@sql_SK_WMS_DB     )
  7. go
    exit
sql_restore.sql

RESTORE DATABASE SK_WMS_DB FROM  DISK = N'U:/106_SK_WMS_DB_Full_2014_02_28T04_58_24_660.bak' WITH RECOVERY,  REPLACE
GO



 
BACKUP DATABASE LBC TO DISK = 'U:\BACKUP_BAT\LBC_Full_114_201512152121.bak' WITH compression ,INIT, NAME = 'Full Database Backup', SKIP
 

RESTORE DATABASE LBC FROM  DISK = N'E:\MSSQL_Backup\LBC_Full_114_201512152121.bak' WITH RECOVERY,  REPLACE
GO


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22193071/viewspace-1094191/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-02-26

  • 博文量
    48
  • 访问量
    143811