Sqlserver命令备份恢复数据库

1,备份，创建脚本bak.bat，内容

sqlcmd -i "f:\dbbak\bak.sql"

脚本调用恢复的语句文本

:connect .backup database db1 to disk = 'f:\dbbak\db1.bak' with initgo

说明:init代表覆盖之前的文件，如果noinit代表append to the most recent backup
2，还原，创建还原脚本restore.bat,内容

sqlcmd -i "f:\dbbak\restore.sql"pause

还原sql为

:connect .USE masterGOALTER DATABASE [db1] SET SINGLE_USER WITH ROLLBACK IMMEDIATEGO--查看是否还有用户连接--SELECT * FROM sys.[sysprocesses] WHERE DB_NAME([dbid])='db1'--GOALTER DATABASE [db1] SET MULTI_USERGORESTORE DATABASE [db1] FROM  DISK = N'f:\dbbak\db1.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10go

3，批量生成备份库sql

SELECT'backup database '+ name+' to disk = ''f:\dbbak\'+name+'.bak'' with init'FROM Master..SysDatabaseswhere dbid>=7; -- 注意条件