SQL Server Performance

restore database

Discussion in 'SQL Server 2005 General DBA Questions' started by reethu, Apr 23, 2007.

  1. reethu New Member

    HI Guys, i think its kind of novice quastion,i am trying to restore database test from invest database backup is that possible or not if not possible,
    how to do this task i need to check maintanance job before creating in prod or on real databases.i planned this way create a new database and restore rhat database from other database back up create maintanance plan for that newly created database.if i am wrong let me know how to do this task.
    error.TITLE: Microsoft SQL Server Management Studio Express
    ------------------------------

    Restore failed for Server 'REETHUSQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

    For help, click:http://go.microsoft.com/fwlink?Prod...ceptionText&EvtID=Restore Server&LinkId=20476

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

    System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'test' database. (Microsoft.SqlServer.Express.Smo)

    For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.3042.00&LinkId=20476

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

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


    SRJ2005
  2. MohammedU New Member

    If I understand your question correctly...
    You are trying to restore Invest database on to Test database...
    Yes, it is possible you can restore one database backup on to another database but you may need to use WITH MOVE option....

    http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1102260_tax301326,00.html?adg=301324&bucket=ETA


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. satya Moderator

    Refer to the books online about using MOVE Option and also check on source server for the path & location of the data files, try to recreate same and if not using MOVE option you can achieve the restore operation.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. reethu New Member

    yap i did it using move options, thanks.

    SRJ2005
  5. viksar New Member

    are both the servers on SQL server 2005
  6. reethu New Member

    i think i am missing part as i restored database using move option, but i can see logical file name as restored from database name
    ex. database to be restored testdb
    from database testdb1
    after creating this way i see logical name is different from original database.eventhogh i changed it it appears same.
    Actually how it works if i create database test and restore it from other database on same server.this way i need to do.if any one have idea please let me know,

    SRJ2005
  7. satya Moderator

    WITH MOVE will move the path details from the backup to the specified location, logical file name remains same.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  8. reethu New Member

    so that we can do all database activities without interupting any future course problems.
    actually i will post request what i got from dev team for this request i used below statement,
    RESTORE FILELISTONLY
    FROM DISK = 'D:program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup est1.bkp'
    RESTORE DATABASE DBTest
    FROM DISK = 'D:program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup est1.bkp'
    WITH MOVE 'test' TO 'D:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTESTDBTest.mdf',
    MOVE 'test1_log' TO 'D:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTESTDBTest_log.ldf'
    GO
    request.
    Please create new database called data_dictionary in all environments cev.test and prod and restore it from the latest backup available from prod SQL2005
    is this correct way or i made mistake please let me have some idea.

    thanks in advance.

    SRJ2005
  9. satya Moderator

    Correct, as you have moved the path as compared to Dev from old to newer location.
    Check the difference from the RESTORE FILELISTONLY values to the statement you have used, it is the clue.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  10. reethu New Member

    THANK YOU, I did not catch exactly ut i understand /

    SRJ2005
  11. reethu New Member

    in this case i have taken one full backup and log at a time and i tried to restore them immdtly but i got this error .why it was like .
    The log in this backup set begins at LSN 5000000007200001, which is too late to apply to the database. An earlier log backup that includes LSN 5000000005400001 can be restored.


    SRJ2005
  12. satya Moderator

    See the mistmatch LSN number and due to that it fails, only BACKUP Database takes higher hand in this aspect.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  13. reethu New Member

    really i did not get it, what is the way i can perform this.

    SRJ2005
  14. MohammedU New Member

    Run the RESTORE HEADERONLY command for both backup file and check the LSN mismatch..


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  15. ghemant Moderator

    LSN is a Log Sequance Number ,when you restores a T-Log it will search and compare for the last LSN of last successful restored T-Log with the newer one you applied, if it mismatch it will fail.

    Hemantgiri S. Goswami
    MS SQL Server MVP
    -------------------------
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

    http://hemantgirisgoswami.blogspot.com
  16. MohammedU New Member

    Tlog backup FirstLSN should be greater than or equal to Full backup LastLSN...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  17. satya Moderator

    Due to the reason of full database backup and next process of log backup has difference in LSN, where you can see the LSN information is displayed when you have tried to restore the log backup that has been performed after full database backup.

    quote:Originally posted by reethu

    really i did not get it, what is the way i can perform this.

    SRJ2005

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  18. reethu New Member

    Thank you i got it .but i come across another issue.
    please may i know how to do it, i am trying to restore database from network path but this database has replication on (publication)
    so how can i perform this restore without distrub that publication.
    after restore publication should get immediate access to the users,

    SRJ2005
  19. satya Moderator

    Look under BOoks online for " Backing Up and Restoring Replicated Databases" topic in this case and you can use KEEP_REPLICATION option.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  20. reethu New Member

    Hi guys actually i have no test environment to test this kind of task. that is only the reason i am taking gurus advise donot think otherwise, when we back databases to disk we split and zip them, so most of the times it splits into multiple files,when i restore them how to do restore.
    thanks.

    SRJ2005
  21. MohammedU New Member

    If I understand, you are taking a database backup to a file and using the zip.exe to zip and split the files...

    Don't use zip.exe to slit the backup files when you are zipping...

    You can take the single database to muliple backup files instead of huge one file and you can zip them and unzip before restore and single backup's multiple backup files can be used for restore...

    BACKUP DATABASE <DBNAME> TO disk = 'c:ackup1.bak', disk = 'c:ackup2.bak'...

    See BOL for more details...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  22. satya Moderator

    Even though if you zip them into various files at the end when restoring back from WINZIP you will have 1 backup file, so in this csae it is always best to see the contents of the backup by using RESTORE FILELISTONLY and based on this (backup file) during restor it will be accomplished. FOr the examples of restoring replication etc look at BOL as advised.

    Don't take our advices as final and in order to ensure you must test them before deploying in production.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  23. MohammedU New Member

    You can also use third party tools like LiteSpeed etc.. to compress the backups.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  24. satya Moderator

    Is there any specific reason to compress the backups?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  25. reethu New Member

    storage issues.

    SRJ2005
  26. satya Moderator

    If you do not have any problem with WINZIP in compress and uncompress method, then it should be ok and ensure tocheck the backup consistency before and after.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page