Backup and restore MSSQL DB

This link: Back Up and Restore of SQL Server Databases

... has the latest version of all the backup information. How-to examples are at the bottom of the page.

* Not sure this applies to Express:
 Basically you can right-click on the [database name]  > Tasks > Back Up ... in SSMS, run a full backup to a disk 'device'. Leave the default folder location, append the file name (i.e. myBackup.bak) and make a note of it.

It's likely easiest to copy the backup file to the MSSQL folder (or new subfolder thereof ) on the destination server (i.e. C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\). You should be able to right-click on the 'Databases' folder > Restore Database ... in SSMS.

The link has pretty much all you need to know to do every type of back up and restore. I encourage you to read all of that material sometime, however it is not required for the task at hand.

Examples for back up and restore via SQL command are also available in that link.

Good luck!

receive error when I try backup

Do you get the same error using this T-SQL command?

BACKUP DATABASE dbJulio 
TO DISK = 'C:\JulioPath\dbJulio.BAK'

Select allOpen in new window

where "dbJulio" is the name of your database and "C:\JulioPath\" is where you want to put the backup.

If so, as you seem to be attempting to migrate from SQL Server Express 2012 to Enterprise 2014, I believe (I haven't tried it!) that it would be possible to simply detach the database from the Express server (using SSMS) and attach it to the Enterprise one using SSMS - possibly with a file copy in between to get it to the right place. Once you have done that then you could move data between databases using the SSMS tools.

Finally, if all else fails, try creating an equivalent database on the enterprise server. Then use SSIS to move the data from the 2012 database to the new 2014 one.

Hope this helps

Mike

Good tips, Mike!

Another one would be to Script Database As ... to replicate the database structure. Perhaps that's what Mike meant by "try creating an equivalent database on the enterprise server".

receive error when I try backup

What's the error?

Follow Rakul step use the same version SMS, I can backup and successful restore the DB on the new DB server.

However when the application try to connect, I get this error from sql log, this is the same application user attempt to connect I can see the user include successful restore, how do I perform password reset for this user
Image-741.png
Image-742.png

When you restore a backup in a different SQL Server instance you need to guarantee that the same login exists in the target SQL Server instance. If not then you need to create it and match the login with the desired user.
Here's the Microsoft's article about transferring SQL Server logins.

why sound so complicated? I need step for SQL express 2012 to SQL Server 2014, the version not listed support on the MS site.