Creating an Overnight Database copy - Best Way

[Hap007]

Hi,

Overview:
1) I have a client whose remote office connects to a live SQL Database for reporting.
2) I want to provide a method where the remote user can connect to a copy of the database rather than the live data.
3) The copy function needs to run overnight, unattended.
4) I can program to make this happen in VB or Access, but me thinks there is a better way.
5) The destination can be a SQL Database copy or could be an Access mdb copy.

Question:
Using Enterprise Manager, what might be the 'Correct' or better way to handle this?

Should I just code a VB or Access App to copy the data?

Any suggestions would be greatly appreciated.

Thanks,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[nigelrivett]

I would backup the database and restore it.

I datestamp the database backup

mydb_full_20040728_152345

For instance then restore the oldest version in the backup directory.
The only thing that can go wrong is that the database may be in use when you try to restore.
You can also leave the database in standby to restore future log backups as it needs to be read only.

I've been meaning to put the full solution on my web site which does the backups (full, log, diffs) copies them to backup server, restores a full, diff then all the logs to test for all the databases - here is the sp that will do a copy of latest backup file and restore it. It goes with the backup SP

http://www.nigelrivett.net/BackupAllDatabases.html

create proc s_TestRestore
@dbname varchar(128) ,
@remotepath varchar(200) ,
@localpath varchar(200) ,
@recipients varchar(128)
as

/*
exec s_TestRestore
@dbname = 'mydb' ,
@remotepath = '\\remsvr\mbackups$\full\mysvr\' ,
@localpath = 'c:\TestRestore\'
*/

declare @cmd varchar(2000) ,
@filename varchar(128) ,
@s varchar(128) ,
@i int ,
@d datetime ,
@sql nvarchar(2000) ,
@StartDate datetime

select @StartDate = getdate()

if exists (select * from master..sysdatabases where name = @dbname)
begin
select @cmd = 'drop database ' + @dbname
exec (@cmd)
end

select @cmd = 'dir /B ' + @remotepath + @dbname + '*.*'
create table #a (s varchar(2000))
insert #a exec master..xp_cmdshell @cmd

delete #a
where s is null
or s not like '%full%'

select @filename = max(s) from #a

select @cmd = 'del /Q ' + @localpath + '*.*'
exec master..xp_cmdshell @cmd

select @cmd = 'xcopy ' + @remotepath + @filename + ' ' + @localpath
exec master..xp_cmdshell @cmd

select @cmd = 'restore filelistonly from disk = ''' + @localpath + @filename + ''''

create table #files (lname varchar(128), pname VARCHAR(128), type varchar(10), fgroup varchar(128), size varchar(50), maxsize varchar(50))
insert #files exec (@cmd)

-- now buld the restore
select @cmd = null ,
@s = ''

while @s < (select max(lname) from #files)
begin
select @s = min(lname) from #files where lname > @s

select @cmd = coalesce(@cmd + ',move ', '') + '''' + @s + ''' to ''' + @localpath + @s + ''''
end
select @cmd = 'restore database ' + @dbname + ' from disk = ''' + @localpath + @filename + ''' with move ' + @cmd + ', standby = ''' + @localpath + 'standby.fil'''
select (@cmd)
exec (@cmd)

drop table #files
drop table #a

select @sql = 'select @i = count(*) from ' + @dbname + '..sysobjects select @d = crdate from master..sysdatabases where name = ''' + @dbname + ''''
exec sp_executesql @sql, N'@i int out, @d datetime out', @i out, @d out

if @d > @StartDate and @i > 20
begin
select @cmd = 'restore ' + @filename + ' completed successfully - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
end
else
begin
select @cmd = '********************* restore ' + @filename + ' failed - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
end

go


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Hap007]

Dear nigelrivett,

Thanks, I will give this a try and let you know.

Since this is for a client, can I setup to run every night?

Are there any other methods that anyone might know of?
I am still trying various solutions on my local server before I visit the client's site.


Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[nigelrivett]

I just schedule it using sql server

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.