July 14, 2014 at 8:27 am
I'm creating a module for our testing team to copy database in same server without using any wizard or providing any access to the server but to give them a webpage where they can copy live database to a testing database on the same server without bugging up the development team.
I cannot use WITH REPLACE since I need backup live db and copy to the test db,
We are using SQL 2012 Devloper
Any help would be appreciated...
-----------------------
USE MASTER
GO
DECLARE @Folder VARCHAR(MAX) = 'E:\MSSQLBackup\'
DECLARE @PathFile VARCHAR(MAX) = @Folder + 'SomeDB.bak'
DECLARE @PathDB VARCHAR(MAX) = @Folder + 'SomeDBTest.mdf'
DECLARE @PathLog VARCHAR(MAX) = @Folder + 'SomeDBTest_Log.ldf'
BACKUP DATABASE SomeDB TO DISK = @PathFile WITH FORMAT, COMPRESSION
Alter Database SomeDBTest SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE FILELISTONLY FROM DISK = @PathFile --found this online but its useless
RESTORE DATABASE SomeDBTest FROM DISK = @PathFile
WITH NORECOVERY,
MOVE 'SomeDBTest' TO @PathDB,
MOVE 'SomeDBTest_Log' TO @PathLog
ALTER DATABASE SomeDBTest SET MULTI_USER;
------------------------
Processed 257943 pages for database 'SomeDB', file 'SomeDB' on file 1.
Processed 2 pages for database 'SomeDB', file 'SomeDB_log' on file 1.
BACKUP DATABASE successfully processed 257945 pages in 1.255 seconds (1716.994 MB/sec).
(2 row(s) affected)
Msg 3154, Level 16, State 4, Line 13
The backup set holds a backup of a database other than the existing 'SomeDBTest' database.
Msg 3013, Level 16, State 1, Line 13
RESTORE DATABASE is terminating abnormally.
July 14, 2014 at 8:30 am
You need to use WITH REPLACE on the restore. Since you're overwriting the DB that's there, I don't understand why you say you can't use that option.
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
July 14, 2014 at 8:47 am
GilaMonster (7/14/2014)
You need to use WITH REPLACE on the restore. Since you're overwriting the DB that's there, I don't understand why you say you can't use that option.
This is the reason why I cannot use WITH REPLACE
Msg 1834, Level 16, State 1, Line 13
The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SomeDB.mdf' cannot be overwritten. It is being used by database 'SomeDB'.
Msg 3156, Level 16, State 4, Line 13
File 'SomeDB' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SomeDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 13
The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SomeDB_log.ldf' cannot be overwritten. It is being used by database 'SomeDB'.
Msg 3156, Level 16, State 4, Line 13
File 'SomeDB_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SomeDB_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 13
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 13
RESTORE DATABASE is terminating abnormally.
July 14, 2014 at 8:54 am
Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.
July 14, 2014 at 8:57 am
Then you need to use WITH MOVE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2014 at 9:02 am
Grant Fritchey (7/14/2014)
Then you need to use WITH MOVE.
Already doing it, please go through my post carefully
Regards
July 14, 2014 at 9:03 am
Hema kumar-337490 (7/14/2014)
Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.
That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?
July 14, 2014 at 9:09 am
sirvikasrana (7/14/2014)
Grant Fritchey (7/14/2014)
Then you need to use WITH MOVE.Already doing it, please go through my post carefully
Regards
You're specifying logical file names that don't exist. According to the error message, the logical file names are SomeDB and SomeDB_Log, but your MOVE clause references SomeDBTest and SomeDBTest_Log
You also need to restore WITH RECOVERY if you want the DB usable afterwards. Unless you're restoring log backups after, there's no reason to restore WITH NORECOVERY
Without the variables, this is, afaict, the statement you need to run
RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'
WITH RECOVERY, REPLACE,
MOVE 'SomeDB' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDB_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'
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
July 14, 2014 at 9:10 am
sirvikasrana (7/14/2014)
Hema kumar-337490 (7/14/2014)
Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?
The error is telling that the file is in use. That means that you need to have a different path or filename for the new database. Read through the error carefully.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2014 at 10:33 am
GilaMonster (7/14/2014)
sirvikasrana (7/14/2014)
Grant Fritchey (7/14/2014)
Then you need to use WITH MOVE.Already doing it, please go through my post carefully
Regards
You're specifying logical file names that don't exist. According to the error message, the logical file names are SomeDB and SomeDB_Log, but your MOVE clause references SomeDBTest and SomeDBTest_Log
You also need to restore WITH RECOVERY if you want the DB usable afterwards. Unless you're restoring log backups after, there's no reason to restore WITH NORECOVERY
Without the variables, this is, afaict, the statement you need to run
RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'WITH RECOVERY, REPLACE,
MOVE 'SomeDB' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDB_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'
Please check my code above I've specified the same file names which you have added in your code.. my code and your code is not working..
I failed but can you please create two databases having name SomeDB & SomeDBTest and try your and my code, let me know how well it worked...
July 14, 2014 at 10:35 am
Grant Fritchey (7/14/2014)
sirvikasrana (7/14/2014)
Hema kumar-337490 (7/14/2014)
Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?
The error is telling that the file is in use. That means that you need to have a different path or filename for the new database. Read through the error carefully.
WITH REPLACE is trying to replace 'SomeDB.mdf' (which I don't want) but I need to replace or create 'SomeDBTest.mdf'. How can I do this?
July 14, 2014 at 10:40 am
sirvikasrana (7/14/2014)
GilaMonster (7/14/2014)
sirvikasrana (7/14/2014)
Grant Fritchey (7/14/2014)
Then you need to use WITH MOVE.Already doing it, please go through my post carefully
Regards
You're specifying logical file names that don't exist. According to the error message, the logical file names are SomeDB and SomeDB_Log, but your MOVE clause references SomeDBTest and SomeDBTest_Log
You also need to restore WITH RECOVERY if you want the DB usable afterwards. Unless you're restoring log backups after, there's no reason to restore WITH NORECOVERY
Without the variables, this is, afaict, the statement you need to run
RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'WITH RECOVERY, REPLACE,
MOVE 'SomeDB' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDB_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'
Please check my code above I've specified the same file names which you have added in your code..
No they're not. You specified Test in the logical names, names which according to the errors you posted don't exist. If you're obsfucating the code, please don't, it makes things way harder to figure out.
sirvikasrana (7/14/2014)
RESTORE DATABASE SomeDBTest FROM DISK = @PathFileWITH NORECOVERY,
MOVE 'SomeDBTest' TO @PathDB,
MOVE 'SomeDBTest_Log' TO @PathLog
I have done this a number of time, the code I posted is what I would use. If it's not working, post the exact error messages (no obsfucation)
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
July 14, 2014 at 10:40 am
sirvikasrana (7/14/2014)
Grant Fritchey (7/14/2014)
sirvikasrana (7/14/2014)
Hema kumar-337490 (7/14/2014)
Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?
The error is telling that the file is in use. That means that you need to have a different path or filename for the new database. Read through the error carefully.
WITH REPLACE is trying to replace 'SomeDB.mdf' (which I don't want) but I need to replace or create 'SomeDBTest.mdf'. How can I do this?
Using a correct MOVE clause.
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
July 14, 2014 at 10:53 am
GilaMonster (7/14/2014)
sirvikasrana (7/14/2014)
GilaMonster (7/14/2014)
sirvikasrana (7/14/2014)
Grant Fritchey (7/14/2014)
Then you need to use WITH MOVE.Already doing it, please go through my post carefully
Regards
You're specifying logical file names that don't exist. According to the error message, the logical file names are SomeDB and SomeDB_Log, but your MOVE clause references SomeDBTest and SomeDBTest_Log
You also need to restore WITH RECOVERY if you want the DB usable afterwards. Unless you're restoring log backups after, there's no reason to restore WITH NORECOVERY
Without the variables, this is, afaict, the statement you need to run
RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'WITH RECOVERY, REPLACE,
MOVE 'SomeDB' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDB_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'
Please check my code above I've specified the same file names which you have added in your code..
No they're not. You specified Test in the logical names, names which according to the errors you posted don't exist. If you're obsfucating the code, please don't, it makes things way harder to figure out.
sirvikasrana (7/14/2014)
RESTORE DATABASE SomeDBTest FROM DISK = @PathFileWITH NORECOVERY,
MOVE 'SomeDBTest' TO @PathDB,
MOVE 'SomeDBTest_Log' TO @PathLog
I have done this a number of time, the code I posted is what I would use. If it's not working, post the exact error messages (no obsfucation)
See, let me clear here.. I want to replace SomeDBTest files not SomeDB files, I am taking backup of SombDB and want to restore it in SomeDBTest, why would I take backup of SomeDB and restore it back since I wanted to restore it into a new test database.
Your code worked on SomeDB files but I my requirement is to make it work on SomeDBTest not on SomeDB.
Here is my Code:
RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'
WITH RECOVERY, REPLACE,
MOVE 'SomeDBTest' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDBTest_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'
------------------------------
Msg 3234, Level 16, State 2, Line 2
Logical file 'SomeDBTest' is not part of database 'SomeDBTest'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
July 14, 2014 at 10:55 am
GilaMonster (7/14/2014)
sirvikasrana (7/14/2014)
Grant Fritchey (7/14/2014)
sirvikasrana (7/14/2014)
Hema kumar-337490 (7/14/2014)
Since you are trying to restore the DB on the same server and both source and destination databases exist exist in the same location. Its trying to recreate/restore the same files on the original location. Make sure data and log file names are different or restore it on a different path.That is what I'm trying to do, I've created two different database on same server with different files and using WITH MOVE. So how to do this correctly?
The error is telling that the file is in use. That means that you need to have a different path or filename for the new database. Read through the error carefully.
WITH REPLACE is trying to replace 'SomeDB.mdf' (which I don't want) but I need to replace or create 'SomeDBTest.mdf'. How can I do this?
Using a correct MOVE clause.
Is there any possible way to copy SomeDB to SomeDBTest ?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply