I recieved 4 database's that were backed up from our client. They are using SQL Server 2000 and each of the files has no extension.
When attempting to restore a database of the same name in SQL Server Express 2k5 I recieve the following error.
System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided
Is there a way to restore the backup without the log backup?
Thank you
-Robert-
I backup a database at the begining of each month with a full and then do nightly diffs on it.
For the same database I run daily fulls and 10 minute log backups.
these two backups create / append to two different backup files.
The problem im having is that I cant restore the Differential backup set. SQL seems to restore the full just fine but alwasy throws an error when its about to start to retore the last diff. now forgive me but I clicked OK on the message and I cant find any record of the error in the logs but its something like:
"SQL cannot restore the database as the database has not been restored to the previous correct state"
is my 10 min TS log backups screwing up the DIff chain somehow?
this is really doing my head in. any help appreciated.
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
I am trying to set up a process where a differential backup is taken daily and applied to another DB. For some reason I cannot get the diff backup to restore. The full DB backup restored fine.
Server: Msg 3136, Level 16, State 1, Line 4
Cannot apply the differential backup on device 'diff' to database 'test'.
Server: Msg 3013, Level 16, State 1, Line 4
Backup or restore operation terminating abnormally.
Any suggestions? Thanks!
hey guyz..
i got this code.. its running... but i am not able to find the file that has been backed up by the program.. pls help.. if there is anything wrong with the codes pls help me
this code is for creating backups
Dim paramsBackup As String = " -Usa -P -q " & Chr(34) & "Backup database test to disk ='c: est.bak'" & Chr(34) & " -n "
Try
Process.Start("c:program filesmicrosoft sql server80 oolsinnosql.exe", paramsBackup)
MsgBox("Backuped")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
this code is for restoring
Dim paramsRestore As String = " -Usa -P -q " & Chr(34) & "restore database test from disk ='c: est.bak'" & Chr(34) & " -n "
Try
Process.Start("c:program filesmicrosoft sql server80 oolsinnosql.exe", paramsRestore)
MsgBox("Restored")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Guys,
In 2000, if I need to restore Transactional backups I have to do it one by one followed by Full Backup or Differntial backups.
I am wondering is there any way I can restore all Transactional files in One Batch in SQL Server 2005?
Many Thanks
SKR
SQL Server's Books On-line seems to hint that this can be done. Does anyone have any info or practical application of this? Can someone confirm that this is possible please.
Aquafortis
Until yesterday I had a server running SQL Server 2008 R2 - with all the SQL Server DB files on an attached disk array.
The server died - so I attached the disk array to a new server - and all the DB data files are visible there.
I installed SQL Server 2014 on the new server and am trying to work out how to point it at the existing database files.
I also have backups of the DB's - but they will take ages to copy over and restore - so it would be much easier to just use the db files. Should I restore the master db first (easy as its small)?
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
View 9 Replies View RelatedI'm working on a restore procedure for the case where all MDF filesare missing, but the LDF files are all intact. A full backup is doneevery 24 hours, and a log backup is done every 3 hours. Afterrestoring the last full + log backups, is it at all possible to usethe LDF files to recover data from that point up to a newer point intime?I've found a post which explains how to do this on SQL Server 2000<http://groups.google.com/group/comp...s.ms-sqlserver/browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4fails with the following error message:BACKUP LOG cannot be performed because there is no current databasebackup.
View 2 Replies View RelatedHi,
I am cleaning database backup history as "Delete history if 4 months old" for backup file, and "Delete history if 1 week old" for Log file back.
lets say, I have disaster after 3 months, and I recover database with data and log backup files. Will I be able to recover with 3 month old backup file with just 1 week Log file backup. As I am keeing my Log backups only for 1 week.
Thanks,
We have a set of databases some are fully read-only others have read-only file groups, is there any way to restore backups of these taken on an MSDE 2000 to an SQL Express 2005 instance?
When doing the inplace upgrade we change these to read-write before the upgrade and set them back after the upgrade.
These databases are used in the field by customers althought the controlled upgrade requires a backup before (and blocks if it fails) and tries a backup after if the post upgrade backup fails (due to disk space) we might need to recover from this odd situation.
The only solution I have is install MSDE some place restore to this then do the controlled upgrade again, any other ideas?
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View RelatedHello,
I think I am getting a similar problem to a previous post, I get this message when restoring a SQL 2005 DB.
Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
It was backed up using whatever tool is in the full version of Server 2005. I only have the express edition and to restore I have run the filelistonly to find the logical file name. I then run a query like:
restore database new name from disk ='file location and name'
with move
'logical file name for mdf' to 'new location',
move 'logical file for ldf' to 'new location'
I have done this many times before with both 2000 and 2005, although the backup is usually done on command backup database 'database name' to disk ='location and name'.
Please help!
Cheers
Matt
Hello,
I am taking the error message which is in the below side,while restoring a database.(I am using sql server 2005)
The backup set holds a backup of a database other than the existing 'db1' database. (db1 is the database name.)
Can you help me?
I need to move a database from one server to a new server. Right now, I only have database file on CD which is generated using the backup feature of MS SQL server.
What I did was I copied the file from CD to the harddisk of the new server.
Then I used the restore database, restore from disk, where I specified the location of the backup database file. When I began to restore, the error message I got was:
The file 'e:MSSQL7dataGTCSQL_data.mdf cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file. Backup or restore operation terminating abnormally.
The error message in the error.log of SQL server is:
BackupFileDesc::VerifyCreatability: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device e:MSSQL7DATAGTCIntranetSQL_dat.mdf.
What did I do wrong? How to use the WITH MOVE option? This is the first time I use MS SQL server. So please give me detailed instruction if posible.
Any suggestion are greatly appreciated.
Sheila
Hi !!
I have this problem when I dumped 10 tables to a new-created-dump device.
I dropped the tables and recreated it with the same layout as before.
When i try to load the tables again eith the command "load table from dumdevice" the first table works fine but the second table gets the error message:
"Schema differs between source table......."
Where the source table according to the eroor message is the first table that
I sucessfully loaded though I didn`t mentioned it in the command.
What can I do, Am I not allowed to dump several tables to the same dumpdevice !!!
Please Help
Pederico
Hi,
I have one backup file of one database, I want to restore the database to server using that backup file. But when I tried to restore the database using that backup file, the query executed successfully, but in management studio at the database it is showing the message like 'Restoring the database'. The database i am trying to restore is about 2.71gb memory. I executed the query 4hrs back but still it is showing same message that it is getting restored. Why it is showing like this. Please help me to solve this problem.
The query i used to restore the database is -
'RESTORE DATABASE [Everydayonline]
FROM DISK = N'D:BOOKSEverydayBackUp'
WITH
NORECOVERY,
NOUNLOAD,
STATS = 10,
MOVE 'ASPNETDB_983ed943e1fe49e3ae7fa189b823b238_DAT' TO 'D:SQLEverydayOnline.mdf',
MOVE 'ASPNETDB_TMP_log' TO 'D:SQLEverydayOnline_log.ldf'
GO'
I did the full backup of .7 TB database about 2 weeks ago and did full restore with norecovery mode and then i did differential backup of about 100 gb and tried to restore but it is showing following error :
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
While restoring the files are different than original database files
Please help
Please that anybody help me.
I have a phisical device called 'respaldo.bak' inside this file there are three backups of three different databases, The first one is database called 'innovasoft' the second one is 'modelo_de_datos' and the last one is 'Inversiones'.
If I perform a restore of the first one there is no problem, but, when i try to restore the second o third database i get the following error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'COMPUTO'. (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
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
El archivo lógico 'INNOVASOFT_Datos' no es parte de la base de datos 'PROBANDO'. Use RESTORE FILELISTONLY para enumerar los nombres de los archivos lógicos.
Fin anómalo de RESTORE DATABASE. (Microsoft SQL Server, Error: 3234)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3234&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I have to say that i want to restore the database with a different name that's why in the error says 'PROBANDO' , i'm really concern about this problem because it means that i have no backups for my data. I used to use this procedure on sql server 2000 every single day without problems, but on sql server 2005 it doesn't work properly.
Please help me.
Thanks in advance.
Hello
I am trying to restore a file from a file/filegroup backup from our live server to a test machine but keep getting the following error:
The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore.
The location of the files on the live server are different to where they will be on the test machine but from my understanding of the restore t-sql i thought it the move would locate them to where they should be? here is my restore command i am running:
RESTORE DATABASE TestDB
FILE = 'File1'
,file ='File2'
,file ='File3'
,file ='File4'
,file ='File5'
,file ='File6'
FROM disk = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupFileGroup.bak'
WITH norecovery,
MOVE 'File1' TO 'C:sqlarchivesFile1.ndf' ,
MOVE 'File2' TO 'C:sqlarchivesFile2.ndf' ,
MOVE 'File3' TO 'C:sqlarchivesFile3.ndf' ,
MOVE 'File4' TO 'C:sqlarchivesFile4.ndf' ,
MOVE 'File5' TO 'C:sqlarchivesFile5.ndf' ,
MOVE 'File6' TO 'C:sqlarchivesFile6.ndf',
I can not find any info about this error i am getting, can anyone point me in the direction of where to try and troubleshoot this message please?
Thanks
hello,
I need to archive a database and restore it on the same server but under a different databasename.
I first backup the database using SMO, now i want to restore it with the code below:
[code]
Private Sub RestoreDataBase(ByVal BackupFilePath As String, ByVal destinationDatabaseName As String, ByVal DatabaseFolder As String, ByVal DatabaseFileName As String, ByVal DatabaseLogFileName As String)
Dim myServer As New Server(My.Settings.SQLServer)
Dim myRestore As New Restore()
myRestore.Database = destinationDatabaseName
Dim currentDb As Database = myServer.Databases(destinationDatabaseName)
If currentDb IsNot Nothing Then
myServer.KillAllProcesses(destinationDatabaseName)
End If
myRestore.Devices.AddDevice(BackupFilePath, DeviceType.File)
Dim DataFileLocation As String = DatabaseFolder + "" + destinationDatabaseName + ".mdf"
Dim LogFileLocation As String = DatabaseFolder + "" + destinationDatabaseName + "_log.ldf"
myRestore.RelocateFiles.Add(New RelocateFile(DatabaseFileName, DataFileLocation))
myRestore.RelocateFiles.Add(New RelocateFile(DatabaseLogFileName, LogFileLocation))
myRestore.ReplaceDatabase = True
myRestore.PercentCompleteNotification = 7
AddHandler myRestore.PercentComplete, AddressOf PercentReturn
Try
myRestore.SqlRestore(myServer)
currentDb = myServer.Databases(destinationDatabaseName)
currentDb.SetOnline()
Catch ex As Exception
If ex.InnerException IsNot Nothing Then
MessageBox.Show(ex.Message & vbCrLf & ex.InnerException.Message, "Ex + InnerEx", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
MessageBox.Show(ex.Message, "Ex", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
End Try
End Sub
[/code]
When the program reaches the red line, he throws the following error:
Restore failed for Server 'NB_DELL_JWOSQLEXPRESS'.
System.Data.SqlClient.SqlError: Logical file 'TMP_MIXix_20080129.' is not part of database 'TMP_MIXix_20080129.'. Use RESTORE FILELISTONLY to list the logical file names.
The originale databasename is MIXix and I take a complete backup of it using the code below (WORKS):
[code]
Sub BackupDB(ByVal Filename As String)
Dim srv As New Server(My.Settings.SQLServer)
Dim bk As New Backup
Dim bdi As New BackupDeviceItem(Filename, DeviceType.File)
bk.Action = BackupActionType.Database
bk.BackupSetDescription = "Full backup of " & My.Settings.Catalog
bk.BackupSetName = My.Settings.Catalog & " backup"
bk.Database = My.Settings.Catalog
bk.Devices.Add(bdi)
bk.Incremental = False
bk.ExpirationDate = Now.Date
bk.LogTruncation = BackupTruncateLogType.Truncate
AddHandler bk.PercentComplete, AddressOf PercentReturn
bk.SqlBackup(srv)
bk.Devices.Remove(bdi)
bk = Nothing
srv = Nothing
GC.Collect()
End Sub
[/code]
Does anyone have an idea what I can do about it?
Friendly regards
When attempting to restore a database, I get the following error:
'Cannot open backup device, (filename).bak, Operating system error 5, access is denied.'
I get similar errors when attempting to attach the database file, it shows up in the Mngt. Tool as 'read only'.
This error does not occur when restoring the same .bak file to other pc's. On the problem pc, we have completely uninstalled and reinstalled Sql Express and Mngt. Tool Express. It occurs whether logged in as full administrator or other user.
Hi all.
I was just restoring one database and creating another concurrently on the same server. The restore took longer than the create due to the respective sizes.
After the create had finished (done in Query Analyzer) I went back to the restore to see how it was getting on (running in Enterprise Manager).
There was an error (which I didn't record exactly - DOH!) along the lines of "could not obtain a lock on the model database, restore terminating abnormally."
I guess the create had model locked while it was creating the default objects, but what was a *restore* doing needing the model database anyway?
Any comments would be interesting.
Andy Tynan
I m a student and i m doing a project on Datamining. I have to mine data of a company. the issue is that the company uses Microsoft SQL server as there DBMS. they backed up there data and gave it to me with schema (schema is separate and data backup is a backup generated through the backup option in SQL server Enterprise Manager). Now i created tables through query analyzer (i think) and then through enterprise manager i tried to restore that backup on my personal pc and i get an error .
the image is attached
see the image for the error
http://img.photobucket.com/albums/v54/kaboomagic/error.jpg
===================================
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)
===================================
any ideas?
this is actually a restore from a BAK file saved from the live server. we copied the bak file over and placed it on the test server hard drive, now we are trying to restore this using SSMS
Hi experts!
I am using SQL Server 2005. while restoring a database. I got following error:
"
An exception occured while executing a Transct-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Additional Information:
Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error:3205 )
"
Please guide me to resolve this problem.
Thanks
I am receiving the following error message when I am trying to restore a database in SQL Express 2005. Has anyone ever seen it before?
microsoft.sqlserver.express.connectionInfo
I have scheduled a database backup at 9 pm and 5 Transaction Log backups. The transaction Log backups happen at 11 am, 1 pm, 3 pm and 5 pm (device does not get initialized) and one at 11 pm where i initialize the backup device. now i have another server with the same database on it. I restore these backups on to this server by simple copying the files and then doing Restore ...From Device...Add file...option. I am able to restore the DB backup but when i try to restore the log backups it get the error msg -
'Specified file 'G:...' is out of sequence. current time stamp is Feb 9 2000 3:54 pm while dump was from Feb 9 2000 3 pm.'
When i am restoring the logs i start from the 1st backup buyt still i get this error. Can someone pls let me know what the problem is and how to resolve it. Thanks.
I was trying to restore a transaction log when I got this.
What does this mean? I can't find anything by Googling on the
'fixed' parts of the message.
The file 'xx' cannot be moved by this RESTORE operation
Hi,
I have backup of data from SQL Server 7.0 and now when i'm trying to restore it into SQL Express 2005, I'm getting following error......
--------------------
Msg 3154, Level 16, State 2, Line 1
The backup set holds a backup of a database other than the existing 'GOSLDW' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
--------------------
Here is SQL i'm using to restore database,
RESTORE DATABASE GOSLDW
FROM DISK = 'C:sqlserverDataGOSLDW'
WITH MOVE 'GOSLDW' TO 'C:sqlserverDataGOSLDW.mdf',
MOVE 'GOSLDW' TO 'C:sqlserverDataGOSLDW.ldf'
GO
Why i'm getting this error? Am i missing anything here?
Thanks in advance for your HELP.
- R
I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)
Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.
Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.
Any advice on how to get this thing finished out?
Hi,
I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task?
Any suggestions?
Thanks,
Dale
What backups options do you have if you do not have a tape drive connected to your SQL 7.0 Server ? Can you run a SQL Maintenance Plan and backup the databases to a remote server. If not, can ARCServe 2000 backup the SQL backups to another server with a tape drive ?
View 1 Replies View Related