Restore error

  • This might be the issue you are running into: http://support.microsoft.com/kb/843515

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • @ jeffrey...

    But my database restores starts from 10%, 20% , 30% ...then it stops in between....

    it is not that it doesnt start...but after sometime it throws error.

    The article explains for a unsuccessful backup not a restore which is my case.

    Sushant

    DBA

    Virgin Islands

    Regards
    Sushant Kumar
    MCTS,MCP

  • the restore script also failed with the same error.. 🙁

    I restored another database from the same prod server to dev server which went smoothly, but that one was just 1GB....This database is 145GB.....So, if hotfixes didnt match (prod server and dev server), that database shouldn't be restored??

    Is this the case of hotfix?

    Regards
    Sushant Kumar
    MCTS,MCP

  • sushantkumar1984 (7/16/2010)


    @ jeffrey...

    But my database restores starts from 10%, 20% , 30% ...then it stops in between....

    it is not that it doesnt start...but after sometime it throws error.

    The article explains for a unsuccessful backup not a restore which is my case.

    Sushant

    DBA

    Virgin Islands

    It does not matter that you are restoring as opposed to backing up the database. The article references the same error you are getting which is caused by a problem with the network configuration.

    There are other things you can look at - for example:

    Setting the LanmanWorkstation timeout values

    Enabling/Disabling TCP Chimney

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ... and then trying to restore it, then also I am receiving the same error as mentioned while i started the post....

    That is strange. The error message you reported in your first post was as follows:

    A transport level error has occured when receiving results from server(provider: TCP provider, error:0 The specified network name is no longer available)

    Is the 410 GB drive connected to the server that you are trying to restore the database on? If so, why would SQL Server raise a TCP provider error if the drive is local to the server?

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • @ Ray mond...

    Is the 410 GB drive connected to the server that you are trying to restore the database on? If so, why would SQL Server raise a TCP provider error if the drive is local to the server? [/b]

    Yes, the 410 GB drive is connected to the server on which i trying to restore.

    I dont know, why is it throwing the same error when it has the .bak file locally too.

    Regards
    Sushant Kumar
    MCTS,MCP

  • @ above all.....

    I made 2 changes

    1) in server properties-->connections-->remote query timeout changed from 600--> 0

    2) started the sql server browser (sql server configuration manager--> services)(it was off earlier)

    Now the restore is smoothly done....

    Thanks all of u for helping me throughtout this post...

    Sushant

    DBA

    Virgin islands

    Regards
    Sushant Kumar
    MCTS,MCP

  • sushantkumar1984 (7/19/2010)[hr2) started the sql server browser (sql server configuration manager--> services)(it was off earlier)

    Have you stopped it earlier intentionally or is it a newliy built server.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • It would be helpful for me to understand if u can send me the output of below command :

    RESTORE FILELISTONLY FROM DISK = 'completepath \ <your backup file name>'

    Also send the current free space on all the drives.... so that I can figure out what all the files can be stored on what locations....

  • @Bhuvnesh

    It was newly built server...so didn't check it earlier..

    @sumit

    I got through now...restored successfully after the 2 changes as mentioned above..

    Regards
    Sushant Kumar
    MCTS,MCP

  • Hi,

    Shrink the files at the source database and truncate all the log files, then take the back up, while taking backup you can share a folder at the destination server and give that shared path name while taking backup. and then you can restore the database in your destination server.

    Cheers!!

    Sushant

  • @ sushant

    It's not considered good practice to shrink a production database.

    If the database has been shrunk and new data is added then it has to grow again, probably at an inconvenient time. Grows and shrinks are expensive IO operations

    Also shrinking a database reorders data and index pages to fit into a smaller volume, hence you are fragmenting your indexes every time you shrink.

    Regards
    Sushant Kumar
    MCTS,MCP

  • source server= where is a backup

    destination server= where you want to restore database

    1. Create share on source server "share_name" - grant Everyone Read

    2. Grant read permissions on the source server folder to the SQL server service account of the destination server

    (If "network service", then you have to select the computer account of the destination server,

    if "system" - you have to change the SQL server service account of the destination server).

    3. on the destination server you can execute:

    RESTORE DATABASE xxx FROM

    DISK = N'\\source_server\share_name\device1.BAK',

    DISK = N'\\source_server\share_name\device2.BAK'

    ...

    WITH RECOVERY ,

    MOVE N'xxx_1' TO N'G:\Databases\xxx1.mdf',

    MOVE N'xxx_2' TO N'F:\Databases\xxx2.ndf',

    ...

  • or else you can do the same without shrinking your producation Database.

    Just share the path in ur destination server and take the backup by giving that and don't forget to make the backup to happen in compress mode.

    cheers!!

    sushant

  • sushant.ranahandol 23461 (8/17/2010)


    don't forget to make the backup to happen in compress mode.

    Compression in Sql 2005 ??

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply