March 23, 2009 at 10:41 am
Thanks for taking the time to ready my post. We have a situation where we have 2 servers, 1 being our test server and the other being our production server. We are a small team, who all have admin access rights to the db. This is being changed, so that we go through 1 who will act as the DBA, and review the scripts etc to ensure constancy, security, best practice etc.
To move to this more locked down approach we are in need of syncing up our test server with the DB's on our production server. We will be doing the following to achive;
1. Backup Test DB's
2. Backup Production DB's
3. Rename existing Test DB's (i.e AdventureWorks to AdventureWorks_Archive)
4. Copy the .bak files from Test Server File System to Production File System (manual)
5. Restoring the Production DB's onto the Test Server
Point 3 is to give the team members time to script/get anything they need from the old Test DB's but only for 1 month. After this time we will drop the old Test DB's from the Test Server.
I am using the following script;
IF @@SERVERNAME = 'PRODUCTION\SQLEXPRESS'
RESTORE DATABASE [LIVE1]
FROM DISK = 'C:\Backup\LIVE.bak'
WITH RECOVERY
GO
--CHECK DATABASE INTEGRITY
IF @@SERVERNAME = 'PRODUCTION\SQLEXPRESS'
DBCC CHECKDB (LIVE)
GO
But get the following errors:
Msg 1834, Level 16, State 1, Line 2
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LIVE.mdf' cannot be overwritten. It is being used by database 'LIVE_archive'.
Msg 3156, Level 16, State 4, Line 2
File 'LIVE' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LIVE.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 2
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LIVE_log.ldf' cannot be overwritten. It is being used by database 'LIVE_archive'.
Msg 3156, Level 16, State 4, Line 2
File 'LIVE_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LIVE_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Looking at books online, I am getting guided towards the WITH MOVE command because although I renamed the Old test DB's, the mdf name doesn't change and that the problem I am getting is that this .mdf already exists and is in use.
I tried:
restore database LIVE
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LIVE.bak'
with move 'LIVE' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LIVE.mdf'
but still get errors...
Could anyone give me some more specific guidance with what is happening and any alterations I would need with my restore script. FYI we don't need to worry about the transaction log as this is a reporting DB only.
Thank you in advance.
March 23, 2009 at 2:24 pm
You'll need to make the obvious database/mdf/ldf/path name changes. Look up restore database in BOL for more options.
RESTORE DATABASE [LIVE1]
FROM DISK = 'C:\Backup\LIVE.bak'
WITH RECOVERY,
MOVE 'Live_data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Livedata.mdf',
MOVE 'Live_Log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\LiveLog.ldf'
-- You can't be late until you show up.
March 23, 2009 at 2:43 pm
You need to specify :
OR new file locations as proposed above.
OR use the 'replace' keyword to overwrite an existing database !
I advise to always specify the target file locations, so any dba will see for sure where the files are designated to !
There may be local server reasons to move around with the files, so if you specify them in your restore script, that will not interfere with ongoing operations.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply