Solved

How do I transfer a database, from a Production Server to a Staging/Development Server?

Posted on 2008-06-23
15
516 Views
Last Modified: 2012-06-21
Hi there.

What is the easiest, simplest, and most secure way to tranfer one database, from a Production server, to the same database name on a Staging server?  

It must be automated - so I can schedule it for 3am daily, and also click "Go" and run it on the fly.  
It must also leave the Source (Production) available as this is a 24x7 environment.

My first thought, upon opening SSIS, was to use the "Transfer Database Task".  But that is getting all weird on me.  I cannot specify or create "DestinationDatabaseFiles" at all, and when I hit "OK" with what I have, the red-X circle is still on the task, complaining that "At least one source file must be specified".  But there are 2 source files listed.  So what is going on.

Thank you greatly.
0
Comment
Question by:jasonwisdom
  • 9
  • 5
15 Comments
 
LVL 2

Expert Comment

by:Alexey_Varlamov
Comment Utility
The most common solution for your task is a "Database Mirroring" - http://msdn.microsoft.com/en-us/library/ms189852.aspx. It is a process of backuping and restorig a database periodically

The more advanced solution is a "Log Shipping" for near realtime mirroring:  http://msdn.microsoft.com/en-us/library/ms188698.aspx
0
 
LVL 1

Author Comment

by:jasonwisdom
Comment Utility
Is there an SSIS solution... does Transfer Database Task actually work, or no?

Also, Log Shipping is not realtime, it's only as often as transaction logs are created - yes?
0
 
LVL 1

Author Comment

by:jasonwisdom
Comment Utility
The other thing is that we'll need automatic as well as manual capabilities.
In other words, Staging will probably restore on a daily basis.
Development needs to restore "on demand" - so it might be today, then 3 weeks from today, then 2 months after that.  Purely on a "by request" basis because Development is, well, being developed.
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
I use a backup and restore. It is fast and it is working like a charm.
0
 
LVL 1

Author Comment

by:jasonwisdom
Comment Utility
Is there a way to make a Backup & Restore a 1-click operation?  
I just want to click a single button, or right-click something and say Go, and have it be done.
The destination has its own file names, its own logical file names, etc.  So it has to be able to work with that.

That's why I like Integration Services.  But how can I get the Transfer Database Task to work?
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
You can script everything and save it in a stored proc to run it easily or schedule it.
0
 
LVL 1

Author Comment

by:jasonwisdom
Comment Utility
Do you have an example of this script, and how would I create such a script?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
This is a script I run on my laptop to backup a database on a server, copy the bak file to my laptop and restore the backup (I even rename the current database on my laptop to keep a copy).


SET NOCOUNT ON
 

-------------------------------------------------------------------------------------------

--

-- These variables will become parameters

--
 

DECLARE @DBName VARCHAR(100)

SET @DBName = 'Pubs'
 

SELECT Getdate() AS 'Start time'
 
 

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

--

-- Constantes

--
 

--Répertoire contenant les fichiers de data sur le serveur destination

DECLARE @SQLDataPath VARCHAR(100)

SET @SQLDataPath = 'C:\_Data\SQL2005\MSSQL.1\MSSQL\Data\'
 

--Paramètre du serveur hôte (source)

DECLARE @SourceServerName VARCHAR(100)

SET @SourceServerName = 'YourServerNameHere'
 

DECLARE @SourceServerUID VARCHAR(100)

SET @SourceServerUID = 'YourServerUIDHere'
 

DECLARE @SourceServerPWD VARCHAR(100)

SET @SourceServerPWD = 'YourServerPWDHere'
 

--NET USE parameters

DECLARE @NetUseParams VARCHAR(200)

SET @NetUseParams = '"\\ServerName\ShareNameWhereTheBackupIs" NetUsePWD /user:NetUseUser'
 
 

--Path des fichiers d'origine pour la copie du backup

