Results 1 to 5 of 5

Thread: Restoring a database from a different server

  1. #1
    Praveena Guest

    Restoring a database from a different server

    Hi!
    I attempted to backup a database on server x and restore it on a database on server y. The details are as follows.

    Server X
    Database name : dbx
    Size :263.88MB (Unrestricted filegrowth option set)
    Logical files : dbx_data, dbx_log
    Physical files : e:mssql7datadbx_data.mdf, f:databaselogsdbx_log.ldf

    Server Y
    Database name : dby
    Size :1MB (Unrestricted filegrowth option set)
    Logical files : dby_data, dby_log
    Physical files : d:mssql7datadby_data.mdf, d:mssql7datadby_log.ldf


    I created a full database backup of dbx on server x at the location, f:ackupsdbx_083099_10.30AM. The size of the file is 272MB. I zipped the file. Copied the file, dbx_083099_10.30AM.xip over to d:ackups of server y. I unzipped the file at server y to the location, d:ackupsdbx_083099_10.30AMdbx_083099_10.30AM.

    I now tried to restore this backup on server y using the statement,
    restore database dby from disk = 'd:ackupsdbx_083099_10.30AMdbx_083099_10.3 0AM
    with replace,
    move 'dbx_data' to 'd:mssql7datadby_data.mdf',
    move 'dbx_log' to 'd:mssql7datadby_log.ldf'


    I get the following errors :

    Server: Msg 3257, Level 16, State 1, Line 1
    There is insufficient free space on disk volume 'd:' to create the database. The database requires 3219128320 additional free bytes, while only 2047102976 bytes are available.
    Server: Msg 3013, Level 16, State 1, Line 1
    Backup or restore operation terminating abnormally.

    Error log shows,
    1999-08-30 10:28:54.68 kernel BackupDiskFile::OpenMedia: Backup device 'd:ackupdbx_083099_10.30AMdbx_083099_10.30AM& #39; failed to open. Operating system error = 1326(Logon failure: unknown user name or bad password.).

    (or)

    Cannot open backup device, 'd:ackupdbx_083099_10.30AMdbx_083099_10.30AM& #39;. Device error or device offline. See SQL Server error log for more info.
    Backup or restore operation terminated abnormally.

    How can I solve this problem ?

    Praveena


  2. #2
    Diana Williams Guest

    Restoring a database from a different server (reply)

    There is a much easier way to restore a database from one server to the other. What you need to do is first, make sure there is enough space on your hard drive that you are restoring to. Second, using the xp_cmdshell in Query Analyzer (or at the DOS command prompt), mape your drive such as:

    EXEC xp_cmdshell 'net use x: \servernamed$ admin /usersisql'

    Then at the server you want to restore to, in your SQL statement or in the visual restore box, use 'x' as the drive letter, then the actual path of where the backup file or device is. Then restore!! It works great. I use it every night to restore to our standby server.

    Any questions, e-mail me.


    ------------
    Praveena at 8/30/99 5:25:28 PM

    Hi!
    I attempted to backup a database on server x and restore it on a database on server y. The details are as follows.

    Server X
    Database name : dbx
    Size :263.88MB (Unrestricted filegrowth option set)
    Logical files : dbx_data, dbx_log
    Physical files : e:mssql7datadbx_data.mdf, f:databaselogsdbx_log.ldf

    Server Y
    Database name : dby
    Size :1MB (Unrestricted filegrowth option set)
    Logical files : dby_data, dby_log
    Physical files : d:mssql7datadby_data.mdf, d:mssql7datadby_log.ldf


    I created a full database backup of dbx on server x at the location, f:ackupsdbx_083099_10.30AM. The size of the file is 272MB. I zipped the file. Copied the file, dbx_083099_10.30AM.xip over to d:ackups of server y. I unzipped the file at server y to the location, d:ackupsdbx_083099_10.30AMdbx_083099_10.30AM.

    I now tried to restore this backup on server y using the statement,
    restore database dby from disk = 'd:ackupsdbx_083099_10.30AMdbx_083099_10.3 0AM
    with replace,
    move 'dbx_data' to 'd:mssql7datadby_data.mdf',
    move 'dbx_log' to 'd:mssql7datadby_log.ldf'


    I get the following errors :

    Server: Msg 3257, Level 16, State 1, Line 1
    There is insufficient free space on disk volume 'd:' to create the database. The database requires 3219128320 additional free bytes, while only 2047102976 bytes are available.
    Server: Msg 3013, Level 16, State 1, Line 1
    Backup or restore operation terminating abnormally.

    Error log shows,
    1999-08-30 10:28:54.68 kernel BackupDiskFile::OpenMedia: Backup device 'd:ackupdbx_083099_10.30AMdbx_083099_10.30AM& #39; failed to open. Operating system error = 1326(Logon failure: unknown user name or bad password.).

    (or)

    Cannot open backup device, 'd:ackupdbx_083099_10.30AMdbx_083099_10.30AM& #39;. Device error or device offline. See SQL Server error log for more info.
    Backup or restore operation terminated abnormally.

    How can I solve this problem ?

    Praveena


  3. #3
    Praveena Guest

    Restoring a database from a different server (reply)

    Thankyou, I shall try this. If there are any problems I shall let you know.
    Praveena


    ------------
    Diana Williams at 8/31/99 11:02:05 AM

    There is a much easier way to restore a database from one server to the other. What you need to do is first, make sure there is enough space on your hard drive that you are restoring to. Second, using the xp_cmdshell in Query Analyzer (or at the DOS command prompt), mape your drive such as:

    EXEC xp_cmdshell 'net use x: \servernamed$ admin /usersisql'

    Then at the server you want to restore to, in your SQL statement or in the visual restore box, use 'x' as the drive letter, then the actual path of where the backup file or device is. Then restore!! It works great. I use it every night to restore to our standby server.

    Any questions, e-mail me.


    ------------
    Praveena at 8/30/99 5:25:28 PM

    Hi!
    I attempted to backup a database on server x and restore it on a database on server y. The details are as follows.

    Server X
    Database name : dbx
    Size :263.88MB (Unrestricted filegrowth option set)
    Logical files : dbx_data, dbx_log
    Physical files : e:mssql7datadbx_data.mdf, f:databaselogsdbx_log.ldf

    Server Y
    Database name : dby
    Size :1MB (Unrestricted filegrowth option set)
    Logical files : dby_data, dby_log
    Physical files : d:mssql7datadby_data.mdf, d:mssql7datadby_log.ldf


    I created a full database backup of dbx on server x at the location, f:ackupsdbx_083099_10.30AM. The size of the file is 272MB. I zipped the file. Copied the file, dbx_083099_10.30AM.xip over to d:ackups of server y. I unzipped the file at server y to the location, d:ackupsdbx_083099_10.30AMdbx_083099_10.30AM.

    I now tried to restore this backup on server y using the statement,
    restore database dby from disk = 'd:ackupsdbx_083099_10.30AMdbx_083099_10.3 0AM
    with replace,
    move 'dbx_data' to 'd:mssql7datadby_data.mdf',
    move 'dbx_log' to 'd:mssql7datadby_log.ldf'


    I get the following errors :

    Server: Msg 3257, Level 16, State 1, Line 1
    There is insufficient free space on disk volume 'd:' to create the database. The database requires 3219128320 additional free bytes, while only 2047102976 bytes are available.
    Server: Msg 3013, Level 16, State 1, Line 1
    Backup or restore operation terminating abnormally.

    Error log shows,
    1999-08-30 10:28:54.68 kernel BackupDiskFile::OpenMedia: Backup device 'd:ackupdbx_083099_10.30AMdbx_083099_10.30AM& #39; failed to open. Operating system error = 1326(Logon failure: unknown user name or bad password.).

    (or)

    Cannot open backup device, 'd:ackupdbx_083099_10.30AMdbx_083099_10.30AM& #39;. Device error or device offline. See SQL Server error log for more info.
    Backup or restore operation terminated abnormally.

    How can I solve this problem ?

    Praveena


  4. #4
    Join Date
    Feb 2011
    Posts
    5

    SQL server 2008 - backup and restore

    Hi,

    I having a problem in backup and restore, kindly anybody help me, by giving your suggestion.

    I maintaining 2 server , 1 - Production server , 2 - Backup server
    i want to take a backup from production server, and that file want to store in backup server directory.

    For eg, my production server having a TEST as database, the regular backup handling in same system by
    here i used production server ip.
    BACKUP DATABASE [Test] TO
    DISK = '\\xx.xx.xx.xx\D$\TEST.BAK'
    WITH NOFORMAT, NOINIT

    if i give a backup server ip, means it throwing an error that,

    Cannot open backup device '\\xxx.xxx.xxx.xxx\E$\BACKUP\Test.BAK'. Operating system error 1326(Logon failure: unknown user name or bad password.).


    Kindly provide me a solution

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Don't dup post same issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •