Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik