Database Restore - To Local Machine.

Discussion in 'Databases' started by rvooys, Mar 2, 2010.

  1. Since I cannot get the generate scripts to work, I tried your new feature.

    I backed up the database on the server, copied to file to my machine via FTP.

    I created a new Database on my Local server.

    I went to restore, and as I add the media, I get the following error.


    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    ADDITIONAL INFORMATION:

    The media family on device 'C:\DB_2589_blackheart_backup.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?Prod...53&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    ===================================

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.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)

    ===================================

    The media family on device 'C:\DB_2589_blackheart_backup.bak' is incorrectly formed. SQL Server cannot process this media family.
    RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?Prod...53&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

    ------------------------------
    Server Name: RVOOYS-LAPTOP\SQLEXPRESS
    Error Number: 3241
    Severity: 16
    State: 7
    Line Number: 1


    ------------------------------
    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.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)
     
  2. Ray

    Ray

    What version of SQL server are you running on your computer when you performed the backup? The error message may also mean that the backup file may have gotten corrupted. It can be due to FTP when you are uploading it to our server. Try running a new backup on yoru computer and uploading the backup file again.
     
  3. I have the similar question as downloading the database backup and try to restore on my local machine. My SQL Server version is 2008 R2 ran on Windows Server 2008.

    Here is the error message, please advise:



    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    ADDITIONAL INFORMATION:

    The media family on device 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB_myaccount_backup.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?Prod...53&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------
     
  4. Ray

    Ray

    Last edited by a moderator: Oct 14, 2015
  5. Actually what I'm trying to do are bulk insert, download the bak file and restore on my local machine...etc. It seems like we could have a database and upload to the server but not the other way around. At least that is the impression I got from the post.

    Please let me know how I can achieve the task without uploading a new database every time.

    Thanks!
     
  6. Ray

    Ray

    Last edited by a moderator: Oct 14, 2015
  7. I'm afraid I didn't make myself clear, so let me explain it again. From my understanding (as well as reading through the kb article), Winhost cannot provide support for using the database backup on your local system.

    And also " If you create a backup on your local system and attempt to restore it to the live database the restoration may fail."

    These told me that I can't either use the bak file to restore the database in my local machine, nor create a bak file to restore to the server.

    Did I miss anything?

    Thanks in advance,
    Jim
     
  8. Ray

    Ray

    We state a disclaimer, because every person can setup their own database differently. But in general if you create a backup file off your own local computer and you get a .bak file off it, (in general) is should work with the Restore tool in the control panel.

    In general, if you use the Backup tool in the control panel, it will initiate a backup off our SQL server and it will place a .bak file to your Winhost root. Then you should be able to get that .bak file our Winhost SQL server generated for you and restore it on your own local database on your own computer. This is generally speaking. But once again we really cannot guarantee it because some people likes to get really complicated with their database, and it becomes impossible for us to troubleshoot something when the failure is occurring on the customer local database on their own computer.

    Either way, Bulk Insert is not supported or enabled on our SQL server so you cannot run bulk insert. You can run the Microsoft Publishing Wizard which will script your database and all its data and objects into a .sql file and run that .sql file against your own local database on you own computer. That option is totally up to you.
     
    Last edited by a moderator: Oct 14, 2015
  9. First of all, thank you Ray for the explanation. I understand that for hosting site you can't give up too much permissions. And I also appreciate that you keep reply me in a timely manner.

    Here is the first step I take to try to restore the bak file from backup on the site. but I got the following message:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    ADDITIONAL INFORMATION:

    The media family on device 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB_10451_stepupfunds_backup.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?Prod...53&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    This is similar to what I got in my first post here. Therefore, is creating the new database locally and restore it in bak format the only way I could have the local copy and permission to perform bulk insert and such function? The script one is for publishing the database to the server, which is not what I am asking.

    Thanks,
    Jim
     
  10. Ray

    Ray

    What are the steps you are taking to restore the backup database generated from our system to your own local database?

    The Database Publishing Wizard should work on our SQL 2008 server. You should be able to connect to our SQL 2008 Server and go to your own Database on our SQL 2008 Server, running the Database Publishing Wizard against it, save the .sql file it creates, then go to your own local database and run it against your own local database.
     
  11. I restore the bak file which I download from the Winhost ftp. Using Microsoft SQL Server Management Studio, click on the "Databases" of Local machine Server in the Object Server and "Restore database".

    Is this what you're asking?

    I had a online database already, and it contained the data I don't want to build from scratch.
     
  12. Ray

    Ray

    I just ran a test myself on my end, it does create a new database when I run the restore procedure. If you want a single database you'll need to restore the backup to a new database and run the export tool to the target database. This should move all of the objects and data you have on the restored database to the target database, than you can delete the database when you no longer need it.
     
  13. I wanted to restore the backup but I encountered the error. How could I solve it? I understand what you said, but it didn't help me as I can't restore the bak file from the server.
     
  14. Ray

    Ray

    When you click on restore, the restore database window should appear. In the "To Database" drop down box what are you choosing?
     
  15. I tried my db name on the site or leave it blank. No success.
     
  16. Ray

    Ray

    First, complete the "From Device" portion and navigate to the backedup database. Once you have done that, then go to drop down next to "To Database". Click on the drop down to display a list of names.

    You should find a database name that is similar to your Winhost database. It should have a format of DB_XXX_XXXX. Chose that and click OK.
     
    Last edited by a moderator: Oct 14, 2015
  17. This time I did exactly what you said. But it showed the error message first:


    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    ADDITIONAL INFORMATION:

    The media family on device 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB***************.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?Prod...53&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    Thus I didn't get to enter the DB name, which I always did before choose the bak file from device before.
     
  18. Ray

    Ray

    Do you have some screenshots you can show us? Its becoming too difficult to walk you through this without me seeing what you are seeing.
     
  19. Ray

    Ray

    Send me a screenshot of the drop down box for "To database" after you choose the target backup location. Also on your screenshot I see databases with the name "C:\.....". Thats odd it shouldn't be like that.
     
  20. Ray

    Ray

    Also what version of SSMS are you running? You may want to try downloading the .bak again from our server. It could be that the first download corrupted the file.
     

Share This Page