Hi all,

I need to be able to take a copy of a database on one server and restore it to another server automatically.

AT my last job the DBA's created a SQL server agent Job that we could run using the management console to restore a database to a DEV server from .bak files on the PROD server. This is the kind of thing i'm after.

Alas a programmer & my DBA knowledge is lacking, and i now work for a small company with no DBA's as such. Any suggestions??

Cheers,
Ken

are you talking about Log Shipping?

http://www.sql-server-performance.co...g_shipping.asp

No i'm not after Log Shipping. We don't need to keep the databases in synch.

All we need to do is restore the latest production backup to Dev when required.

I've been hunting around and i think i've found a script i can use in a job to manage this.

Cheers,
Ken

There are some issues with what you want to do. What if the target db exists, what do you want to name the target db, and a number of others. I will post my code in the next reply that may help you on your way. It looks at an *.bak files in a particular location. Then it drops the database of the same name on the server its run on (VERY DANGEROUS). So be careful in how you use this script. But for example if on prod server you have your dat files on drive e: but want to restore them to dev server d: you have to change some things I hard coded for my needs. My script pulls *.bak from a variety of source SQL servers with many different locations of the files then restores them all to a single server dropping the database if it exists and createing a database name that matches the *.bak file. For example if you had a thisdatabase.bak file it would drop the database thisdatabase if it exists, create a new one in a hard coded path, then restore thisdatabase.bak to that newly created database doing all moves as needed. Use my script to build what you want but again BE VERY CAREFUL as its easy to drop a database you wanted to keep. It is also setup to loop so if you had 100 *.bak files in a particular path it will restore all 100 of them and not stop till its done.

Let me know if this does what your looking for.....

yeah thats the kind of thing i'm after. database name stays the same, just want to trash the dev version and restore a copy from prod.

Cheers,
Ken

set concat_null_yields_null off
Print '##### Start Time was: ' + convert(char(24),getdate())

Declare @cmd Varchar(4000) -- the sql command build and executed several times through out the script
       ,@cmd2 Varchar(4000) -- second level deep nested cmd
       ,@itteration tinyint -- count of how many times a loop has been done.
       ,@logic_name Varchar(100) -- logical name used in the intitial db creation
       ,@add_me Varchar(4000) -- temp string for adding in paths
       ,@svc_name Varchar(64) --
       ,@db_name Varchar(36) -- temp name of the target database being restored into (db_name.bak) derrived from the *.bak file name not needed when looping
       ,@src_location Varchar(4000) -- path from the server to get to the folder containing the *.bak files to be restored
       ,@sql_FilePath Varchar(4000) -- path to create the database in for the SQL server
       ,@sql_LogPath Varchar(4000) -- path to create the logs for the SQL server
       ,@sql_BakPath Varchar(4000) -- path to create the logs for the SQL server
       ,@rcount int -- int for storing rowcount

set @sql_BakPath = '\\source\Backup\'
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------

select @svc_name = (select @@servicename)

if upper(@svc_name) = 'SQLONE'
   begin
      set @sql_FilePath = 'F:\SqlOne'
      set @sql_LogPath = 'E:\SqlOne'
   end

if upper(@svc_name) = 'SQLTWO'
   begin
      set @sql_FilePath = 'F:\SqlTwo'
      set @sql_LogPath = 'E:\SqlTwo'
   end

if upper(@svc_name) = 'SQLDW'
   begin
      set @sql_FilePath = 'F:\SqlDW'
      set @sql_LogPath = 'F:\SqlDW'
   end

if len(@sql_FilePath) = 0
   goto quit

------------------------------------------------------------
-- Prepare temp tables needed for the process
------------------------------------------------------------

CREATE TABLE #restoretemp
(
    LogicalName varchar(500)
   ,PhysicalName varchar(500)
   ,type varchar(10)
   ,FilegroupName varchar(200)
   ,size varchar(200)
   ,maxsize varchar(200)
)

------------------------------------------------------------
-- Get a list of the *.bak files into a cursor
------------------------------------------------------------
set @cmd = 'xp_cmdshell ''dir ' + @sql_BakPath +'\*.bak' + ' > ' + @sql_BakPath + '\zyxwvuBackupList.txt /b'''
print @cmd
exec(@cmd)


create table #tempBakList (fname varchar(100))

set @cmd = 'BULK INSERT #tempBakList '
set @cmd = @cmd + ' FROM ''' + @sql_BakPath + '\zyxwvuBackupList.txt' + ''' WITH '
set @cmd = @cmd + '( FIELDTERMINATOR = ''|'', '
set @cmd = @cmd + ' ROWTERMINATOR = ''\n'''
set @cmd = @cmd + ' )'

print @cmd
exec(@cmd)

set @cmd = 'update #tempBakList set fname = left(fname,len(rtrim(fname)) - 4)'
print @cmd
exec(@cmd)


-- exclude certain databases from the restore here
delete from #tempBakList where fname in ('master','model','msdb','pubs', 'northwind','ServerInfo')
delete from #tempBakList where fname in ('datawarehouse')
delete from #tempBakList where upper(substring(fname,1,3)) = 'ZZZ' -- to not wipe any test/in process databases

------------------------------------------------------------------------------------------
-- at this point all the backup files to be loaded are in the #tempBakList table, need to drop the table if it exists, then add the table in a cursor
-- this section is the cursor that does just that.
------------------------------------------------------------------------------------------
DECLARE @name_counter int
          ,@row_counter int -- = '0'

set @name_counter = 0

--select name from sysobjects
DECLARE restore_cursor CURSOR FOR

