December 29, 2009 at 7:41 am
Can we restore a database from lower version to Higher version
I mean SQL Server 2005 (1399) to SQL Server 2005 (4273)
I want to restore databases as part of moving our production to different server.
December 29, 2009 at 7:50 am
Yes you can.
The only restriction I know and and am aware of is that a backup from a higher Version cannot be restored on an earlier version. Like 2008 DB backup won't work on 2005 or 2000 and similarly 2005 backup won't work on 2000 or earlier.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 29, 2009 at 10:25 am
Bru Medishetty (12/29/2009)
Yes you can.The only restriction I know and and am aware of is that a backup from a higher Version cannot be restored on an earlier version. Like 2008 DB backup won't work on 2005 or 2000 and similarly 2005 backup won't work on 2000 or earlier.
Thank you for your reply.
One more question..
In production server ReportServer, ReportServerTempDB databases are there.
Do I need to restore these two databases to new prod server?
December 29, 2009 at 10:41 am
Are you using Reporting Server ? If yes you need to restore them both.
Before doing it, read the article below.
Moving a Report Server Database to Another Computer
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 29, 2009 at 11:40 am
Currently we are not using Reporting server. Earlier we used to get a report for internal application(not for any client application).
What you advice
December 29, 2009 at 11:57 am
Deos that internal application report use the SS Report Server? You better check the resport server by looking at the Reportserver Report Manager and see for any reports deployed.
Based on that you have got to decide to restore SSRS DBs or not.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 29, 2009 at 12:22 pm
i think you only need ReportServer. The temp one is like tempdb that is used to hold data for when reports are executing
December 29, 2009 at 12:42 pm
SQL Noob (12/29/2009)
i think you only need ReportServer. The temp one is like tempdb that is used to hold data for when reports are executing
My initial assumption was the same, but the technet article mentions the steps to restore the TempDB also.....
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 29, 2009 at 12:54 pm
Some reports are there.
Restoring Reporting databases are not like normal backup and restore?
I am planning to below steps
1. Stop report server
2.detach database in primary server
3.copy mdf and ldf file to secondary server
4 attach the database
5.start report server.
One question I have already reportserver db in secondary server. any otherthings to take care
December 29, 2009 at 1:36 pm
Does the ReportServer on the secondary server has any reports deployed?
To be on the safer side you need to backup that even nothing exists and something exists, I would have it like that and restore this ReportServer on primary server as a different database name and manually look to move the data into the ReportServer on secondary server.
Even that step needs to be done after making sure that you do not have duplication of report data occurring because of the data movement.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 29, 2009 at 1:51 pm
No reports are deployed in secondary server.
Your ansewer is confusing to me. Can you please explanin in details.
We are moving our production to a another server, we will use this server as production after moving.
In my case report server is already installed in secondary server (.mdf and .ldf are there)
detach/attach
I need to copy .mdf and .ldf files from primary and paste in seconday server. Since I have already files in secondary , how to proceed?
December 29, 2009 at 2:07 pm
If you are going to overwrite the mdf and ldf files at the secondary server (new server) the reportserverDB existing on that server will be overwritten and will be lost. Are you OK with that? It is always better to have a backup of the existing DB in a separate location.
Since you say that the secondary server has no reports deployed, it should be fine to use the detach attach method and move the ReportServerDB and the ReportServerTempDB.
In my earlier reply I was suggesting a step if you had reports deployed on the secondary server, which is not the case so you need not worry about that.
But at any case to be safer side, have backups of the existing databases on both servers before doing this. If you have not enough confidence, take guidance from your senior colleague.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 29, 2009 at 2:13 pm
Note that moving those databases to the secondary server, either with detach/attach or just a copy over of the mdf/ldf (note that you need to stop the SQL services), does not mean that everything will work. Any users you have in the databases will not necessarily be synced up with logins on that new server. You would need to move those logins separately (sp_help_revlogin) and perhaps sync them (sp_change_users_login)
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 29, 2009 at 2:35 pm
Steve Jones - Editor (12/29/2009)
Note that moving those databases to the secondary server, either with detach/attach or just a copy over of the mdf/ldf (note that you need to stop the SQL services), does not mean that everything will work. Any users you have in the databases will not necessarily be synced up with logins on that new server. You would need to move those logins separately (sp_help_revlogin) and perhaps sync them (sp_change_users_login)
When you say ''just a copy over of the mdf/ldf '', what exactly. detach/attach also we need to copy/move mdf ldf file to new server.
I had moved all logins with permission to new server, and taken backups
December 29, 2009 at 2:37 pm
Bru Medishetty (12/29/2009)
If you are going to overwrite the mdf and ldf files at the secondary server (new server) the reportserverDB existing on that server will be overwritten and will be lost. Are you OK with that? It is always better to have a backup of the existing DB in a separate location.Since you say that the secondary server has no reports deployed, it should be fine to use the detach attach method and move the ReportServerDB and the ReportServerTempDB.
In my earlier reply I was suggesting a step if you had reports deployed on the secondary server, which is not the case so you need not worry about that.
But at any case to be safer side, have backups of the existing databases on both servers before doing this. If you have not enough confidence, take guidance from your senior colleague.
Thanks Medishetty!
I tried with normal Restore with backup (overrite existing db)...that worked for me. In what way detach/attach better than restore backup.
After doing detach and attach. If I want to use primary server for any reporting, do I need to install SSRS again?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply