August 18, 2010 at 1:59 pm
Hello everyone!
I'm playing around today with backup and restore for an SSIS package. My goal is to backup from my DEVELOPMENT instance from database 197 and restore that to my PRODUCTION instance OVERTOP of my PROD database.
On line I find several T-SQL scripts that kind of make sense, but I trust you guys (and gals) over Google any day (hehe)
Here's what I found...
RESTORE DATABASE Business_Data_TSQL
FROM DISK='d:\Business_Data.bak'
WITH RECOVERY
MOVE 'Business_Data' TO 'D:\TSQL\Business_Data.mdf',
MOVE 'Business_Data_log' TO 'D:\TSQL\Business_Data_log.ldf', STATS=5
This makes sense up to the MOVE section. I understand what the MOVE is doing, I'm just concerned about the DEVELOPMENT database being called 197 and PRODUCTION database being called PROD. Is it as simple as saying:
MOVE '197' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PROD.mdf',
MOVE '197_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PROD_Log.ldf'
Will that OVERWRITE the existing PROD.mdf and PROD_Log.ldf already in 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'?
Thank you as always!
:w00t:
August 18, 2010 at 2:26 pm
It is as long as you also include the 'with replace' clause
---------------------------------------------------------------------
August 18, 2010 at 4:18 pm
I keep getting...
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'MOVE'.
Here's my actual SQL
RESTORE DATABASE HCN_PROD2
FROM DISK ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\197.bak'
WITH REPLACE
MOVE '197' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\HCN_PROD2.mdf',
MOVE '197_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\HCN_PROD2_log.ldf',
MOVE '197_Audit' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\HCN_PROD2AUDIT.ndf',
STATS=5
What might I be missing?
August 18, 2010 at 4:39 pm
Never mind... I had a "moment"... Sorry
Thanks for the help!
RESTORE DATABASE [HCN_PROD2] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\197.bak' WITH FILE = 1, MOVE N'HCN_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\HCN_PROD2.mdf', MOVE N'HCN_AUDIT' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\HCN_PROD2AUDIT.mdf', MOVE N'HCN_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\HCN_PROD2_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
August 19, 2010 at 3:48 am
no probs.
FILE =1 should not be necessary. If you are always backing up to the same backup file name ensure you use the init clause in the backup, otherwise it will be appending and the backup fie will grow very large.
after the restore you may also need to change the database owner and synch orphaned user ids.
---------------------------------------------------------------------
August 19, 2010 at 7:52 pm
just to add 50 Cents more to what george mentioned:
The restore would fail if it is not given exclusive access.
Add this before your restore:
alter database db_name_to_restore
set single_user with rollback immediate
waitfor delay '00:00:05'
alter database db_name_to_restore
set multi_user
-- from here starts restore code
-- sync orphaned users if any
HTH
Cheers !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
August 20, 2010 at 9:39 am
Excellent catch... thank you very much! :Whistling:
The DB owner should be okay (at least it appears to be) and I added another step in the SSIS package to synch orphan users...
October 23, 2012 at 2:54 am
you have to insert comma(",") after with replace..
October 23, 2012 at 2:56 am
Please note: 2 year old (resolved) thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply