Hi,
I am trying to do restoration for n databases using automated script.The script is given below. it is not giving errors while job execution ,but restoration is not happening.(no record is in msdb.restorehis tory).
any advice from you. I have doubt on my dynamic sql part.
CREATE procedure test_restore
as
DECLARE @backup_set_id_ Full INT
--DECLARE @Full_Backup_Se t_Date datetime
DECLARE @backup_set_id_ Diff INT
--DECLARE @backup_set_id_ end INT
--DECLARE @Last_Backup_Se t_Date datetime
DECLARE @databaseName sysname
DECLARE @DBName SYSNAME -- stores the database name that is currently being processed
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @filename VARCHAR(200)
SET @fileName = 'E:\test\'+ @dbname + '.bak'
SELECT name
INTO #WhichDatabase
FROM master.dbo.sysd atabases
WHERE name NOT IN ('master', 'model', 'msdb', 'pubs', 'tempdb', 'Northwind')
ORDER BY name
-- Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase
SET @RowCnt = @@ROWCOUNT
-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN
-- Get the ID of the most recent full backup for the database
SELECT @backup_set_id_ Full = MAX(backup_set_ id)
FROM msdb.dbo.backup set
WHERE database_name = @databaseName
AND type = 'D'
-- If no DIFF backup exists, then set the DIFF ID to the full backup ID
IF @backup_set_id_ Diff IS NULL
BEGIN
SET @backup_set_id_ Diff = @backup_set_id_ Full
END
SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '+ @filename
+ mf.physical_dev ice_name + ''' WITH ' + 'FILE = ' + convert(varchar (10), b.position) + ', RECOVERY'
as CommandSet
FROM msdb.dbo.backup set b,
msdb.dbo.backup mediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_ Full
--ORDER BY backup_set_id
DELETE
FROM #WhichDatabase
WHERE name = @DBName
-- Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase
SET @RowCnt = @@ROWCOUNT
-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR DELAY '00:00:05'
END
DROP TABLE #WhichDatabase
I am trying to do restoration for n databases using automated script.The script is given below. it is not giving errors while job execution ,but restoration is not happening.(no record is in msdb.restorehis tory).
any advice from you. I have doubt on my dynamic sql part.
CREATE procedure test_restore
as
DECLARE @backup_set_id_ Full INT
--DECLARE @Full_Backup_Se t_Date datetime
DECLARE @backup_set_id_ Diff INT
--DECLARE @backup_set_id_ end INT
--DECLARE @Last_Backup_Se t_Date datetime
DECLARE @databaseName sysname
DECLARE @DBName SYSNAME -- stores the database name that is currently being processed
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @filename VARCHAR(200)
SET @fileName = 'E:\test\'+ @dbname + '.bak'
SELECT name
INTO #WhichDatabase
FROM master.dbo.sysd atabases
WHERE name NOT IN ('master', 'model', 'msdb', 'pubs', 'tempdb', 'Northwind')
ORDER BY name
-- Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase
SET @RowCnt = @@ROWCOUNT
-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN
-- Get the ID of the most recent full backup for the database
SELECT @backup_set_id_ Full = MAX(backup_set_ id)
FROM msdb.dbo.backup set
WHERE database_name = @databaseName
AND type = 'D'
-- If no DIFF backup exists, then set the DIFF ID to the full backup ID
IF @backup_set_id_ Diff IS NULL
BEGIN
SET @backup_set_id_ Diff = @backup_set_id_ Full
END
SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '+ @filename
+ mf.physical_dev ice_name + ''' WITH ' + 'FILE = ' + convert(varchar (10), b.position) + ', RECOVERY'
as CommandSet
FROM msdb.dbo.backup set b,
msdb.dbo.backup mediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_ Full
--ORDER BY backup_set_id
DELETE
FROM #WhichDatabase
WHERE name = @DBName
-- Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase
SET @RowCnt = @@ROWCOUNT
-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR DELAY '00:00:05'
END
DROP TABLE #WhichDatabase