locked
Backup and Restore with missing previous backups RRS feed

  • Question

  • Hello,
    I've a SQL Server Express 2012 DB that I need to backup and restore on a different machine.

    I know that in the past someone performed full db and logs backup with sqlcmd.exe and I found some of this backup files but not all of them.

    In the last 6 months no backups has been taken.

    What is the right procedure I need to follow in order to save a backup of this DB and restore it on different machine withou losing data?

    Thank you.

    Tuesday, June 23, 2015 6:33 PM

Answers

  • Create a Full Database Backup

    Backup and restore of SQL DBs

    From the current machine:

    USE AdventureWorks2012;
    GO
    BACKUP DATABASE AdventureWorks2012
    TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
       WITH FORMAT,
          MEDIANAME = 'Z_SQLServerBackups',
          NAME = 'Full Backup of AdventureWorks2012';
    GO


    On the new machine:

    RESTORE DATABASE AdventureWorks2012
       FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak' 

    You'll probably also need to fix some logins since SQL logins have their SID locally. Look here for instructions:sp_change_users_login

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    Tuesday, June 23, 2015 7:04 PM
  • I can' find the data of the last months or also the data of the previous day.

    Then you did something wrong in your backup / restore procedure, there can't be any other explanation for it.

    On backup, have you may be selected an existing backup media? If so have you changed this setting to "Overwrite all existing..."

    If not, then you added a new backup at the end of the media set and on restore you haven't selected the last backup and so you restored the first (old) backup from media file; that's why newer data are missing.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 24, 2015 6:18 AM

All replies

  • Create a Full Database Backup

    Backup and restore of SQL DBs

    From the current machine:

    USE AdventureWorks2012;
    GO
    BACKUP DATABASE AdventureWorks2012
    TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
       WITH FORMAT,
          MEDIANAME = 'Z_SQLServerBackups',
          NAME = 'Full Backup of AdventureWorks2012';
    GO


    On the new machine:

    RESTORE DATABASE AdventureWorks2012
       FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak' 

    You'll probably also need to fix some logins since SQL logins have their SID locally. Look here for instructions:sp_change_users_login

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    Tuesday, June 23, 2015 7:04 PM
  • 1) Backup database  on source server 

    BACKUP DATABASE DB TO DISK = 'c:\temp\DB.Bak'

    2) verify backup 

    RESTORE VERIFYONLY FROM DISK = 'c:\temp\DB.Bak'
    GO

    3)Place backup where full access on location .

    4) Restore on destination server:-

    RESTORE DATABASE DB    FROM DISK = 'D:\backup\DB.Bak'

    Restore using GUI


         


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Tuesday, June 23, 2015 8:21 PM
  • I've forgot to tell you that I've tried with SQL Management Studio using the GUI and when I import the backup on the new machine I can't see the data of the last 4 months.

    Do your method export all the data in the DB backup?


    • Edited by eaquadro Tuesday, June 23, 2015 8:44 PM
    Tuesday, June 23, 2015 8:38 PM
  • I've forgot to tell you that I've tried with SQL Management Studio using the GUI and when I import the backup on the new machine I can't see the data of the last 4 months.

    Do your method export all the data in the DB backup?


    Yes; just use the BACKUP DATABASE command and it will copy EVERYTHING in the production database. Then copy the files to the new server and use the RESTORE DATABASE command.  I listed out details above.

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Tuesday, June 23, 2015 8:54 PM
  • I've forgot to tell you that I've tried with SQL Management Studio using the GUI and when I import the backup on the new machine I can't see the data of the last 4 months.

    Do your method export all the data in the DB backup?


    what do you mean by cannot see the data of the last 4 months?? a full backup will inculde all the data present in the database at the time it was finished.

    if you restore the backup/backups - all you would get is retrieve the data that is present in the backup file.
    so, if the data you were looking for was already DELETED before the backup was taken on the source system, it will not inculde that information,(assuming  a simple scenerio of restoring a full backup),so, there is no point in restoring the backup.

    if you looking to restore certain data, you will need to figure when the data was present in the source system, and if you have those backup files , you would need to restore them.

    msdb.dbo.backupset table has all the information on when backups taken and type of backup etc..


    Hope it Helps!!

    Tuesday, June 23, 2015 9:03 PM
  • Thank you guys, I explain me well.

    Using a certain management software the users create and modify data.
    This software use a DB on SQL Server Express.

    In a certain point of time with no activities at all on the DB I execute a FULL backup and next I execute a restore on a new machine with another SQL Server Express.
    Then I configure the management software to use the new SQL Server and when I start it I can' find the data of the last months or also the data of the previous day.

    For all this procedure I used the SQL Management Studio GUI.

    I think that using your procedure or the SQL Management Studio GUI is the same thing. Is it right?

    Wednesday, June 24, 2015 5:42 AM
  • I can' find the data of the last months or also the data of the previous day.

    Then you did something wrong in your backup / restore procedure, there can't be any other explanation for it.

    On backup, have you may be selected an existing backup media? If so have you changed this setting to "Overwrite all existing..."

    If not, then you added a new backup at the end of the media set and on restore you haven't selected the last backup and so you restored the first (old) backup from media file; that's why newer data are missing.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 24, 2015 6:18 AM
  • I think that using your procedure or the SQL Management Studio GUI is the same thing. Is it right?

    Management Studio uses the same procs that we have posted. As Olaf has shown, you have various options in the UI which allow you to append another backup. When you do a restore from a file that contains many backups you have to choose the one you want. I would suggest using the backup script or choosing a completely new file to backup to and see what happens when you restore.

    It's possible that the software that the users are using is in some way dependent on something other than just the data in the database and a restore isn't enough. If the full backup doesn't show all the data then I would try to do a data compare and make sure that the data is there. If it is all there, and it will be, then I would contact the vendor for the user software and see if they have some "special" steps for migrating.

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Wednesday, June 24, 2015 1:57 PM