Restoring SQL Server2K backup onto SQL Server Express 2005

Is there any way to restore a full database backup made on sql server 2000
to SQL Server Express 2005? When I tried, I received an error message saying
the *.sho file could not be found.
Thanks,
Keith
hi Keith,
keithb wrote:
> Is there any way to restore a full database backup made on sql server
> 2000 to SQL Server Express 2005? When I tried, I received an error
> message saying the *.sho file could not be found.
actually it is... when a SQL Server 2000 database is restored onto a SQL
Server 2005 instance, it's database compatibility setting will not be ported
to 90, and you have some issues to fix (if you want to), like modifying the
compatibility level if you want the new SQL Server 2005 features to be
available for that database, update all statistics with FULL SCAN as they
get invalidated and set the database ownership to a valid login if you need
to access database diagrams..
and usually restoring a SQL Server 2000 database backup is a painless task..
I can only think of a SQL Server 2000 database exceeding the SQLExpress
limit of 4gb.. just that.. but I do not reaaly know what a "*.sho" file is..
how did you tried that?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Restoring sql server 7.0 database into sql server 2005 express - problem!

Hi!

I'm a beginner w ith SQL Express and am having some problems. I am using 2005 SP1 and installed the SQL Server Management Studio Express as well. I have a backup from a SQL Server 7.0 database that I am trying to restore into my database using the management studio. When I try to restore from the file into my database, I get the error,

System.Data.SqlClient.SqlError: The backup of the system database on the device D:\GMBeta2Local.bak cannot be restored because it was created by a different version of the server (7.00.1063) than this server (9.00.3033). (Microsoft.SqlServer.Express.Smo)

Is this true? Is there any way for me to get this data into my SQL Server 2005 express on my laptop?

hi,

thi message is usually raised when a system database is tried to be restored across different SQL Server versions, as reported in http://support.microsoft.com/kb/264474/en-us..

you should only "upgrade" user's databases..

regards

Restoring SQL Server 2000 backup to Express

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:

The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)

The backup is made from an SQL Server 2000 database called 'UpperBridge'

I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.

Any help very much appreciated.

hi,

withers wrote:

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:

The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)

The backup is made from an SQL Server 2000 database called 'UpperBridge'

I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.

Any help very much appreciated.

try forcing the "overwrite" of the database already present in the SQLExpress instance,
RESTORE DATABASE ....
....
WITH REPLACE;

or just drop the database before restoring (obviously take a full local backup of it before dropping it, if required )..

regards

Restoring sql express and databases from tape backup.

Hello,

I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.

I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task scheduler on the server before the disaster recovery.

However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task scheduler and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.

-

Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 \.S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....

Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid.

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>sqlcmd -S.\SQLExpr
COPLEYNEWSDATABASEscripts\MASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

-

My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.

Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.

I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.

This is some kind of issue with SQL Server Express being restored by tape backup.

Any suggestions, thanks.

hi,

usually you will not be able to restore system databases to a server that is not the one you took the dumps of...

you can restore on it users databases with little efforts (usually related to database users/logins mapping), but system databases should not be restored from dumps of different servers..

if this is not the case, perhaps the files related HP restore did not a good job.. I did not find lot of info about problems like that..

you can perhaps try, in order to verify the problem is not "Network" related, modifying the connection string to "(Local)\InstanceName", in order to activate a shared memory connection and not a connection under a different network protocol..

you can even check all the required newtowork protocols are enabled via the SQL Server Configuration Manager and SQL Server Surface Area Configuration...

but both these are related to

>The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid.
and
>Named Pipes Provider: Could not open a connection to SQL Server [2].

and not with the very first problem,

>Configuration block version 0 is not a valid version number...

this seems to me more .Net assembly related versus SQL related..

waiting for Mike to jump in

regards

|||

Thank you for your reply.

I guess I am wondering what other people do for disaster recovery. Is there something wrong with my disaster recovery plan?

