sqlserver2008 全备+还原

来源：这里教程网时间：2026-03-02 10:32:18 作者：

要配合开发进行新项目上线，过程是 1、开发库的全备 2、开发库导入整合后的数据的全备 3、将第二步备份后导入到生产库
思路清晰2次备份一次恢复，能不能更简单呢，一个字懒呗：）把备份和恢复写成脚本，三次双击完成上线是不是很爽呀。

sklu_full_backup_skwmsdb.bat

点击(此处)折叠或打开

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

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

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

点击(此处)折叠或打开

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

--full backup

declare
@Database_SK_WMS_DB nvarchar(256),
@Path nvarchar(2048)

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

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\'

exec (@sql_SK_WMS_DB )

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