Solved

SQL Server Restore Script

Posted on 2013-05-14
9
317 Views
Last Modified: 2014-10-28
Evening all!

I was wondering who would be kind enough to assist me with my predicament today!

I am currently migrating an application that has a SQL instance dependance. The instance is local and I already have a script that backs it up, its extremely lengthy so if you have a script that is short and to the point that will back it up, i will take that also!!. My main requirement now is for a script that can restore the backup back into SQL Server. The backup is from a SQL Server 2005 database and the restore will be to a SQL Server 2008 database.

If there is a GUI based script I can use that I can select A)Backup - which will take all of the required information (instance name, permissions, tables etc) and another option B)Restore - which will put it all back.


I'm a SQL noob and need to get this right, which is why I am seeking your assistance!

Any help is appreciated!
0
Comment
Question by:Amarjot Singh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 500 total points
ID: 39165677
I would give this a read (it lets you select logins/users so you don't have orphaned users) and it's, in my approximation, the easiest way to do this with the least amount of SQL knowledge: http://msdn.microsoft.com/en-us/library/ms188664%28v=sql.105%29.aspx.  I would make absolutely certain you don't use the move option and stay with the copy option, since if you detach/attach you are going to upgrade the db to a different version and there would be no going back.
0
 
LVL 4

Expert Comment

by:mikosha
ID: 39165698
Pretty easy way to detach-attach databases.
Take a look on this article, it has simple 3 steps to completa this operation:
http://www.packtpub.com/article/moving-a-database-from-sql-server-2005-to-sql-server-2008-in-three-steps

Very important note from this article as well: "The simple procedure outlined in this tutorial is adequate if you need to transfer databases from MS SQL Server 2005 to MS SQL Server 2008. If you need to selectively transfer logins, or choose objects to transfer perhaps the Copy Database Wizard may be better suited."

So make sure it's suitable to your case.
0
 

Author Comment

by:Amarjot Singh
ID: 39165723
Thanks Mikosha, I think i agree with didnthaveaname though. Detach-Attach introduces far too many risks than I'd be willing to take. I would say that copy would be a better method which decreased risks.

Didnthaveaname - I'll have a read over the MS notes you sent now! Thanks


EDIT:

Sorry guys - i dont think i made myself clear - I am upgrading machines from XP to Win 7 - the databases are being backed up from the XP OS and then restored on the Win 7 OS so I dont think i can use either detach-attach or copy db... i think?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39166279
If I am understanding what you were saying, both methods should be independent of the host OS.  For instance, if I were upgrading from SQL 2005 on Server 2003 to SQL 2012 on Server 2012, this would be no problem.  The OS will limit the version of SQL Server that you can install, but beyond that, it shouldn't have any impact on either of our suggestions.  Is that what you were concerned about?
0
 

Author Comment

by:Amarjot Singh
ID: 39166296
Not necessarily what I meant.

I require a solution that will backup/export the database to a network resource and then once the machine has been rebuilt and SQL server reinstalled, the restore/import will pull from the network resource and place back in place with all or as much of the metadata intact.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39177900
Either solution (Backup/Restore or Detach Copy Attach) can be used in your case for your user databases.  However, you will not be able to do this with your master database as they are different versions.  So you will have to script out all your logins and if you do it right (include the SID) you should not have any orphaned users.
0
 

Author Comment

by:Amarjot Singh
ID: 39178424
Thanks guys, I'm going to be working on this from tomorrow onwards.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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