select * from #tempBakList
ORDER BY fname

OPEN restore_cursor

while 0 = 0
BEGIN

FETCH NEXT FROM restore_cursor INTO @db_name
if @@FETCH_STATUS <> 0
   break

   set @cmd = ''
   set @cmd2 = ''
   set @name_counter = @name_counter + 1

            ----------
            -- drop the database if it is already there
            ----------
               set @cmd = 'drop database ' + @db_name
               print '########## 001 ########## ' + @cmd
               exec(@cmd)
            ----------
            -- create the DB
            ----------
               set @cmd = 'CREATE DATABASE ' + @db_name + ' ON '
               set @cmd = @cmd + '( NAME = ' + @db_name + '_data , '
               set @cmd = @cmd + 'FILENAME = ''' + @sql_FilePath + '\' + @db_name +'.mdf'' '
               set @cmd = @cmd + ',SIZE = 10'
               --set @cmd = @cmd + ',MAXSIZE = 50'
               --set @cmd = @cmd + ',FILEGROWTH = 5 '
               set @cmd = @cmd + ' )'
               set @cmd = @cmd + ' LOG on '
               set @cmd = @cmd + '( NAME = ''' + @db_name + '_log'','
               set @cmd = @cmd + ' FILENAME = ''' + @sql_LogPath + '\' + @db_name + '.ldf'' '
               set @cmd = @cmd + ' ,SIZE = 5MB '
               --set @cmd = @cmd + ' ,MAXSIZE = 25MB '
               --set @cmd = @cmd + ' ,FILEGROWTH = 10MB'
               set @cmd = @cmd + ') '

               print '########## 002 ########## ' + @cmd
               exec(@cmd)

            -----------------------------------------
            -- find out what needs to be done for a move
            -----------------------------------------
               Truncate table #restoretemp
                  SET @cmd = 'RESTORE FILELISTONLY FROM DISK = ''' + @sql_BakPath + '\' + @db_name + '.bak'' '
               print '########## 003 ########## ' + @cmd
               INSERT #restoretemp EXEC(@cmd)

               select 'backup info' as bak,* from #restoretemp

               set @itteration = 1
               while 0=0
                     Begin
                        select LogicalName from #restoretemp where upper(type) <> 'L' and upper(FilegroupName) <> 'PRIMARY'
                        set @rcount = @@rowcount
                           if @rcount = 0
                              set @itteration = 100
                        if @itteration > 99
                           Break
                        select @logic_name = (select top 1 LogicalName from #restoretemp where upper(type) <> 'L' and FilegroupName <> 'PRIMARY')
                        SET @cmd = 'ALTER DATABASE ' + @db_name + ' ADD FILE (NAME = ' + @db_name + '_data' + right('00' + convert(varchar(2),@itteration),2) + ', FILENAME = ''' + @sql_FilePath + '\' + @db_name + '_data' + right('00' + convert(varchar(2),@itteration),2) + '.ndf'', SIZE = 10)'
                        SET @cmd2 = @cmd2 + ', MOVE ''' + @logic_name + ''' TO ''' + @sql_FilePath + '\' + @db_name + '_data' + right('00' + convert(varchar(2),@itteration),2) + '.ndf'''
                        exec(@cmd)
                        set @cmd = 'delete from #restoretemp where LogicalName = ''' + @logic_name + ''''
                        print @cmd
                        exec(@cmd)

                        set @itteration = @itteration + 1
                        Continue
                     End

            ----------
            -- Now restore the DB
            ----------
               set @cmd = 'RESTORE DATABASE [' + @db_name + '] '
               set @cmd = @cmd + 'FROM DISK = ''' + @sql_BakPath + '\' + @db_name + '.bak'' WITH FILE = 1, NOUNLOAD, STATS = 10 '
               set @cmd = @cmd + ', REPLACE '


               select @add_me = (Select LogicalName from #restoretemp where FilegroupName = 'PRIMARY')
                  --print '==========> ' + @add_me
                  --select * from #restoretemp
               set @cmd = @cmd + ', MOVE ''' + @add_me + ''' TO ''' + @sql_FilePath + '\' + @db_name +'.mdf'' '

               select @add_me = (Select LogicalName from #restoretemp where FilegroupName is null )--= 'NULL')
                   --print '==========> ' + @add_me
                   -- select * from #restoretemp
               set @cmd = @cmd + ', MOVE ''' + @add_me + ''' TO ''' + @sql_FilePath + '\' + @db_name +'.ldf'' '

               set @cmd = @cmd + @cmd2

               print @cmd
               exec(@cmd)

print ' ============================================ CYCLE COMPLETE ================================================== ==============='

END

CLOSE restore_cursor
DEALLOCATE restore_cursor

quit:
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
 drop table #tempBakList
 drop table #restoretemp
Print '##### Finish Time was: ' + convert(char(24),getdate())

I have some built in safeguards and there is a slight wrapping issue with my post due to my long lines.

The script as is will not run unless your server names are 'SQLONE', 'SQLTWO', or 'SQLDW'. This was done
so if I run the script on a prod server there is not chance of it executing. It's a work in progress script
so its not completely cleaned up but the logic took some time researching. For example if they have multiple
files that they stored their data and log files in it moves them all. It does not require that they only have
one of each and it does the needed move functionality. I had to remove some corporate propriatary stuff from the script
I did not test it so I hope doing that did not break it. Expect to have to tweek the script a bit but if you
follow the logic it should work fine. Let me know if you have a problem.

thanks for that, i'll check it out