Hi all,
I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.
I have tried giving the new database the same name as the original and a different name to the original database
(Below is the scripted T-SQL that I get from the DB Admin tool
RESTORE DATABASE [Fers_Production]
FILE = N'FERS_Production_dat',
FILE = N'FERS_Production_log'
FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO
When I run this I get the following error.
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Fers_Production' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.
Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.
Any help is much appriciated, many thanks
Derek
Hi all,
Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)
TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
A system assertion check has failed. Check the SQL Server error log for details
Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
Location: IndexDataSet.cpp:12001
Expression: retCode == INSERT_SUCCESSFUL
SPID: 53
Process ID: 1092 (Microsoft SQL Server, Error: 3624)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476
Unfortunately the link that MS provide says there is no aditional info.
Thanks
Derek
|||
The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file.
|||
Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.
I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.
Thanks for your help.
Derek
|||I have merged these threads, as the error seems to be the same in both cases.|||Derek,
Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.
use fers_production
go
Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
go
Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
go
Then restart SQL Server after you have done this.
|||
Thanks again Andy,
I have been caught up with other things hence the delay in my saying thanks.
I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.
Cheers
Derek
|||
Backup File = mydatabase.bak
1. Run Microsoft SQL Server Management Studio application.
2. If mydatabase is in Databases : delete mydatabase.
3. Right Click to Databases and select Restore Database ....
4. Destination for restore -> To database: mydatabase
Source for restore -> select From device -> Specify the backup media and select the backup sets to restore
Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location
(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension
ex.:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).
5. Press OK button.
Have fun!
|||
Hi,
I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
Here is the description of my problem,
I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error
System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.
I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
Any comments?
Regards,
Jacek
|||Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)
I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.
RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO
Barry many thanks for this!
I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.
I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.
I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.
--live copy
use tempdb
create database Platinum
go
alter database Platinum set single_user with rollback immediate
go
alter database Platinum set multi_user with rollback immediate
go
--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO
ALTER database Platinum set recovery SIMPLE
GO
--Training Copy exactly the same copy
use tempdb
create database PlatinumTraining
go
alter database PlatinumTraining set single_user with rollback immediate
go
alter database PlatinumTraining set multi_user with rollback immediate
go
RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO
ALTER database PlatinumTraining set recovery SIMPLE
GO
"The backup set holds a backup of a database other than the existing 'Fers_Production' database."
Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.
No comments:
Post a Comment