John,

Here's a script using SQL Litespeed to restore the latest mydatabase
backup file to mytargetdb:

DECLARE at.cmd varchar(2000) ,
at.fname varchar(128) ,
at.s varchar(128) ,
at.i int ,
at.d datetime ,
at.sql nvarchar(2000) ,
at.StartDate datetime,
at.dbname varchar(128),
at.remotepath varchar(200)

SET at.dbname = 'mydatabase'
SET at.remotepath = '\\myserver\sharefolder1\sharefolder2\'

SELECT at.StartDate = getdate()

CREATE TABLE #a (s varchar(2000))

-- get latest backup from remote path
SELECT at.cmd = 'dir /B ' + at.remotepath + at.dbname + '*.*'
INSERT #a EXEC master..xp_cmdshell at.cmd

DELETE #a
WHERE s IS NULL

SELECT at.fname = max(s) from #a
-- Print at.fName

DROP TABLE #a

SET at.remotepath = at.remotepath + at.fname

exec master.dbo.xp_restore_database at.database='mytargetdb',
at.filename= at.remotepath,
at.encryptionkey='',
at.filenumber=1,
at.with='REPLACE',
at.with='RECOVERY',
at.with='MOVE "mydatabase_data" TO "G:\Microsoft SQL
Server\mssql2k\data\mytargetdb_data.mdf"',
at.with='MOVE "mydatabase_log" TO "G:\Microsoft SQL
Server\mssql2k\data\mytargetdb_Log.ldf"',
at.servername = 'mytargetservername'

Replace 'at.' with the at sign to complete the variable names. It
could be modified for straight T-SQL.

S. Davey (8/31)


MI DIT\Agency Services
7:30 A.M. - 4:00 P.M. ET
517.335.4237 (M- F CCC )


>>> "Eisbrener John "
<mssqldba-ezmlmshield-x11729734.[Email address protected] 08/30/07
5:29 PM >>>
I figure this is the perfect list to ask if anyone has a
script/job/etc. that
will restore a database from a backup file to another server. So, in
the act
of being lazy, does anyone have any scripts/jobs/etc. handy that can do
this?


What I want to do is refresh a test environment from production backups
on a
scheduled basis. Currently I do this manually, but I'm getting tired
of this
process and would rather script it into a job. Instead of taking the
time to
create this script myself initially (unless of course I have to), I
thought
I'd ask you guys first. Here are circumstances that may or may not be
of use
to those of you thinking that you may have something handy for me.

1. Restoring from SQL 2000 backups generated via maintenance plans
(which means a changing backup file name)
2. Backups stored on network drive accessed via UNC file path
3. Backups located in Backup/Subdirectories
4. Restoring to a SQL 2000 database server
5. Database Names and will need to be changed
6. Data and Log file paths will need to be changed
7. Must run some custom SQL scripts after restore operations are
complete

If anyone has anything they'd like to pass along I'd appreciate it.

Thanks everyone,

John Eisbrener
SQL Database Administrator
Capitol Insurance Companies





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



MS Sql Server LazyDBA home page