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.

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. :)

• Edited by Daniel Janik 11 hours 59 minutes ago
• Proposed as answer by Olaf HelperMVP 1 hour 24 minutes ago

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

     

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 10 hours 21 minutes ago

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. :)

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..

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?

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.