--(dans notre cas c'est Z parce qu'un drive sera mappé dans le script

DECLARE @SourcePath VARCHAR(100)

SET @SourcePath = 'R:'
 

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

--

-- Variables

--
 
 

--Contiendra le path complet ou le backup sera copié

DECLARE @BCKDestPath VARCHAR(1000)

--Contiendra le physical_device_name du backup source (path complet du fichier de backup)

DECLARE @BCKSourcePath VARCHAR(1000)

--Utiliser pour conserver l'état des commandes EXEC

DECLARE @result INT

--Contiendra différentes requêtes SQL

DECLARE @Query nVARCHAR(4000)

--Contiendra différentes commandes à être exécutées par EXEC

DECLARE @cmd VARCHAR(1000)

--Variables utilisées pour le KILL des sessions

DECLARE @SPID AS NVARCHAR(4)

DECLARE @KillCommand AS NVARCHAR(255)
 

DECLARE @TimeStamp AS VARCHAR(20)

SET @TimeStamp = REPLACE(REPLACE(REPLACE(convert(CHAR(20), GetDate(), 120), '-', ''), ':', ''), ' ', '')
 

--Variables utilisées pour les noms de fichiers (physique et logique) lors du restore

DECLARE @PhysicalData AS VARCHAR(250)

DECLARE @PhysicalLog AS VARCHAR(250)

DECLARE @LogicalData AS VARCHAR(250)

DECLARE @LogicalLog AS VARCHAR(250)
 
 
 

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-- STEP 1

--

-- Prend les fichiers en backup sur le serveur de production

--
 

SET @Query = 'DRIVER={SQL Server};SERVER=' + @SourceServerName + ';UID=' + @SourceServerUID + ';PWD=' + @SourceServerPWD + ';'
 

EXEC sp_addlinkedserver 

   @server = 'Linked_Server', 

   @srvproduct = '',

   @provider = 'MSDASQL',

   @provstr = @Query
 

execute sp_serveroption 'Linked_Server','rpc','true'
 

execute sp_serveroption 'Linked_Server','rpc out','true'
 

EXEC  Linked_Server.master.dbo.emoreau_BackupDB @DBName
 

exec sp_dropserver 'Linked_Server'
 
 
 
 

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-- STEP 2

--

-- Copie du fichier de backup

--
 

SET  @BCKSourcePath = @SourcePath + '\zzEMoreau.bak'
 

SET @cmd = 'net use ' + @SourcePath + ' ' + @NetUseParams
 

select @bcksourcepath, @cmd
 

EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT

IF (@result = 0)

        PRINT 'NET USE = Success'

ELSE

BEGIN

        PRINT 'NET USE = Failure'

        RAISERROR('Impossible de mapper le drive ',1,1)

        GOTO finish

END
 
 
 
 

SET @BCKDestPath = 'c:\temp\' + @DBName + @TimeStamp + '.BAK'
 

SET @cmd = 'copy "' + @BCKSourcePath + '" "' + @BCKDestPath + '"'
 
 

EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT

IF (@result = 0)

        PRINT 'Copy backup = Success'

ELSE

BEGIN

        PRINT 'Copy backup = Failure'

        RAISERROR('Copy backup = Failure',1,1)

        GOTO finish

END
 
 

SET @cmd = 'net use ' + @SourcePath + ' /delete'

EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT

IF (@result = 0)

        PRINT 'NET USE /DELETE = Success'

ELSE

BEGIN

        PRINT 'NET USE /DELETE = Failure'

        RAISERROR('Impossible de démapper le drive ',1,1)

        GOTO finish

END
 
 
 

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-- STEP 3

--

-- Restore database

--
 

PRINT ' --- RENAMING THE CURRENT DATABASE --- '
 

declare @DBNameZZ VARCHAR(20)

set @DBNameZZ = 'zz' + @DBName 
 

IF EXISTS (SELECT * 

	   FROM   master..sysdatabases 

	   WHERE  name = @DBNameZZ)

	BEGIN

		PRINT @DBNameZZ + ' is existing'

		SET @cmd = 'DROP DATABASE ' + @DBNameZZ

		exec (@cmd)

	END

ELSE

	BEGIN

		PRINT @DBNameZZ + ' does not exist'

	END
 

IF EXISTS (SELECT * 

	   FROM   master..sysdatabases 

	   WHERE  name = @DBName)

	BEGIN

		SET @cmd = 'sp_RenameDB ' + @DBName + ', ' + @DBNameZZ

		exec (@cmd)
 

	END
 

PRINT ' --- TRYING TO SET EXCLUSIVE ACCESS TO THE DATABASE --- '
 

--Kill all users in Destination Database except system users
 

while (select count(*) from sysprocesses pc

inner join sysdatabases sd on pc.dbid=sd.dbid

where 

pc.spid<>@@SPID and sd.name=@DBName)<>0

BEGIN

        set @spid=(select top 1 pc.spid from sysprocesses pc

        inner join sysdatabases sd on pc.dbid=sd.dbid

        where 

	pc.spid<>@@SPID and sd.name=@DBName)

        set @killcommand='kill ' + @spid

        exec(@killcommand)

END
 

PRINT ' --- READY TO RESTORE --- '
 
 

SET @PhysicalData = @SQLDataPath + @DBName + '_Data' + @TimeStamp + '.mdf'

SET @PhysicalLog = @SQLDataPath + @DBName + '_Log' + @TimeStamp + '.ldf'
 
 

                SET @LogicalData = @DBName

                SET @LogicalLog  = @DBName
 
 
 

RESTORE FILELISTONLY

      FROM DISK = @BCKDestPath
 
 

RESTORE DATABASE @DBName

        FROM DISK = @BCKDestPath

        WITH REPLACE, RECOVERY,

        MOVE @LogicalData TO @PhysicalData,

        MOVE @LogicalLog TO @PhysicalLog
 
 

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------
 
 

finish:
 

SELECT Getdate() AS 'End time'
 

SET NOCOUNT OFF

GO

Open in new window

0
 
LVL 1

Author Comment

by:jasonwisdom
Comment Utility
Thank you emareou, let me check it out hopefully tomorrow.  If this (or close to this) does the trick then the points are yours.
0
 
LVL 1

Author Comment

by:jasonwisdom
Comment Utility
Thank you emoreau for sharing your script, and my apologies for taking so long to get around to checking it out.

I can't use this.  It creates then drops linked servers, and it also uses xp_cmdshell - a big security no-no in this environment.

I just want to back up a database to a file, on a shared network drive,
and restore that backup file to another server.

It should only be two scripts.  Right?
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 150 total points
Comment Utility
>>It should only be two scripts.  Right?

Yes you can have one (the backup) that runs at 1AM
And the other one (the restore) that runs at 2AM (if your backup takes less then one hour to complete).
0
 
LVL 1

Author Comment

by:jasonwisdom
Comment Utility
Or I can put both scripts into a Maintenance Plan or SSIS package or Job and execute the first then second.
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
if the 2 servers are not linked, how will you launch one process from the other server?
0
 
LVL 1

Author Comment

by:jasonwisdom
Comment Utility
To resolve this issue,
we created a Job.  It runs a batch file as CmdExec.  the batch file can run Backup database and Restore database on 2 different servers.
This is one step better than xp_cmdshell...still not my optimal solution - I would have like to have an SSIS package or Maintenance Plan that could do the trick all at once, without needing Linked Servers or OS-level executables.

Emoreau I'll give you credit for answering this - you didn't directly lead me to resolution so I'm giving you a "B".  You did help me as much as anybody, though...thank you.
0
 
LVL 1

Author Closing Comment

by:jasonwisdom
Comment Utility
Commentary listed above.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now