jasonwisdom
asked on
How do I transfer a database, from a Production Server to a Staging/Development Server?
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.
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"
Thank you greatly.
ASKER
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?
Also, Log Shipping is not realtime, it's only as often as transaction logs are created - yes?
ASKER
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.
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.
I use a backup and restore. It is fast and it is working like a charm.
ASKER
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?
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?
You can script everything and save it in a stored proc to run it easily or schedule it.
ASKER
Do you have an example of this script, and how would I create such a script?
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
ASKER
Thank you emareou, let me check it out hopefully tomorrow. If this (or close to this) does the trick then the points are yours.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Or I can put both scripts into a Maintenance Plan or SSIS package or Job and execute the first then second.
if the 2 servers are not linked, how will you launch one process from the other server?
ASKER
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.
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.
ASKER
Commentary listed above.
The more advanced solution is a "Log Shipping" for near realtime mirroring: http://msdn.microsoft.com/en-us/library/ms188698.aspx