October 20, 2010 at 1:24 pm
What is the best way to synchronize production database to development envirnoment without effecting performance on the production. All of the databases are partitioned.
So far i used to restore all databases onto development but i dont want to continue this anymore due to the time involved.
October 20, 2010 at 1:34 pm
October 20, 2010 at 2:23 pm
Tara-1044200 (10/20/2010)
So far i used to restore all databases onto development but i dont want to continue this anymore due to the time involved.
How often are you doing this and why is time the issue?
Create a script in a SQL job that you can start in the evening before you go home and let it run over night. If you have multiple database have multiple jobs.
It takes a little bit of code to find the most recent backup, kill any active connections and then the restore is on its way.
You probably can't use replication because as soon as the schema changes replication breaks. Backup and restore are the simplest. If you want to go the Sys Admin way you can snapshot the files on the SAN, but this is just another form of backup and restore (or attache)
Use this as a basis:
Declare@dir varchar(255),
@cmd varchar(255),
@db varchar(255)
Set @db = 'MyDatabaseName'
Create table #tmp (fname varchar(255))
Set @dir ='\\BackupServer\BackupDrive$\BackupFolder\NextFolderLevel\'
Set @cmd = 'dir /A-D /O-D /B ' + @dir + '' + 'FirstPartOfBackupFileName' + '*.BAK' -- /O-D gives latest backup first
print @cmd
Insert into #tmp EXEC xp_cmdshell @cmd
Select top 1 @dir = @dir + fname from #tmp
where fname is not NULL
order by fname desc -- Gives most recent backup first
Print @dir
if @dir like '%File Not Found%'
BEGIN
raiserror('No backup file found for database %s',16,1, @db)
END
ELSE
BEGIN
-- execute ProcToKillUsers 'MyDatabaseName' -- Either create a proc that checks sysprocessors and kills
-- users based on DB name, or add the code here
restore database @db
FROM DISK = @dir
with MOVE 'Logical_mdf' TO 'X:\FilePath\MyDatabase.mdf'
, MOVE 'Logical_ndf' TO 'Y:\FilePath\MyDatabase.ndf'
, MOVE 'Logical_Log' TO 'L:\FilePath\MyDatabase_log.ldf'
, REPLACE
END
Drop table #tmp
Sorry my original was taken from an old LiteSpeed version and I didn't make all the corrections.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply