June 20, 2006 at 6:41 am
I have been tasked with creating a process that will be used for developers to be able to restore from the previous day's backup to various locations.
A. There will be one process that will not be automated completely, and this is for when a developer will take a backup and restore to their local laptop. This will be done on demand. Does anyone else have such a process? what works best for you?
B. The other process, is much more indepth. Basically, the "Production" backup happens at some point (2pm - our busy time is overnight), and then the plan is to have the development server have the database restored. From there, the now restored DB will copy itself again to another DB, the "Clean" database, then, have some predefined scripts executed to do the actual "cleaning". From here, it will once again, copy the "Clean" database to two other db's for others to test against for that day. Again, does anyone have a process that resembles this? if so, what are you doing, what works best for you?
I have more tasks that I will want to get others input, but for now, this will do.
TIA
Cory
-- Cory
June 21, 2006 at 10:32 am
We refresh our test databases weekly with a scheduled SQL Agent Job.
The following is example code, similar to one of our Job steps.
I hope this helps.
--This solution requires you to create a backup device named ProdBackup
--------------------------------------------------------------
-- Back up the full database.
backup database [prodDatabase] TO ProdBackup
with init, skip
go
-- get backup file number to restore
declare @File as int
select @File=dbo.GetLastFile('ProdBackup')
print @file
-- restore database
RESTORE FILELISTONLY
FROM ProdBackup
RESTORE DATABASE [testDatabase]
FROM ProdBackup
WITH MOVE 'prodDatabase_Data' TO 'D:\SQLServer\MSSQL\DATA\testDatabase.mdf',
MOVE 'prodDatabase_log' TO 'D:\SQLServer\MSSQL\DATA\testDatabase.ldf',
REPLACE,
GO
--------------------------------------------------------------
--Here is the GetLastFile() UDF used
CREATE FUNCTION dbo.GetLastFile(
@DeviceName AS nvarchar(50)
)
RETURNS INT
AS
BEGIN
DECLARE @GetLastFile AS INT
SELECT @GetLastFile=
(SELECT TOP 1 position
FROM msdb.dbo.backupset T1
WHERE T1.media_set_id=
(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupmediafamily
WHERE logical_device_name=@DeviceName
ORDER BY media_set_id desc)
ORDER BY Backup_start_date desc)
RETURN @GetLastFile
END
June 21, 2006 at 11:47 am
B. I don't have anything like this, but without replication set up, your best bet might be.......backup the original DB, restore it to the Developement server. Stop services on the Development server, copy the .mdf and .ldf files; copy the 'copies' to the other servers and attach the databases.
-SQLBill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply