automatic backup production DB''s to test server

  • 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.

  • 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

  • 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

  • Thanks JP - will look out for this.

    regards H.

     

  • 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.

  • 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

  • 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