T-SQL (SS2K8) :: Unable To Restore Backup File To Different Database
May 28, 2014
I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.But the message I am getting is: The file 'D:SQLDataTomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master]
RESTORE DATABASE [DEV] FROM DISK = N'D:SQLBackupDEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:SQLDataTomsTest.mdf',
MOVE N'DEV_log' TO N'D:SQLDataTomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.
I took the backup of a database (called students) from one of the machine on our LAN and then tried to restore it on another machine but am unable to restore the database. Below are some details of the problem I am facing:
- The machine on which I am trying to restore the database (i.e. target box) previously had a database by the same name (no it was not a replica). I had deleted this database from the target box before trying to restore the database (of the same name and which was backed-up from the source box)
- When trying to restore (via EM) I get a VERY LONG dialog box with lots of details. Below are some details of what this dialog box says:
The title of the database is MS SQL-DMO (ODBC SQL State: 42000) and It says "The physical name 'C:Program Files.....datastudents.mdf' may be incorrect. File 'students_Data' cannot be restored to 'C:Program Files.....datastudents.mdf'. Use with MOVE to identify a valid location for this file.
And the dialog box provides similar details related to 'students.ldf' Can u please help me with this.
I took the backup of a database (called students) from one of the machine on our LAN and then tried to restore it on another machine but am unable to restore the database. Below are some details of the problem I am facing:
- The machine on which I am trying to restore the database (i.e. target box) previously had a database by the same name (no it was not a replica). I had deleted this database from the target box before trying to restore the database (of the same name and which was backed-up from the source box)
- When trying to restore (via EM) I get a VERY LONG dialog box with lots of details. Below are some details of what this dialog box says:
The title of the database is MS SQL-DMO (ODBC SQL State: 42000) and It says "The physical name 'C:Program Files.....datastudents.mdf' may be incorrect. File 'students_Data' cannot be restored to 'C:Program Files.....datastudents.mdf'. Use with MOVE to identify a valid location for this file.
And the dialog box provides similar details related to 'students.ldf' Can u please help me with this.
I am trying to restore a SQL Server 2005 database backup file.The size of the database backup is 5.51 GB. I specify the source as a device and then provide the path of the .bak file and press OK i get the error as follows
TITLE: Microsoft SQL Server Management Studio ------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The media family on device 'G:ITWeb DB BkupDB BkupNew BkupWSS_Content_ITWEB.bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476
So we do SQL 2005 Database backup daily. At one point, we have to restore the backup. However, we cannot restore it whatsoever; it keeps saying that the database is in use.
It is actually a SQL Database for a CRM Server. I stop the internet service, so CRM or any other instances cannot pull up information from the SQL Database. I try running sp_who and killing the processes that are still using the database, but those processes keep coming back again.
Here is the error that I keep getting.
Could anyone please help me on this one? Thanks in advanced!
I am using sql server 2005 and unable to restore backup file(.bak) file when i ry to restore it to other database it shows following error
restore failed for server (microsoft.sqlserver.smo) additional information: system.data.sqlclient.sqlerror : the media set has 2 media families but only 1 are provided.All members nust be provided.(microsoft.sqlserver.smo)
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write restore filelistonly from disk = 'path and filenam.bak' restore database. zy from disk = 'path and filename.bak' with replace, move..... move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Now i must restore a database from a backup file of MS SQL Sever 6.5,but this file is bad. When i restore database from this file, SQL Server tell me that the format of this file is not file format of SQL Server 6.5.So that I must repair this backup file firstly. I don't know database file format of SQL Server 6.5.
I have a user database which has 1 data file and 1 logfile. I did a complete backup of the database to a file on the disk drive, using Enterprise manager. Due to some reason, I had to drop the database. The only way I could restore it was by, 1. Create the database with the same name. 2. Restore the complete backup from the file device.
While doing so, I get the message "Backup set holds the backup of daatabase other than the existing 'userdbname' database. BAckup terminating."
Is it because the database was dropped and recreated. If I choose the option to overwrite, it restores successfully.
Is this normal and right way to do it? Is there any thing else that I need to take care of, while backup or restore?
hi i have restore database use backup file in another machine of same name of database there following error
TITLE: Microsoft SQL Server Management Studio ------------------------------
Restore failed for Server 'database name'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The backup set holds a backup of a database other than the existing 'dbname' database. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476
I am confused regarding why the default behavior of this option is to use file no 1 if the option is not specified? If I take two backups on different days, weeks, months, etc. and write them to the same Backup set, the second and most recent backup by the way gets the higher file number (position) as one would expect. What is incomprehensible to me is why the decision was made to restore the oldest backup from the backup set if you do not specify with file no? Seems like common sense that I would NEVER want to restore the oldest backup by default and would most-likely always want to restore the most recent backup by default!
If anyone has suggestions on how I could suggest that the behavior of this option be changed, I would appreciate it, as this has caused me much pain.
Hi everybody, On executing the RESTORE command of SQL Server to restore from a backup of 78.3 MB, the "Server Application Unavailable" error message comes up.The error message in the Application log is as follows:aspnet_wp.exe (PID: 2184) was recycled because memory consumption exceeded the 152 MB (60 percent of available RAM). However using Query Analyser of SQL Server I am able to restore the database. What is the solution to this problem?
I can run this example from SQL Book Online from sql query analyzer. I can build the TestDB database.
BACKUP DATABASE Northwind TO DISK = 'c:Northwind.bak' RESTORE FILELISTONLY FROM DISK = 'c:Northwind.bak' RESTORE DATABASE TestDB FROM DISK = 'c:Northwind.bak' WITH MOVE 'Northwind' TO 'c: est estdb.mdf', MOVE 'Northwind_log' TO 'c: est estdb.ldf' GO
But... When I build stored procedure and call it through VB6. I've got the gray database symbol along with message TestDB (Loading/Suspecting) Why I cannot run these commands through VB6
I have a full backup followed by transaction log every Monday, Wednesday and Friday, how can i restore this file using sql agent to automate restoration of backup files with different file-name.
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK' WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF', MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF', REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).
I have tried restoring and attaching options but nothing works :(
I also get the error saying
"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".
Please this is urgent. WIll be VERY grateful for your urgent reply.
I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).
I have tried restoring and attaching options but nothing works :(
I also get the error saying
"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".
Please this is really urgent. Will be VERY grateful for your urgent reply.
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC 2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original 3) Took the new database offline 4) deleted the MDF and LDF files of the new database 5) put "MyDB_0.LDF" in the place of the LDF file I just deleted 6) put the database back on-line 7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)" 8) tried to do Tail Log Backup with this command   BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak' WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3 Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
I tried to use backup and restore database tasks to restore backup file but it does not work. The backup file I tried to restore in SQL server 2000 is from somewhere else (from my friend) and saved in cd-rom, not the one I created before. How can I restore it to view in SQL server 2000 database? Can you show me step by step? Thanks for your help
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
We have the issue of wanting to restore an MSSQL database (.BAK) but are unable to "Connect" to the existing database; but we are able to "Start" the database currently.
When we are unable to perform a "Connect", then under "All Tasks" you don't get the option to "Restore Database"
I am hoping there is a way around this and still a possibility of doing a restore without having to do an entire install of MSSQL again and then a restore?
I have a production SQLServer 6.5 on Nt 3.51 SP4 that had problems two weeks ago, an I had to restore the backup of the previous night.
we are testing our disaster recovery procedures on another server, identical to the production one.
We have installed NT 3.51 SP 4 and SQLServer 6.5 in the same order and with the same configuration of the production server, but i can't restore on the test server the backup files that i have succesfully resotred on the production server.
we make the SQLServer back up on file ( and then we backup those files on tape with NT backup) so I don't think is a problem of HW Tape Bios or Compression because i have tried to resotre directly the files without taking them from a tape.
1) I have created a new database , without data, with the same devices, in megabyte, that I have on the production server. than from enterprise manager I have started the restore from file, and after two seconds everything stops, and I can't even shutdown the task but i MUST turn the server off using the power button.
when I start the server again there are no specific errors in the event log nor in the SQLServer error log. The db i was trying to restore is marked "loading" and it is not available.
This same procedure works perfectly on the production server.
2) I have then created another db on the test server, and I have succesfully tranferred ( but not resotred) the produciont db onto the test one, using the transfer menu on SQLEnterprise manager.
3) another strange thing is that I have tryed to expand the tempdb, which is only 2 mega by default, but whenever i try to expand it, using enterprise manager, only the LOG area of tempdb sucessfully expands, not the data area. I don't know if this behaviour is related to the unsuccesfull restore or if it is another problem.
i have checked the sort order and character set of the 2 servers and they are the same.
I have 2 questions, and I appreciate if somebody can help me to figure out the good way to do. Thanks a lot.
1/. If I want to restore tranlog backup file from linked server to SQL Server 2000, Is the database online or offline during that time (since I want to make sure db online for users, but not offline)
2/. Can I restore tranlog backup file from SQL Server 2000 to SQL Server 2005 database?
I need to restore a backup file which resites on a network share to my local Sql70 server. Is it possible to restore a database from a backup file on the network share? Thanks!
This script will read the contents of a DB backup file, and generate a restore command.
Set the value of parameter @backup_path to point to the backup file, run in Query Analyzer, cut/paste the output into another Query Analyzer window, modify as necessary, and run.
This is just a barebones script to demo how this can be done. Modify as necessary to meet your own needs.
Works in SQL 2000 and 7.0. May work in SQL 2005, but it is not tested.
-- Create Restore Database Command from DB Backup File
select [--Restore--]= case when a.Seq = 1 then @cr+ @cr+'restore database '+c.DatabaseName+ @cr+'from disk ='+@cr+@tab+''''+ @backup_path+''''+@cr+'with'+@cr else '' end+ @tab+'move '''+a.LogicalName+ '''to '''+a.PhysicalName+''' ,'+ case when a.Seq = b.Seq then @cr+@tab+'replace, stats = 5 , recovery' else '' end from #filelist a cross join ( select Seq = max(b1.Seq) from #filelist b1 ) b cross join ( select DatabaseName = max(c1.DatabaseName) from #header c1 ) c order by a.Seq go drop table #header drop table #filelist
I am trying to create sql code that restores a backup of a master database to a new database on the same server. It “seems” to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with “MODTRNMaster”
The databases which are created from the restores start with “M1_” and “M2_”. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster to disk = 'f:kupMODTRNMaster.bak'
backup database MODTRNMaster_IMG to disk = 'f:kupMODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC to disk = 'f:kupMODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM to disk = 'f:kupMODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. I’m hoping that there is just something simple that I’m overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master go
drop database M1_MSLH go
restore database M1_MSLH from disk = 'f:kupMODTRNMaster.bak' with move 'DEV5_Data' to 'f:mssqldataM1_MLSH.mdf', move 'MM' to 'f:mssqldataM1_MLSH_1.mdf', move 'AMB' to 'f:mssqldataM1_MLSH_2.mdf', move 'DM' to 'f:mssqldataM1_MLSH_3.mdf', move 'IMM' to 'f:mssqldataM1_MLSH_4.mdf', move 'ED' to 'f:mssqldataM1_MLSH_5.mdf', move 'DEV5_Log' to 'f:mssqllogM1_MLSH_log.ldf', recovery
1.) Taking a database out of an availability group, 2.) Setting the recovery to simple, 3.) Shrinking the log file, 4.) Setting the recovery mode back to full, 5.) Then backing it up.
I need to restore the file to my secondary server with replace and non recovery mode. I am having trouble performing that call? I have the code to reestablish the database to the availability group if I can get the restore feature working.Â
