Restoring Database using Scripts

I have and MSDE environment and I use "OSQL" to administer the database. I've written a backup script and restore script. My problem/question as to do with the restore. Both scripts run just fine.
However, I want my restore to use the lastest and greatest backup set within the backup device. I never really know what the lastest backup set is most of the time. But when ever my restore process runs I want it to use the lastest backup within the back
up device. How do I specify in my script to use the lastest backup set when a restore is done?
Thanks
Hi,
If you are going to restore in the same server see the MSDB..BACKUPSET table
for position. See the below query.
select database_name,backup_finish_date,position from msdb..backupset where
database_name='msdb'
If you are doing it in a different machne use the below command.
restore headeronly from disk='c:\msdb.bak'
You can restore the latest backup based on position
Thanks
Hari
MCDBA
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
I've written a backup script and restore script. My problem/question as to
do with the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the backup device. I never really know what the lastest backup set
is most of the time. But when ever my restore process runs I want it to use
the lastest backup within the backup device. How do I specify in my script
to use the lastest backup set when a restore is done?
> Thanks
|||hi Larry,
"Larry Bird" <LarryBird@.discussions.microsoft.com> ha scritto nel messaggio
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
>I've written a backup script and restore script. My problem/question as to
do with
>the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the
> backup device. I never really know what the lastest backup set is most of
the time.
>But when ever my restore process runs I want it to use the lastest backup
within the
>backup device. How do I specify in my script to use the lastest backup set
when a
>restore is done?
Im'm late, and Hari already pointed out what needed, but, anyway =;-D
I woul'd not automatically scritp this... restore shoul'd be better a manual
operation as it's not a dayly house keeping activity...
anyway... you can start from getting the max file position...
SET NOCOUNT ON
DECLARE @.db VARCHAR(128)
DECLARE @.file VARCHAR(128)
DECLARE @.cmd VARCHAR(1024)
SELECT @.file = 'D:\ADOvb6\VBH_FILE\vbhotel.bak' , @.db = 'vbhotel'
SET @.cmd = 'RESTORE HEADERONLY FROM DISK = '''+ @.file + ''''
CREATE TABLE #restore (
BackupName VARCHAR(128) ,
BackupDescription VARCHAR(128) ,
BackupType INT ,
ExpirationDate DATETIME ,
Compressed INT ,
Position INT ,
DeviceType INT ,
UserName VARCHAR(128) ,
ServerName VARCHAR(128) ,
DatabaseName VARCHAR(128) ,
DatabaseVersion INT ,
DatabaseCreationDate DATETIME ,
BackupSize INT ,
FirstLsn VARCHAR(128) ,
LastLsn VARCHAR(128) ,
CheckPointLsn VARCHAR(128) ,
DifferentialBaseLsn VARCHAR(128) ,
BackupStartDate DATETIME ,
BackupFinishDate DATETIME ,
SortOrder SMALLINT ,
CodePage SMALLINT ,
UnicodeLocaleId INT ,
UnicodeComparisonStyle INT ,
CompatibilityLevel TINYINT ,
SoftwareVendorId INT ,
SoftwareVersionMajor INT ,
SoftwareVersionMinor INT ,
SoftwareVersionBuild INT ,
MachineName NVARCHAR(128) ,
Flags INT ,
BindingId uniqueidentifier ,
RecoveryForkId uniqueidentifier ,
Collation NVARCHAR(128)
)
INSERT INTO #restore EXEC(@.cmd)
SELECT MAX(r.Position) AS [MaxFile]
FROM #restore r
WHERE r.BackupType = 1
AND r.DatabaseName = @.db
AND r.MachineName = @.@.SERVERNAME
GO
DROP TABLE #restore
--<--
MaxFile
1
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply