Post reply

restore bacjup from diff location

  • February 17, 2005 at 7:54 am

    #86905

    I am having an issuing applying a backup that I copied from a differnet SQL server. I did use the sp_addumpdevice to add the device but when I loook at the contents of the backup there is no backup set name it says incomplete. How can I define the backup set name as I think this is my problem

  • February 17, 2005 at 8:16 am

    #542820

    It Would be something like this:

    sp_addumpdevice @devtype = 'DISK', 
	@logicalname = 'Full_Bak',
	@physicalname = 'E:\Backup_Dir\FileName.BAK'
GO
RESTORE DATABASE DBname
	FROM Full_Bak
        WITH RECOVERY, REPLACE , STATS,
        MOVE 'DBname_data' TO 'E:\DATA\DataFile.mdf', 
        MOVE 'DBName_log' TO 'E:\DATA\LogFile.Ldf'
GO
SP_DROPDEVICE @LOGICALNAME =  'Full_Bak'
GO

    You may want to step through these as opposed to running at once.

    Good luck.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • February 17, 2005 at 8:35 am

    #542824

    The device is unnecessary and is adding another level of complexity. For simplicity why not simply restore directly from the file.

    RESTORE DATABASE DBname

    FROM DISK='E:\backup\full.bak'

    WITH RECOVERY, REPLACE ,

    MOVE 'DBname_data' TO 'E:\DATA\DataFile.mdf',

    MOVE 'DBName_log' TO 'E:\DATA\LogFile.Ldf'

  • February 17, 2005 at 8:42 am

    #542828

    Do I need to detach the the db to restore its telling DB in use?

  • February 17, 2005 at 9:06 am

    #542835

    You need to be in the master database, not the one you are restoring. Also, no one else can be in that database.

    Before actually starting the restore run the following commands.

    ALTER DATABASE dbname
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO
EXEC sp_dboption  @dbname = 'dbname' , 
   @optname = 'single user', @optvalue ='TRUE'
GO
EXEC sp_dboption  @dbname = 'dbname' ,
   @optname = 'dbo use only', @optvalue ='TRUE'
GO

    Just a side note - you should post all messages and questions in the open forum threads. Sometimes the initial repondent just won't have time to get back to you.

    Jason, I've always had problems going to the disk. I've found it is easier to add the dump device. Besides this came out of a script that I would run monthly. It made it easier to put the dump device at the top of the script and edit there instead of going down through the rest of it to find the file names. Just my $0.02.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • February 17, 2005 at 9:11 am

    #542837

    Thank you both for providing me your expertise. I appreciate the help and Jim/Jason......Jim I will post to the open forum in the future thanks....LYNN~~

  • February 17, 2005 at 9:18 am

    #542838

    Jim,when I try to set single user mode and DBO only I get this messgage

     

     

    Database options single user and dbo use only cannot be set at the same time.

     

    Any thoughts

  • February 17, 2005 at 9:28 am

    #542849

    It means other users are connected preventing single user mode. 

  • February 17, 2005 at 9:36 am

    #542855

    Are you in the master database?

    Go into the enterprise manager and -> Management -> Current Activity -> Process Info and look for any users of the database. Terminate their connections.

    Or you might want to get the script "Kick all users out of a DB" at

    http://qa.sqlservercentral.com/scripts/contributions/907.asp



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • February 17, 2005 at 9:48 am

    #542862

    Now I am getting this

     

    Cannot open backup device 'Full_Bak'. Device error or device off-line. See the SQL Server error log for more details.

     

    ???

  • February 17, 2005 at 9:50 am

    #542864

    I am in single user and I went in an killed the other running connections now the backup device is off line?

  • February 17, 2005 at 10:01 am

    #542870

    When you added the device before were you in the database you want to restore to?

    Go to that database adn run "sp_helpdevice". If the "Full_Bak" is there do the sp_dropdevice. Then go back to the master database and redo the sp_addumpdevice.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • February 17, 2005 at 12:58 pm

    #542921

    Now it runs data base reads loading and it just sits....the ldf and mdf are being restored to the proper directory but the DB remains loading.....ANd then I get this error

     

     

    Server: Msg 3270, Level 16, State 1, Line 1

    An internal consistency error occurred. Contact Technical Support for assistance.

    Server: Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

     

    The DB I am restoring is not on the server yet. Do I need to create it first then restore to it?

     

  • February 17, 2005 at 2:02 pm

    #542941

    Okay.....this isn't as horrible as it looks...probably.

    First off...

    1. Make sure you have a lot of disk space free.

    2. Are the disks on a SAN? What is network traffic like?

    3. Confirm the paths exist.

    4. Sometimes it does help to create a small empty database before doing the restore.

    Now when you start the restore change the "RECOVERY" to "NORECOVERY". After the restore the database will not be operational.

    Then after the restore do a

    "RESTORE DATABASE DBName WITH RECOVERY"

    Good luck.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • February 18, 2005 at 7:18 am

    #543068

    It sounds to me that the backup is corrupted or not finished. You can run the following command to verify it:

    RESTORE HEADERONLY FROM DISK='your back up file name'

    If the backup name is "INCOMPLETE", you need to back up the database again.

     

     

Viewing 15 posts - 1 through 15 (of 20 total)

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