February 27, 2006 at 4:11 am
Hi, I would like to be able to automate the process of updating my test sever with the latest copy of production DB on regular basis. Currently I'm having to do this manually a number of times during the day. Is there an easy way of automating this process – any help would be much appreciated.
February 27, 2006 at 5:41 am
I have an additional step after each backup that copies the backup file to a drive on the test server, i then use this to restore from on the test server via a job. I generally have two copies of the same database on the test box, one named the same as live which the developers work on, the second with _backup appended to the name, this is the one i restore every night from the live backup. This is generally what i point developers at if they wan't to query 'live' data rather then pointing them at actual Live.
On a side note, i also copy each transaction log backup to the test server as well. I then just keep the last 24 hours Full backup and Tran backups on the live box but can keep several days worth on the test box, this generally means i don't have to go off and find a tape if i need to go back a couple of days.
Hope that helps a bit.
Growing old is mandatory, growing up is optional
February 28, 2006 at 4:28 am
Hi,
I have written a few USP's that do that job for me on a weekly basis. I will have to rewrite these USP's for you to get rid of 'company specific code'.
I will try to post these as soon as possible, but it's a busy week... Please hang on a few days...
Regards,
JP
February 28, 2006 at 4:31 am
Thanks JP - will look out for this.
regards H.
March 1, 2006 at 8:26 am
Hi everybody,
We have a DR site about 1000km away from our primary site, so full backups of the databases can' be shipped on WAN (size @ 13gb). Hence I want to take backup on standby server. We have configured log shipping. Is it possible to stop log shipping for some time, open standby database in normal mode, take a backup, again make it standby & start log shipping. Please advice on the situation.
March 1, 2006 at 9:16 am
Abhijeet,
Please do not 'hijack' other people's posts.
Proper practice:
1. start your own thread.
2. do not 'hijack' other threads.
3. pay attention to when a thread was started...some you have posted this question to are over 2 years old.
4. don't post the same question multiple times.
-SQLBill
March 2, 2006 at 6:03 am
Hi,
As promised: the stored procs I was telling you about.
Instructions:
1.You need to place the 'usp_KillDatabaseConnections' on each server you like to restore to.
2. Place the 'usp_restoreDBAsTestDB_Version_1_8 on the server that you use to perform this. (Can be the same as 1.)
3. Be sure to make backups to a shared network location (this must be accesible by both target and source server)
4. Change the path's of the MDF and LDF files in the SP. (This is the only hardcoded part in the SP)
Test the procedure.
Let me know if it works!
Regards
JP
CREATE
Proc usp_RestoreDBAsTestDB_VERSION_1_8
(@dbname sysname = '', @RestoreTargetServer sysname ='', @OriginatingServer varchar(100) = '')
AS
/*
Description: Restore the database to a test database. The database name will be renamed to TEST_databasename.
This procedure will look up the most recent DISK BACKUP file and will perform a restore on
the server with this file. (It will always assume just 1 backup in the file). Furthermore, existing
connections on the target server in the (existing) test_database will be killed.
Author: JP de Jong for USGPeople.nl
Version: 1.8
Comments: mail to jp@jpdejong.nl
Version history: As of version 1.8 complete re-write of the procedure. It now handles restoring databases on
a different server, restoring over exisitng database, auto-lookup of latest backup file,
fix-up for 'illegal' database and server names'
Remarks: The following requirements need to be met if a remote restore is performed:
A SQL login for the SQL Agent of the EXECUTING SQL Server is needed on the TARGET and SOURCE server
The Windows Account needs to have access to a NETWORK SHARE on which the BACKUP files reside.
The backup needs to be written to the network share -> RESTORE DATABASE name TO DISK = '\\server\share\backupfile.bak'
*/
DECLARE
@MDFOrigLocation varchar(255) --HARDCODED in STORED PROC
DECLARE
@LDFOrigLocation varchar(255) --HARDCODED in STORED PROC
DECLARE
@TextLengthOfMDFOrigLocation int
DECLARE
@TextLengthOfLDFOrigLocation int
Declare
@OSQL varchar(8000)
DECLARE
@PhysicalName varchar(2000)
DECLARE
@RestoreDB sysname
DECLARE
@LogBak varchar(100)
DECLARE
@PhysBak varchar(100)
DECLARE
@LogLog varchar(100)
DECLARE
@PhysLog varchar(100)
DECLARE
@Command varchar(1000)
DECLARE
@Recovery varchar(100)
if
@dbname = ''
begin
--return usage info
PRINT 'usp_RestoreDbAsTestDB_VERSION_1_8 @DBName sysname, @RestoreTargetServer sysname, @OriginatingServer varchar(100)'
PRint ' '
Print '@DBName = name of production database that will be restored as a TEST database (varchar(100))'
print '@RestoreTargetServer = name of server to restore database on. If blank, then local'
Print '@OriginatingServer = ''servername'' or leave empty. The name of the server that currently holds the backupset'
Print ' '
Print 'Restore the database to a test database. The database name will be renamed to TEST_databasename.'
Print 'This procedure will look up the most recent DISK BACKUP file and will perform a restore on'
Print 'the server with this file. (It will always assume just 1 backup in the file). Furthermore, existing'
Print 'connections on the target server in the (existing) test_database will be killed.'
Print '(c) 2006 by JP de Jong / USGPeople the Netherlands.'
RETURN
end
/*
Actual Procedure starts here.
*/
SET
NOCOUNT ON
SET
@Recovery = 'RECOVERY'
if
@OriginatingServer = ''
begin
SELECT
TOP 1 @PhysicalName = mf.physical_device_name
FROM msdb.dbo.backupmediafamily mf
INNER JOIN
msdb
.dbo.backupset s
ON mf.media_set_id = s.media_set_id
WHERE
physical_device_name
like '\\%' --get a file backup on a remote server
AND database_name = @dbName --for this database
and type= 'D' --only full Database backup
ORDER BY database_name,
backup_finish_date
DESC
end
ELSE
BEGIN
--remote server!!!
declare @NewCommand Nvarchar(4000)
set @NewCommand = '
SELECT
TOP 1 mf.physical_device_name
INTO ##TempBackup
FROM ['
+ @OriginatingServer + '].msdb.dbo.backupmediafamily mf
INNER JOIN
['
+ @OriginatingServer + '].msdb.dbo.backupset s
ON mf.media_set_id = s.media_set_id
WHERE
physical_device_name like ''\\%'' --get a file backup on a remote server
AND database_name = '''
+ @dbName + ''' --for this database
AND type= ''D'' --only full Database backup
ORDER BY database_name,
backup_finish_date DESC'
--print @NewCommand
exec ( @NewCommand)
select @PhysicalName = Physical_device_name from ##tempbackup
drop table ##tempBackup
END
--Now we have to figure out where to restore the database
Create
Table #TempBackupFiles(
LogicalName varchar
(100) NULL,
PhysicalName varchar
(255) NULL,
Type varchar
(10) NULL,
FileGroupName varchar
(100) NULL,
Size
Numeric(18,0) NULL,
MaxSize
Numeric(18,0) NULL
)
Set
@Command = 'insert into #TempBackupFiles exec (''restore filelistonly from disk = ''''' +
@PhysicalName
+ ''''''')'
--print @Command
Exec
(@Command)
update
#TEmpBackupFiles set PhysicalName = replace(PhysicalName,'\\','\') --filenames sometimes like 'D:\MSSQL\DATA\\MDFfile.MDF' due to configuration mistake
--HARDCODED ...
SET
@MDFOrigLocation = 'D:\SQL\MSSQL\DATA\'
SET
@LDFOrigLocation = 'E:\SQL\LOGS\'
SET
@TextLengthOfMDFOrigLocation = len(@MDFOrigLocation)
SET
@TextLengthOfLDFOrigLocation = len(@LDFOrigLocation)
--Changing filenames and database names.
begin
set @RestoreDB = 'TEST_' + @dbname
select @LogBak = LogicalName , @PhysBak = @MDFOrigLocation + 'TEST_' + substring(PhysicalName,@TextLengthOfMDFOrigLocation + 1, len(physicalname)- @TextLengthOfMDFOrigLocation) FROM #TempBackupFiles where Type = 'D'
select @LogLog = LogicalName , @PhysLog = @LDFOrigLocation + 'TEST_' + substring(PhysicalName,@TextLengthOfLDFOrigLocation + 1, len(physicalname)- @TextLengthOfLDFOrigLocation) FROM #TempBackupFiles where Type = 'L'
end
if
@RestoreTargetServer =''
begin
--local server
--Select @LogBak, @PhysBak, @LogLog, @PhysLog
--Now kill database connections in test db
exec usp_KillDatabaseConnections @RestoreDB
--issue a restore command
set @Command = 'Restore Database [' + @RestoreDB + '] from disk = ''' + @PhysicalName + ''' with move ''' + @LogBak + ''' to ''' + @PhysBak +''' , move ''' + @loglog + ''' to ''' + @PhysLog + ''', ' + @Recovery + ', STATS = 10'
PRINT @Command
exec (@command)
/*
Following part is optional. Fill in your own TEST account that wou will need to grant access to!
set @Command = 'use [' + @RestoreDB + '] EXEC sp_grantdbaccess ''your_login'' EXEC sp_addrolemember ''db_datawriter'',''your_login'' EXEC sp_addrolemember ''db_datareader'',''your_login'' '
Print @command
exec (@command)
*/
Print 'Database restored on local server.'
end
else
begin
--remote server
--kill connections
Set @Command = 'exec usp_KillDatabaseConnections ' + @RestoreDB
--The account used to run this procedure (SQL Agent) needs a login on the TARGETSERVER.
set @OSQL = 'xp_cmdshell ''OSQL -E -S ' + @RestoreTargetServer + ' -Q"' + @Command + '" '' '
print @OSQL
exec (@osql)
--issue a restore command
set @Command = 'Restore Database [' + @RestoreDB + '] from disk = ''''' + @PhysicalName + ''''' with move ''''' + @LogBak + ''''' to ''''' + @PhysBak +''''' , move ''''' + @loglog + ''''' to ''''' + @PhysLog + ''''', ' + @Recovery + ', STATS = 10'
PRINT @Command
set @OSQL = 'xp_cmdshell ''OSQL -E -S ' + @RestoreTargetServer + ' -Q"' + @Command + '" '' '
Print @OSQL
exec (@OSQL)
/*OPTIONAL
--set rights
set @Command = 'use [' + @RestoreDB + '] EXEC sp_grantdbaccess ''''Your_Login'''' EXEC sp_addrolemember ''''db_datawriter'''',''''your_login'''' EXEC sp_addrolemember ''''db_datareader'''',''''your_login'''' '
Print @Command
set @OSQL = 'xp_cmdshell ''OSQL -E -S ' + @RestoreTargetServer + ' -Q"' + @Command + '" '' '
print @OSQL
exec (@osql)
*/
Print 'Database restored on remote server'
end
GO
CREATE
PROCEDURE usp_killDataBaseConnections @DatabaseName varchar(50), @WithMessage bit=1
AS
/*
Description: Stored Procedure that kills database connections for a given database.
Author: William Ryan (knowdotnet.com)
Version: n/a
*/
SET
NOCOUNT ON
DECLARE
@spidstr varchar(8000)
DECLARE
@ConnectionKilled smallint
SET
@ConnectionKilled=0
SET
@spidstr = ''
IF
db_id(@DatabaseName) < 4
BEGIN
PRINT 'No can do...'
RETURN
END
SELECT
@spidstr=COALESCE(@spidstr,',' )+'kill '+CONVERT(VARCHAR, spid)+ '; '
FROM
master..sysprocesses WHERE dbid=db_id(@DatabaseName)
IF
LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnectionKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DatabaseName)
END
IF
@WithMessage =1
PRINT CONVERT(VARCHAR(10), @ConnectionKilled) + ' Connection(s) killed for DB ' + @DatabaseName
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply