[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

How do you migrate a SQL 2005 express database?

I need to migrate a MS SQL 2005 express database that contains log in information for
members of a Sharepoint site to a new Server. I backed up the database in SQL management studio, and saved it on the new server but when I tried to restore the database on the new server using SQL management studio this database file was not recognized.  Any ideas?
0
PDSWSS
Asked:
PDSWSS
  • 5
  • 3
  • 3
  • +1
1 Solution
 
sstopsCommented:
Are you sure that the version and Service Pack Level is higher than the original server.

Can you post the exact error message?
0
 
souvik2008Commented:
Create a new database in the new server with the same name as it was in the previous server. Now Stop the SQL Server services. and replace the mdf and ldf files of these database with the mdf and ldf files from the old server databse.
Now start the SQL server service , your database will be migrated to the new server.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
While doing the backup you would not have added the extension *.bak and hope your backup file misses extension.

Just add .bak extension to your backup file and try restoring it now.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
PDSWSSAuthor Commented:
Do you need to turn off SQL Server services when backing up and restoring the database?

Thanks
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
No.. Its not required. You need to close all existing connections and can do the backup and restore.
0
 
PDSWSSAuthor Commented:
rrjegan17:

Isn't bkf the extension for a backup file?
0
 
souvik2008Commented:
If you are still having problem with backups . you can use my technique to migrate the DBs. This is a efficient one as long as you can get hold of two machines. Which is the case now . I worked on this technique and it worked like a wonder. It is the most simplest and efficient way I believe.

P.S You don't have to shutdown the database server while copying the mdf and ldf files from the location. You just have to stop the service in the new machine (server) for replacing the mdf and ldf files there.
0
 
PDSWSSAuthor Commented:
souvik2008:

Do you know if your backup method works for restoring SQL databases for Windows Sharepoint Services sites e.g.,
config, content, admin, search dbs?  Thanks
0
 
PDSWSSAuthor Commented:
Adding the .bak extension allowed restore to recognize the file. I have attached the error message I received when I attempted to open the aspnet database after restoring with the .bak extension.

I have also attached the error message I received when I tried to open the aspnet DB after following souvki2008's suggested method.

What is the solution here?



restoreerror.doc
SQL-error.doc
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope the backup was done in another machine and you dont have permissions on it.

Try doing this

1. Right-click the .bak file, Choose Properties --> Security tab.  
2. Check Name for "SQL" and add the following two SQL accounts from the list:
    * SQLServer2005MSSQLUser$<machine name>$MSSQLSERVER
    * SQL

This may slightly vary as you have an express edition. Find an equivalent user over there "SQLServer2005MSSQLUser$<machine name>$MSSQLSERVER"

This will help you out.
0
 
souvik2008Commented:
Try this method This will surely help you to get the full intact database to new server.

Don't create any new database on the new server. Just place the old mdf and ldf files in the path C:/mssqlserver/mssql1/data/ . Now open the sql server management studio and expand the databases tree if it is not already expanded. Now right click on the databases and click on the attach from the context menu. Now a new dialog box will open . Here click on the add button and select the mdf file which you have already added in the data directory in the new server.

Now refresh the databases by right click on the databases tree and  select refresh from the context menu. The new database is ready to use now.
0
 
PDSWSSAuthor Commented:
Worked like a charm. Thank you.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now