Post reply

Restore error from backup file SQL2000

  • August 16, 2012 at 1:26 am

    #392958

    Hi,

    I have restored database at SQL2K8R2 from backup file sql server 2000.

    there is only one Backup file located into external HD, not in local server HD.

    I tried restore directly from external HD, what could be reason?

    use master

    go

    RESTORE DATABASE [TELE] FROM DISK =

    N'E:\TELE_201208021156.BAK' WITH FILE = 1,

    MOVE N'TELE_Data' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\

    MSSQL\DATA\TELE.MDF',

    MOVE N'TELE_1_Data' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\

    MSSQL\DATA\TELE.MDF',

    MOVE N'TELE_Log' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\

    MSSQL\DATA\TELE.LDF',

    MOVE N'TELE_1_Log' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\

    MSSQL\DATA\TELE.ldf',

    NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

    GO

    Msg 3176, Level 16, State 1, Line 1

    File 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER MSSQL\DATA\TELE.MDF' is claimed by 'TELE_1_Data'(3) and 'TELE_Data'(1). The WITH MOVE clause can be used to relocate one or more files.

    Msg 3176, Level 16, State 1, Line 1

    File 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER MSSQL\DATA\TELE.ldf' is claimed by 'TELE_1_Log'(4) and 'TELE_Log'(2). The WITH MOVE clause can be used to relocate one or more files.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

  • August 16, 2012 at 1:56 am

    #1526364

    existing SQL 2000 file name and location like this format

    TELE_Data D:\TELE Data Files\TELE_datafiles\TELE_Data.MDF

    TELE_Log D:\TELE Data Files\TELE_datafiles\TELE_Log.LDF

    TELE_1_Data D:\TELE Data Files\TELE_datafiles\TELE_DATA2.MDF

    TELE_1_Log D:\TELE Data Files\TELE_datafiles\TELE_log2.ldf

  • August 16, 2012 at 1:57 am

    #1526366

    You've specified the same physical file more than once in the restore. Each file in the database you're restoring has to go to a different file on disk.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 16, 2012 at 3:14 am

    #1526414

    GilaMonster (8/16/2012)

    You've specified the same physical file more than once in the restore. Each file in the database you're restoring has to go to a different file on disk.

    Yes. I understand. But how to restore at single file name at sql server 2008 r2 database such as TELE.mdf and TELE.ldf from multiple files in SQL server 2000.

    thanks

  • August 16, 2012 at 3:20 am

    #1526417

    You can't. A restore must recreate the DB as it was at the time of backup. Once restored you can then go and move objects and drop files.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 16, 2012 at 3:41 am

    #1526431

    ok, thanks for reply...

    could you give me solution and correct script for restoring. still i am facing same error.

  • August 16, 2012 at 3:43 am

    #1526434

    In your restore script, make sure that every file has a different physical file name. In the one you have, the mdf appears twice and the ldf appears twice. Specify 4 different file names (the names that the original DB had would be a good choice)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 16, 2012 at 4:02 am

    #1526440

    First time Restore database from SQL server 2000 backup files.

    By default create multiple MDF file and log file in SQL server 2008.

  • August 16, 2012 at 4:13 am

    #1526442

    ???

    When you restore a backup (from any version of SQL) it recreates the DB as it was at the time of backup, same number of files, same things in the files. If the restore created multiple data and log files, it's because the source DB had multiple data and log files.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 16, 2012 at 4:24 am

    #1526448

    yes.. source database SQL 2000 have mulitple data & log files.

    I caputred the script in SSMS throu database restore option.

    RESTORE DATABASE [TELE] FROM DISK =

    N'E:\TELE_Backup\TELE\TELE_201208021156.BAK' WITH FILE = 1,

    MOVE N'TELE_Data' TO N'D:\Program Files\Microsoft SQL Server MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TELE.MDF',

    MOVE N'TELE_1_Data' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER MSSQL\DATA\TELE1.MDF',

    MOVE N'TELE_Log' TO N'D:\Program Files\Microsoft SQL Server MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TELE_log.ldf',

    MOVE N'TELE_1_Log' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER MSSQL\DATA\TELE_log.ldf',

    NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

    GO

    in this case, can create one more data file. ndf , ldf then will try restore the database.

  • August 16, 2012 at 4:28 am

    #1526452

    That still won't work, you're specifying the same ldf for two log files. Each file must have a different file name.

    MOVE N'TELE_Log' TO N'D:\Program Files\Microsoft SQL ServerMSSQL10_50.MSSQLSERVER\MSSQL\DATA\TELE_log.ldf',

    MOVE N'TELE_1_Log' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVERMSSQL\DATA\TELE_log.ldf',

    Those must be different files.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 16, 2012 at 4:30 am

    #1526454

    You're still attempting to stuff multiple files into a single location. You can't do that this:

    MOVE N'TELE_1_Log' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVERMSSQL\DATA\TELE_log.ldf',

    Needs to be changed to something else, maybe this:

    MOVE N'TELE_1_Log' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVERMSSQL\DATA\TELE_1_log.ldf',

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 16, 2012 at 4:56 am

    #1526463

    thank you all.. finally database being executing for restore as below command , as well as created database files same like existing as per sql 2000.

    use master

    go

    RESTORE DATABASE [TELE] FROM DISK =

    N'E:\TELE_Backup\TELE\TELE_201208021156.BAK' WITH FILE = 1,

    MOVE N'TELE_Data' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TELE_Data.mdf',

    MOVE N'TELE_1_Data' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TELE_1_Data.mdf',

    MOVE N'TELE_Log' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TELE_Log.ldf',

    MOVE N'TELE_1_Log' TO

    N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TELE_1_Log.ldf',

    NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

    GO

Viewing 13 posts - 1 through 12 (of 12 total)

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