February 14, 2002 at 4:15 am
Hi everybody,
Can someone help writing a script for restoring a database and his tables.
Backups go into d:\mssql\backup\subfolderx.
I must run in a scheduler.
It's handy to fill in allready the databasename and path?
And is there a good script resource somewhere?
Thanx.
Casper.
February 14, 2002 at 5:04 am
We have a script library posted here on the site. Have you tried writing the script yet? Post what you have so far!
Andy
February 14, 2002 at 6:11 am
Andy I found this from Chris Kempster at SQLservercentral.
CREATE procedure GenRestoreDatabaseScript_sp @p_dbname varchar(50), @p_datetime datetime, @p_usefullbackupend datetime, @p_stats varchar(20) = 10, @p_stopat datetime = null as
SET NOCOUNT ON
--
-- Name: GenRestoreDatabaseScript_sp
-- By: Chris Kempster, Jan 2002
-- Version: 1.0
--
-- Parameters
-- @p_dbnameDatabase to generate restore scripts for
--@p_datetimeStart time to search for last FULL backup
--@p_usefullbackupendEnd time to search for last FULL backup
--@p_statsdisplay STATS (default 10%)
--@p_stopatFor Point In Time (PTR) recovery, date which to recover to
--
-- Current Restrictions
--1) doesnt factor in more than 1 device, only works of FROM DISK=
--2) doesnt work with the TAPE option
--3) doesnt restore specific FILE or FILEGROUPS
--4) doesnt restore to a specific MARK, only STOPAT
--5) not tested for replicated or clustered databases
-- How it works
--1) locates a FULL backup between @p_datetime and @p_usefullbackupend
--2) locates any differentials and log backups associated with this FULL (by locating the next FULL backup after the one it found and using anything between these for the DB)
--3) supports multiple appended backups to a single backup device (file)
--4) will script the FULL backup, then the last DIFFERENTIAL then all LOGs after this differential, finally doing a WITH RECOVERY for the last LOG or full/differential (whater the last backup type was)
-- Examples
--
--Generates script starting from the last FULL backup between the two dates below using a stat recalc of 40 for DB tmp
--exec GenRestoreDatabaseScript_sp 'tmpdb', 'Jan 3, 2002 9:00 AM', 'Jan 3, 2002 10:00 PM', 40
--
--Generates script to restore DB to 'Jan 3, 2002 11:45 AM' using available log files
--exec GenRestoreDatabaseScript_sp 'tmpdb', 'Jan 3, 2002 9:10 AM', 'Jan 3, 2002 9:20 AM', 40, 'Jan 3, 2002 11:45 AM'
DECLARE @v_lastfullbackupINTEGER
DECLARE @v_nextdayfullbackup INTEGER
DECLARE @v_lastdiffbackup INTEGER
DECLARE @v_lastlogbackup INTEGER
DECLARE @v_logfilestopat VARCHAR(100)
DECLARE @v_restoreoptions_all_db VARCHAR(150)
DECLARE @v_errorVARCHAR(150)
-- Locate last backup details for the database
-- the @p_datetime defines the FULL backup file to be restored from (last file is always used for the particular date)
set @v_lastfullbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'D')
set @v_nextdayfullbackup = (select min(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_usefullbackupend and BKS.type = 'D')
-- ensures restore doesnt use files assoc with next full backup (if any)
if @v_nextdayfullbackup is null or @v_nextdayfullbackup = ''
set @v_nextdayfullbackup = 99999999
set @v_lastdiffbackup = (select max(BKS.backup_set_id)from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'I' and BKS.backup_set_id < @v_nextdayfullbackup)
set @v_lastlogbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_start_date >= @p_datetime and BKS.backup_start_date <= @p_usefullbackupend and BKS.type = 'L' and BKS.backup_set_id < @v_nextdayfullbackup)
-- Set restore options
-- EDIT THIS SINGLE LINE BELOW TO ADD YOUR OWN OPTIONS FOR ALL RESTORES
if @p_stats is not null and isnumeric(@p_stats) = 1 and @p_stats between 1 and 100
set @v_restoreoptions_all_db = ', STATS = ' + cast(@p_stats as varchar)
else begin
raiserror('Invalid STATS parameter. Numeric values from 1 to 100 only.', 16,1)
return 1
end
if @p_stopat is not null begin
set @v_logfilestopat = ', STOPAT = ''' + cast(@p_stopat as varchar) + ''''
-- set max log file to where we need to stop
set @v_lastlogbackup = (select max(BKS.backup_set_id) from msdb.dbo.backupset BKS where BKS.database_name = @p_dbname and BKS.backup_set_id > @v_lastfullbackup and BKS.backup_set_id < @v_nextdayfullbackup and BKS.type = 'L' and @p_stopat between backup_start_date and backup_finish_date)
if @v_lastlogbackup is null or @v_lastlogbackup = '' begin
raiserror('Invalid STOP AT date. No log files exist or date does not fit in specific log backup range.', 16,1)
return 1
end
end
-- ##########################################################
-- Generated script of recover commands for a given DB name and date
-- Step 1. FULL database recovery
-- ##########################################################
print '-- Recover last full database backup'
select
'RESTORE DATABASE [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
case when
(select count(*)
from msdb.dbo.backupset BKS2
where BKS.database_name = BKS2.database_name
and BKS2.backup_start_date > BKS.backup_start_date
andBKS2.type in ('L', 'I')) >= 1
then
', NORECOVERY'
else
', RECOVERY'
end
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id = @v_lastfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'D' -- full backup
IF @@rowcount <= 0 BEGIN
set @v_error = 'ERROR - Could not find any full backups made during the day specified - ' + CAST(@p_datetime AS VARCHAR)
raiserror(@v_error, 16,1)
return 1
END
-- ##########################################################
-- Step 2. DIFFERENTIAL database recovery
-- ##########################################################
print '-- Recover last differential'
select
'RESTORE DATABASE [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
case when
(select count(*)
from msdb.dbo.backupset BKS2
where BKS.database_name = BKS2.database_name
and BKS2.backup_start_date > BKS.backup_start_date
andBKS2.type in ('L')) >= 1
then
', NORECOVERY'
else
', RECOVERY'
end
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id = @v_lastdiffbackup
andBKS.backup_set_id > @v_lastfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'I' -- differential backup
-- ##########################################################
-- Step 3. LOG file recovery (not including last log)
-- ##########################################################
print '-- Recover log files (not including last log)'
-- if there is a last diff backup, then gets logs after this backup, otherwise logs only after the full
IF @v_lastdiffbackup is not null
select
'RESTORE LOG [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
', NORECOVERY'
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id < @v_lastlogbackup
andBKS.backup_set_id > @v_lastdiffbackup
and BKS.backup_set_id < @v_nextdayfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
ELSE
select
'RESTORE LOG [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
', NORECOVERY'
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF
where
BKS.backup_set_id < @v_lastlogbackup
andBKS.backup_set_id > @v_lastfullbackup
and BKS.backup_set_id < @v_nextdayfullbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
-- ##########################################################
-- Step 4. LOG file recovery (last file)
-- ##########################################################
print '-- Recover last log file'
select
'RESTORE LOG [' + BKS.database_name + '] ' +
'FROM DISK = ''' + BMF.physical_device_name + ''' ' +
'WITH FILE = ' + cast(position as varchar) +
+ @v_restoreoptions_all_db +
case when @v_logfilestopat is null then
', RECOVERY'
else
@v_logfilestopat +
', RECOVERY'
end
from
msdb.dbo.backupset BKS, msdb.dbo.backupmediafamily BMF--, msdb.dbo.backupfile BF
where
BKS.backup_set_id = @v_lastlogbackup
andBKS.media_set_id = BMF.media_set_id
andBKS.database_name = @p_dbname
andBKS.backup_start_date >= @p_datetime
andBKS.type = 'L' -- log
GO
But I must be changed I think.
Thanx andy.
quote:
We have a script library posted here on the site. Have you tried writing the script yet? Post what you have so far!Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply