Backup and Restore

Question

Backup and Restore

acko

Right there with Babe

Points: 754
More actions
July 1, 2004 at 2:20 am

#108539

Hi everyone
I have this situation with backup and restore.
I have two instances of sql, sql1 and sql2.
Production server is sql1 and test server is sql2.
I created database maintenance plan that makes full backup every night on tape.
I would like to make some kind of job or to add steps to job that was created with DMplan which will make restore of backuped database to sql2 server.
How can i do this? How can i read which is the last file backuped on tape?
Thanks very much
Alex

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sharrell SSC-Addicted Points: 477 More actions · Answer 1

Hi Alex,

I don't use SQL Tape backups, but you should be able to use a query like this one to find the last backup for you database. Then you can issue the restore command using the returned device.

select physical_device_name from backupmediafamily where media_set_id = (select media_set_id from backupset where database_name = 'SQL1Db' and type = 'D' and backup_finish_date = (Select Max(backup_finish_date) from backupset where database_name = 'SQL1Db' and type = 'D'))

Regards,
Steve

Meddle not in the affairs of dragons, for you are crunchy and taste good with ketchup.

Bruce McCarthy SSC-Addicted Points: 477 More actions · Answer 2

First off, I wouldn't modify the jobs used by a DBMP. Once you do that, SQL squawks if you go back and try to modify the MP, itself.

I do a similar thing to what you're trying to do - in fact, the exact same thing - except that our DBMP backup is to disk not tape. I use a DTS package to accomplish it. Since the DBMP saves the file with the date and time as part of the filename, using an ActiveX Script Task I get the filename of the latest backup and set it as a DTS variable, then, using an Execute SQL Task I retrieve the filename from the variable and execute a RESTORE DATABASE statement.

Not sure if/how it would work w/ tape. If you'd like my code for the disk-based solution, just let me know.

Bruce McCarthy

acko Right there with Babe Points: 754 More actions · Answer 3

Hi Bruce

It would be nice if you send me your code.

Thanks a lot

aleksandar

acko Right there with Babe Points: 754 More actions · Answer 4

acko

Right there with Babe

Points: 754

July 5, 2004 at 3:25 am

#513265

Thanks Steve

Alex

Bruce McCarthy SSC-Addicted Points: 477 More actions · Answer 5

Alex,

I just wrote you a long, detailed message with my code, but lost it when I clicked Preview. Hopfully I'll have time to create it again soon.

Sorry. I'm bummed.

Bruce

Bruce McCarthy SSC-Addicted Points: 477 More actions · Answer 6

DTS Package has two Global Variables, one ActiveX Script task, one Execute SQL task.

Global Variables:

gFolder (the root folder of the backup files)

gFPath (the dynamically determined path to the backup file to be restored)

================================================================

ActiveX Script Task

name: get file info

language: VBScript

code:

Function Main()

Dim fso, fol, fil, farray, fname, fprefix, fyear, fmonth, fday, sdb

sdb = "aldb" 'the db name

Set fso = CreateObject("Scripting.FileSystemObject")

Set fol = fso.GetFolder(DTSGlobalVariables("gFolder").value & sdb)

Set farray = fol.Files

' set everything to yesterday

fmonth = month(dateadd("d", -1, now()))

fday = day(dateadd("d", -1, now()))

fyear = year(dateadd("d", -1, now()))

if fmonth < 10 then fmonth = "0" & fmonth

if fday < 10 then fday = "0" & fday

' sql names the backup file in the format dbname_db_yyyymmddhhmm.ext

' therefore, you could easily use today instead of yesterday and

' get the latest backup - if there are multiple backups in a day -

' by getting the max of the 4 characters that are the timestamp part of the name

fprefix = sdb & "_db_" & fyear & fmonth & fday

' look for file named w/ yesterday's time stamp and store it in global var

For Each fil in farray

fname = fil.Name

if left(fname, 16) = fprefix then

DTSGlobalVariables("gFPath").value = fol & "\" & fname

Exit For

End If

End Function

================================================================

Execute SQL task:

name: restore db

db: master

code:

-- use a home-grown SP that calls a RESTORE DATABASE statement - see code, below

exec sp_ps_restore_db 'aldb', ?, 'aldb_Data', 'aldb_Log', 'S:\Program Files\Microsoft SQL Server\MSSQL\Data\aldb_data.mdf', 'S:\Program Files\Microsoft SQL Server\MSSQL\Data\aldb_log.ldf'

-- the question mark used as a param of the SP, above is mapped to the global variable gFPath using the Parameters button on the SQL task dialog. The value was determined and stored by the ActiveX task.

-- set any permissions, etc. after restore

use aldb

exec sp_revokedbaccess 'jagman'

exec sp_adduser 'jagman'

================================================================

SP Used in Execute SQL task:

create proc dbo.sp_ps_restore_db

@dbname as varchar(25),

@sourcepath as varchar(255),

@logicalfilename_D as varchar(50),

@logicalfilename_L as varchar(50),

@targetfilepath_D as varchar(100),

@targetfilepath_L as varchar(100)

as

RESTORE DATABASE @dbname

FROM DISK = @sourcepath

WITH

MOVE @logicalfilename_D TO @targetfilepath_D,

MOVE @logicalfilename_L TO @targetfilepath_L,

REPLACE,

RECOVERY

GO

================================================================

Hope this helps. Sorry for the delay. Let me know if I can clarify anything.

Bruce