Database file or filegroup method was used to perform the backup and now i tried to restore that into different server by using SQL Server Enterprise Manager.
I got this error message:
"The database you are attempting to restore was backed up under a diffrent sort order ID (54) than the one currently running on this server (52), and at least one of them is a non-binary sort order. Backup or restore operation terminating abnormally."
Why i got this error message? And what is "backed up under a diffrent sort order ID (54)" ?
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.
My backup file is BAK FILE and it's in D:MSSQL7/Backup/Sales.BAK.
I want to restore that into a different server in D Drive. For that i created a database called "RestoreB". (RestoreD_Data.MDF and RestoreB_Log.LDF). I tried to restore using Enterprise manager, but i got error message.
"File 'Sales_BAK' is not a database file for database 'RestoreB'. Backup or restore operation terminating abnormally'
How do write the statement to restore that file? Anyone can help me to restore that file...
I want restore the backup files. This backup was done by somebody else. So i don't know about the sort order, unicode collation and character set... of that files. How do i find those information to restore?
I got only .mdf(not .ldf) file from server people after server crash.and when i am trying to attach the db using sql server 2005 management studio It shows an error message..
quote: Directory lookup for the file "E:Program FilesMicrosoft SQL ServerMSSQL$WEBSERVER39dataMastdb_hr_log.LDF" failed with the operating system error 3(The system cannot find the path specified.). (.Net SqlClient Data Provider)
We have a production server and a bunch of development servers. TheProduction server is located in a NOC (Network Operations Center)facility and is physically unaccessible on a regular basis. There areDTS packages on the production server which we'd like to pull down toour Dev servers.I was able to save the DTS package to a file and downloaded it to ourDev Servers. But how in the world do I restore it?? I'm a fairlytechnical guy and for the life of me, I'm not able to figure this oneout! How do I restore the DTS package which has been stored into a..dts file?Please help.Thank you in advance,-Umar.
I have a full database backup in a .bak file which includes both mdf and ldf parts. Unfortunatly when I try to restore the database the ldf it requires far more hard drive space than I have avaliable.
Is there any way to restore the database minus the log?
Or, is there any way to split the .bak file to the mdf and lfg files and restore just the mdf?
I cannot do another backup, or trim and backup. This .bak file is all I have to work with.
If a SQL 2000 server instance is destroyed, but the database file (*.mdf) is saved on the disk, can a database within a newly created server instance be built on the saved file?
If I attempt to create a new database and choose the saved mdf file as the data file, then the server refuses to accept this file. Is there a way to create the new database and make it read the standalone mdf file?
I am trying to create a scheduled task taht will restore a database from a backup file. I do not store my backups on a backup device, but on a local disk. To restore a DB from a backup device, the following statement will work: "Load DBName from BackupDeviceName". Does any know what statement to use to restore from a file, if the file is "E:DBName_db_dump_199909272220".
Is it possible to restore a database with only a .bak file?? When I try to restore the system comes up with 2 paths where probably the original .mdf and .ldf files existed, but on my system they don't exist. How can I avoid this problem? (it's not possible to get the ldf and mdf files anymore...)
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 have a scenario where I am trying to set up multiple database instances for multiple test/development environment(s) for each group where the test/dev environment will contain a copy of what was in the production environment.. The test/dev environment can be refreshed on demand based on the prior night's full backup of the production environment.. This is good for our web developers and for training purposes, as the test environment(s) can be played around with, and will retain data for as long as the developers/testers/trainers need it, and then can be refreshed to the most current data when everyone in the group decides they want it refreshed...
Normally, this works out well...
However I am having a file size issue...
The production database was pre-allocated (a long time ago) to a large file size (probably to reduce external fragmentation).... So even though the backup file is only 5GB, the production database file itself is something like 40GB... I believe the production database has a maintainance plan on it already that rebuilds the indicies each weekend, etc...
Anyhow, the problem is that when I restore the 5GB database back into a newly created database file, the file expands all the way up to 40GB again, even though the backup file is 5GB...
Normally this would be fine, the problem is that I am trying to create multiple environments, and I do not have the disk space on my test/dev server for 40GB (plus another 15GB or so for the transaction log) multiplied by each of my test/dev environments... It would be much nicer if I could get this down to 5GB (or heck, even 10GB), since I know for sure that the total amount of data in the database doesnt exceed 5GB, and I have plenty of space on my disk for 5 (or 10) GB multiplied by each of the environments I want to create...
I have tried DBCC SHRINKDB and I have tried DBCC SHRINKFILE with the truncate after the restore, which seems to work but doesn't....
I have also tried to go into the database properties and change the "initial size" but that doesnt do anything etiher
Is there any way to get this file back down to a manageable size after the restore??
Or better yet, is there a special method to restore the database so it wont 'expand' back out to 40GB in the first place??? Perhaps some option to tell the restore process that even though the source database had a 40GB pre-allocation, that the database I am restoring into doesn't need to be pre-allocated??
Hi, I have Sql server management studio express install on one pc with windows server 2003 os , working ok. I also installed the same on my vista machine.
I use backup tool to store the back file from the windows server 2003 and to restore it back in the vista.
After connect to the SQLEXPRESS instance in the management studio I right click the Database folder ->"Restore Database"->To database "xxx" (same name as was in the prev os) From device->...->backup media - file , Add - choose the file with .back extension -> OK
In this stage there are 2 windows: "Sql server windows NT has stop working" And the collecting information for ms to help me.. With 3 files: C:ProgramDataMicrosoftWindowsWERReportQueueReport17f38cd5WER80EE.tmp.version.txt C:ProgramDataMicrosoftWindowsWERReportQueueReport17f38cd5WER80EF.tmp.appcompat.txt C:ProgramDataMicrosoftWindowsWERReportQueueReport17f38cd5WER8C3C.tmp.mdmp
After click cancel , the management studio popup exception window with those details: TITLE: Microsoft SQL Server Management Studio Express ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo) ------------------------------ ADDITIONAL INFORMATION: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ Exception details: =================================== An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo) ------------------------------ Program Location: at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand) at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query) at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSqlWithResults(Server server, String cmd) at Microsoft.SqlServer.Management.Smo.Restore.ReadBackupHeader(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.buttonSelectDevice_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) =================================== A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (.Net SqlClient Data Provider) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476 ------------------------------ Server Name: MY-DEVPCSQLEXPRESS Error Number: 64 Severity: 20 State: 0
------------------------------ Program Location: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.ReadBuffer() at System.Data.SqlClient.TdsParserStateObject.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
after this I can see in the SQL server configuration manager that the service of "SQL Server (SQLEXPRESS)" is stopped
I try to play with the protocols for sqlexpress enable and disable some of them , using only tcp etc etc , nothing work.
product version details: 1. Microsoft SQL Server Management Studio Express 9.00.2047.00 Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205) Microsoft MSXML 3.0 5.0 6.0 Microsoft Internet Explorer 7.0.6000.16473 Microsoft .NET Framework 2.0.50727.312 Operating System 6.0.6000 2. SQL Server Configuration Manager , Microsoft Corporation , Version: 2005.090.3042.00 all opertaions under administrator account
Hello, I had backed up a database from SQL 2000, just before I format my computer. Now I have installed SQL 2005 and trying to restore that database to it, it doesn't work and I get the following error when doing that
===================================
Restore failed for Server 'localhostSQLEXPRESS'. (Microsoft.SqlServer.Express.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
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()
===================================
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing '2for1' database. (Microsoft.SqlServer.Express.Smo)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages) at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries) at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
I need an application with asp.net that let me restore database by getting .bak file and database name and database user and user password, my database is SQL Server 2005.
I have to do a database restore from tape from a couple of months ago. When I look at the SQL scheduled task history all databases were sucesfully backed up to a .DAT file. However, when I look at the .DAT file which I have just retrieved from tape - the database which I am looking for isn't listed under the backup information, so I can't choose it to carry out the restore.
DOES ANYONE KNOW HOW I CAN RESTORE A SQL7 DATABASE WITH JUST THE DATA FILE? THERE IS NO GOOD BACKUP OF THE DATABASE AND THE LOG FILE IS MISSING. THE CLIENT THOUGHT THEY WHERE BACKING UP THE DATABASE BACKUP FILE TO TAPE, BUT THEY WHERE NOT. THE BACKUP FILE WAS BEING OVERWRITTEN EVERY NIGHT. THE LAST NIGHTS BACKUP DID NOT GET TO FINISH TO COMPLETION. PLEASE, ANY INPUT WOULD BE APPRECIATED. THANKS, TIM
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 a Database A in production with 12GB as data file and 8 GB as log file. How do I restore this db in Development with a smaller log file, say 1GB? I can't shrink the log file or anything in production. What is the best way to restore in Dev with a smaller log file?
I posted this problem last week and still couldn't get this to work. I have a backed up copy of a production db that i want to test. The production server is using server 7 white i have 2000 at my desktop. How can i get this to work ? I am beginning to 'lose' it. Any help would be greatly appreciated. I would like to complete this test today. Thanks
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 have been trying to use DMO(C++) to backup a database and then restore from that backup to a new db name AND physical data and log files, with no success. I have been able to successfully restore the backup to the same database and change the physical file names using the RelocateFiles property on the Restore object, but not change both. Any help would be greatly appreciated.
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.
Hello,I need to restore the Master DB in my SQL 2k server (with sp3). Theproblem is that i don't have SQL backups of this file I only havegeneral backup of the entire c: drive i made using NTBackup. So itried all I know and succesfully run the rebuildm utility. Now when Itry to restore it from the general backup (not SQL backup) using"restore database master from gen_backup" i got error: "The file ondevice 'gen_backup' is not a valid Microsoft Tape Format backup set."RESTORE DATABASE is terminating abnormally.Any ideas how I can restore it?
I'm running the isqlw.exe command from a console app. The output log file is telling me access is denied to my My Documents folder (where the .bak file is) to do the restore. It keeps erroring and teminating the RESTORE command. It's not read only or anything (not that I'd think that would necessarily affect it) and I don't know why it would throw an OS access denied error on any part of my computer for me since I'm logged into the computer as an admin and I'm using the sa login info in the isqlw command.
I have backup my database with "file and filegroup"selected. I have dropped my database by accident. is it possible to restore my data back from "file and filegroup".bak??
One of our database came to Restoring mode. I suddenly stop my SQL service and Copied only MDF files again Started SQL service ,unexpectedly i dropped the Database. Now i cant able to attach the database only with my MDF file.
I tried below Scripts. All scripts shows same error.
EXEC sp_attach_single_file_db @dbname='PhoenixPolice', @physname=N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf' GO CREATE DATABASE PhoenixPolice ON Â Â (NAME = N'PhoenixPolice', Â Â FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf') FOR ATTACH_REBUILD_LOG GO CREATE DATABASE phoenixPolice ON (FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf') FOR ATTACH
All scripts shows below error
File activation failure. The physical file name "F:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAPhoenixPolice_1.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'PhoenixPolice'. CREATE DATABASE is aborted.
as indicated by my stupid question, I am very new to sql. our vrsion is 2000 and I'm talking about in enterprise manager, the database that was created is not showing up in the list of db. Although I can see the file in explorer.
The problem I€™m having is when I try to attach the database €œmailarchive3Q2007_data.mdf€? it is also looking for the log file €œmailarchive3Q2007_log.ldf€? . The log file was removed by someone else off our system. I have a backup of the file but it is too large to restore now (160 gig) when the system was first set up the recovery model was not set to simple so the log just grew till it filled up our drive. I no longer have the drive space necessary to restore the log file and shrink it. So what do I do now? I need some kind of €œmailarchive3Q2007_log.ldf€? file to attach the database in enterprise manager.
Hi SQL server experts, I'm trying to restore from a backup file. I need to restore the datafile and logfile to different names. I modified the options page, changed the 'Restored As' file name. I clicked on the General page and Clicked on the Options page again, the old values came back. How do I handle this? Thanks in advance. Pingx
Hi Everybody,I use MS SQL server 2000 enterprise manager on a windows server 2003.I created a new database in enterprise manager, and try to restore the new database from a .bak file. I went to ---->restore database. I checked "from device" and select the right path to the .bak file.Then I click ok, but there is an error pop up." tend to override existing database", so I went to --->option, and checked "force override existing database". then I click ok, but get an error look like this http://ewwa.lo7.net/error.JPGI thought it was a problem with my .bak file. So I try to use another .bak file which I am sure it is 100% good. It prompt the restore is sucessfully. But when I checked the db, there nothing there(No tables).I think there must be something wrong with my enterprise manager's configuration.Do you know what happen with my enterprise manager? Thank you a lot in advance:)!!
