Solved

Converting a 15 GB SQL Server 2005 Workgroup DB into SQL Server Express 2008 R2

Posted on 2014-12-22
9
220 Views
Last Modified: 2014-12-31
I support an application that uses SQL Server Express 2008 R2 as the DB for the less expensive edition of the product.

A new client has an older version of the more expensive edition and purchased the less expensive edition when they bought their upgrade. They then asked me to help with the upgrade.

The previous DB is mostly docs and was stored in SQL Server 2005 Workgroup. The backup is 15 GB.

Is there any way to convert that backup into SQL Server Express 2008 R2? I tried to use the apps Restore function and it didn’t work…

Thanks very much,

John
0
Comment
Question by:243johnm
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 7

Expert Comment

by:Robby Swartenbroekx
ID: 40514143
0
 
LVL 1

Author Comment

by:243johnm
ID: 40514150
Thanks Robby. 10 GB is the maximum DB size: the docs should go into Filestream and not count against the DB size...

John
0
 
LVL 7

Assisted Solution

by:Robby Swartenbroekx
Robby Swartenbroekx earned 166 total points
ID: 40514157
ah, ok.
Then you need to remove the table with the docs in the SQL 2005 standaard instance and then you can detach the database and attach it to 2008 express (first do a shrink, so it drops below the 10Gb limit).
0
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40514612
First of all you need to migrate all data from MSSQL 2005 to MSSQL 2008R2 Express leaving the doc column, so you can have all data without passing the 10GB limit.
Then after you moved the documents to the filestream folder you need to update the table to link the records to the respective documents.
If you don't have SSIS installed then you need to do this by creating a linked server and manually scripting the procedures.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 167 total points
ID: 40518911
>>  I tried to use the apps Restore function and it didn’t work… <<

That's very vague.  What error message?  Did you use change the file paths to match the new server?  Did you make sure the FILESTREAM path was available exactly as it was on the old server?  I don't know if even that will work but it should at least have a chance of working.

When dealing with multiple tables, you have easy ways around the 10GB limit.  It's much more difficult to get around if a single table is near or over 10GB by itself.
0
 
LVL 1

Author Comment

by:243johnm
ID: 40519075
Thanks all.

Robby & Victor: those solutions are way beyond my knowledge of SQL!
Scott: this is the message that the apps restore function returned: "Problems encountered when trying to restore SQL Database DB_Name". Looking in the logs, there was nothing helpful!

John
0
 
LVL 1

Accepted Solution

by:
243johnm earned 0 total points
ID: 40519089
I received some assistance a couple of days ago from someone who had the same problem with the app we both support. His solution:

Install a trial of SQL Server 2008 with an instance name other than one used already and with Filestream enabled.
Restore the SQL backup into SQL Server 2008.
Run a script he gave me that removed the docs from the docs table, put them into Filestream and then deleted the docs table.
SQL Backup of that DB
Restore into SQL Server 2008 R2 Express.
Uninstall the trial.

His process worked: the 15+ GB SQL Server 2005 DB turned into a 120 MB SQL Server 2008 R2 Express DB with 15 GB of docs in Filestream!

Thanks again everyone who tried to help me with this issue!

John
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40519091
Awesome!

Thanks for the follow-up info, very interesting.
0
 
LVL 1

Author Closing Comment

by:243johnm
ID: 40525163
Took several days for someone with knowledge of the app to help me out ... but this solution worked!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
GRANT, REVOKE, DENY 4 38
Query 14 54
Excel conversion issue with Sql server 14 50
user defined date datatype in SQL Server- can it be overdone.. 6 24
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

867 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

22 Experts available now in Live!

Get 1:1 Help Now