Backup and restore MSSQL DB

Posted on 2016-08-12
Last Modified: 2016-08-17
Hi , what is the step to backup from MSSQL express to the MSSQL SQL 12
Question by:Julio Jose
  • 3
  • 3
  • 2
  • +2
LVL 12

Expert Comment

ID: 41754581
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!

Author Comment

by:Julio Jose
ID: 41754820
receive error when I try backup

LVL 16

Expert Comment

ID: 41754876
Do you get the same error using this T-SQL command?
TO DISK = 'C:\JulioPath\dbJulio.BAK'

Open 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

LVL 12

Expert Comment

ID: 41754968
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".
LVL 11

Accepted Solution

Nakul Vachhrajani earned 250 total points
ID: 41755005
Backing up and restoring from SQL 2012 to SQL 2014 should not be a problem. Based on the screenshots, it looks like you are trying to use SSMS for SQL 2008 - which will result in the Index out of bounds message.

Try to use SSMS for SQL 2012 for backup and SSMS for SQL 2014 for the restore. If those are not available, using the BACKUP DATABASE T-SQL command is your best bet.
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41756018
receive error when I try backup
What's the error?

Author Comment

by:Julio Jose
ID: 41756483
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
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41756543
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.

Author Comment

by:Julio Jose
ID: 41756548
why sound so complicated? I need step for SQL express 2012 to SQL Server 2014, the version not listed support on the MS site.
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41756557
You're only migrating a database and that's nothing complicated with that. A SQL Server Express database has no difference from any other regular SQL Server edition. The limitation is only handled by the engine and not the database itself.

I wrote 3 articles about SQL Server database migration. Each one with a different method but the goal is the same. Have a read on them so I think it will help you knowing more about the process:
SQL Server database migration - The Detach/Attach method
SQL Server database migration - The Backup & Restore method
SQL Server database migration - The Copy Database Wizard method

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Introduction We as admins face situation where we need to redirect websites to another. This may be required as a part of an upgrade keeping the old URL but website should be served from new URL. This document would brief you on different ways ca…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now