DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @filename VARCHAR(256) -- name of backup file
DECLARE @shellcmd VARCHAR (256) -- shell command script
DECLARE @files TABLE (ID int IDENTITY, FileName varchar(100))
DECLARE @file VARCHAR(256) -- file with full path
DECLARE @dbname VARCHAR(256) -- name of the database
-- set path where backup files are located.
-- make sure to end the value with a backslash
SET @path = 'E:\db-backup\2019w07.2019-02-11.Operation\1.Before\'
-- shell command script
SET @shellcmd = 'dir ' + @path + '*.bak /b'
INSERT INTO @files EXECUTE xp_cmdshell @shellcmd
DECLARE db_cursor CURSOR FOR
SELECT FileName
FROM @files
WHERE FileName IS NOT NULL
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @file = @path + @filename
SET @dbname = LEFT(@filename, CHARINDEX('_', @filename) - 1)
RESTORE DATABASE @dbname
FROM DISK = @file
WITH FILE = 1, UNLOAD, REPLACE, STATS = 10
FETCH NEXT FROM db_cursor INTO @filename
END
CLOSE db_cursor
DEALLOCATE db_cursor
Comments
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