sp根据备份文件来创建DB

ALTER PROCEDURE [dbo].[usp_RestoreAllBak] AS BEGIN
-- 从备份文件中取出dbname,然后恢复
-- 这里默认备份文件路径为 d:\sqlbak，需要按实际修改
-- 这里默认备份文件名为 dbname.bak ，需要按实际修改
-- move to
IF OBJECT_ID('tempdb..#tbbak', 'u') IS NOT NULL
DROP TABLE #tbbak
IF OBJECT_ID('tempdb..#dbs', 'u') IS NOT NULL
DROP TABLE #dbs
DECLARE @dbname VARCHAR(40)
DECLARE @sql VARCHAR(MAX)
CREATE TABLE #tbbak ( line VARCHAR(500) )
INSERT #tbbak
EXEC xp_cmdshell 'dir d:\sqlbak'
SELECT REVERSE(SUBSTRING(REVERSE(line), CHARINDEX('.', REVERSE(line)) + 1,
CHARINDEX(' ', REVERSE(line)) - 4)) AS dbname
INTO #dbs
FROM #tbbak
WHERE CHARINDEX('.bak', line) > 0
UPDATE #dbs
SET dbname = REPLACE(dbname, ' ', '')
WHILE EXISTS ( SELECT 1
FROM #dbs
WHERE dbname NOT IN ( SELECT name
FROM master.sys.databases ) )
BEGIN
SET @sql = ' restore database '
SELECT TOP 1
@sql = @sql + ' ' + dbname +' from disk=''d:\sqlbak\'+dbname+'.bak'' with password=''123456'''
FROM #dbs
WHERE dbname NOT IN ( SELECT name
FROM master.sys.databases )
PRINT @sql
EXEC ( @sql )
END
END