I do a daily backup the Master, MSDB, Model, MyDatabase using simple backup and running a SQLCMD that backs up the database files Master.bak, MSDB.bak, Model.bak, and MyDatabase.bak to a different directory. This way I can have my full tape backup of my server run afterwards and back up everything (Operating System, SQLExpress, Master.bak, MSDB.bak, Model.bak, MyDatabase.bak etc.) . Then when disaster hits, I can restore my same server from last nights tape backup and then run my SQLCMD restore scripts to restore the Master.bak, MSDB.bak, Model.bak, MyDatabase.bak.

I have done many disaster recoveries from tape backup with no problems with the Operating System or any applications, except when it comes to SQL Express.

For some reason, after I follow the steps above, if I then go to open the SQL Express management studio express, I cannot connect to the server and its databases. This does'nt seem to be a settings issue unless settings are being changed behind the scenes. It does'nt make any sense to me that I would not be able restore SQL Express and its databases by tape backup. I can do it with the operating system and everything else.

Do i need to re attach the databases or something additional like that?

|||

hi,

I usually have seen total disaster recovery performed via traditional CDs..

I mean, you have all your installation media from which install the OS, all software and the like... (actually new machines as well ).. then you go on with SQL Server databases.. but "user's databases" and not system dbs as it's usually a mess to mix inter-instances system dbs..

taking a "live backup" of the folders SQL Server is running from and it's related subdirs, including data folder, is usually not safe for dbs, as this, if available to the backup sw at all, includes unclean data from the physical files.. tapes and backup sw vendors should use the appropriate apis to interface to and interact with SQL Server live databases or the result will just be a mess...

if something goes wrong, say, master.mdf gets corrupted, you just restore a corrupted master database.. and usually that means SQL Server will not be able to start at all, so you are no longer able to restore your last valid (SQL) backups.. but you are required to "rebuild" the master db.. from the installation media.. http://msdn2.microsoft.com/en-us/library/ms143269.aspx ...

(this is very hand in SQLExpress scenario as well, as, in MSDE 2000 time frame, you are out of luck as the feature was not available in the setup options, and no rebuildm.exe was shipped along with MSDE package..)

when you are done, you can then restore your user's databases as well..

but I'm still waiting for Mike to jump in for further info about you initial exception ...

reagards

|||

(cross-posting from my reply to this question in the SQL Disaster Recovery forum http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=744&SiteID=1 )

The issue is that the backup app is not properly restoring master. Most likely it failed to back up the file because it was locked by SQL at the time. Backup apps using the VSS infrastructure do not have this issue.

So, you have a few options:

1) shut down SQL Express during the backup so that the database files are properly saved

2) after bare-metal restore, uninstall SQL Express (this will not remove any databases you've created), reinstall it, and then restore your master database from backup (along with all other databases). This should restore all customizations that you have made.

3) Books Online documents a procedure for rebuild the system databases (master, model, msdb) using setup.exe:

If you extract the setup files to a directory by downloading and saving sqlexpr.exe (if you haven't already) using the command sqlexpr.exe /x, you'll have all the setup files.

Then, use the command:

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=SQLExpress REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> REINSTALLMODE=vomus

The setup will recreate all of the system databases without disturbing anything else. You can then restore master and the other databases from your backups and be up and running.

sql

Restoring Databases between SQL2005 Express and Developer Editions

Hi,

Are there any issues between porting databases fom SQL2005 express to SQL2005 Developer?

I'm trying to move Adventure works between the two on my Laptop. Both instances were installed identically and in the same directory paths except express is in MSSQL.1 and Developer in MSSQL.2

If I try to backup from Express to Developer and restore in developer I get an error suggesting that the backup contains more than 1 database image. When I detach\attach from express to developer I get a

Msg 5133, level 16, State 1, Line 1

Directory look up fro the file 'c:\program files ....' failed with operating system error 123 (error not found)

Does anyone have any ideas?

Thanks in Advance

Dave

Yep. The database file locations are stored within the backup media, you will have to change the file location of the datafiles during the configuration of the restore (if you use the GUI to perform the restore) or use the WITH MOVE Option if using the TSQL command for restoring.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
--