restore master database: express

Showing posts with label express. Show all posts

Wednesday, March 28, 2012

Restore using SQLDMO

I am using SQLDMO for restoring database from back up file . If the back up file version(SQL2000) is differenent than database version(SQL Express) I am trying to restore, does SQLDMO performs upgrade if the backup files are an earlier version. if no how can i handle that situation?It should restore the database on the server, but set it to compatability level 80, so the SQL 2000 functionality will still be there.

Monday, March 26, 2012

Restore to SQL 2005?

Hello,
I'm using SQL 2005 Express and I have a database that I've created a backup
for, but when my ISP tries to restore it with the full version of SQL 2005,
they say the tables are missing. Any ideas why I can restore it with the
Express edition and they can't do it with the full one?
Thanks
JimWhat exactly is meant by "the tables are missing"? Is it that the database
restores successfully but is empty?
On possible cause is that the database backup file contains multiple backups
and only the first (oldest) is restored by default.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.gordonferon.com> wrote in message
news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm using SQL 2005 Express and I have a database that I've created a
> backup for, but when my ISP tries to restore it with the full version of
> SQL 2005, they say the tables are missing. Any ideas why I can restore it
> with the Express edition and they can't do it with the full one?
> Thanks
> Jim
>|||Hi Jim
Can you give us more details?
What does 'full version of SQL 2005' mean? Is it standard or enterprise?
32-bit or 64-bit? What OS?
WHO says tables are missing? What tables? Is there an error message? When
does it show up? What does it say exactly?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm using SQL 2005 Express and I have a database that I've created a
> backup for, but when my ISP tries to restore it with the full version of
> SQL 2005, they say the tables are missing. Any ideas why I can restore it
> with the Express edition and they can't do it with the full one?
> Thanks
> Jim
>|||I'm using Visual Web Developer with SQL 2005 Express. I have a database that
I created for my website and everything works the way it should on my
computer. My ISP says the only way they can create the database on their
side is to restore it from a backup. I created a full backup by right
clicking on the database and going through all the steps. When my ISP tries
to restore it, they're telling me that the database is empty. They are using
SQL 2005 Standard version 9.0.3 & my version is 9.00.2047.00. I'm using Win
Xp 32bit. There are no error messages on either my side or their's when
creating or restoring the tables.
Jim
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi Jim
> Can you give us more details?
> What does 'full version of SQL 2005' mean? Is it standard or enterprise?
> 32-bit or 64-bit? What OS?
> WHO says tables are missing? What tables? Is there an error message? When
> does it show up? What does it say exactly?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I'm using SQL 2005 Express and I have a database that I've created a
>> backup for, but when my ISP tries to restore it with the full version of
>> SQL 2005, they say the tables are missing. Any ideas why I can restore it
>> with the Express edition and they can't do it with the full one?
>> Thanks
>> Jim
>>
>
>|||Make sure that you backup to a new file (a file which doesn't exist) so you don't by mistake append
the backup and the always restore the most oldest backup. Or check out the INIT option of the backup
command.
Also, don't take your ISP word that the db is empty. Login using a query window (if at all possible)
and verify for yourself. Also, make sure this isn't just a side effect of login - username mismatch
(read about sp_change_users_login and Google for sp_help_revlogins).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim" <jim@.gordonferon.com> wrote in message news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
> I'm using Visual Web Developer with SQL 2005 Express. I have a database that I created for my
> website and everything works the way it should on my computer. My ISP says the only way they can
> create the database on their side is to restore it from a backup. I created a full backup by right
> clicking on the database and going through all the steps. When my ISP tries to restore it, they're
> telling me that the database is empty. They are using SQL 2005 Standard version 9.0.3 & my version
> is 9.00.2047.00. I'm using Win Xp 32bit. There are no error messages on either my side or their's
> when creating or restoring the tables.
> Jim
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>> Hi Jim
>> Can you give us more details?
>> What does 'full version of SQL 2005' mean? Is it standard or enterprise? 32-bit or 64-bit? What
>> OS?
>> WHO says tables are missing? What tables? Is there an error message? When does it show up? What
>> does it say exactly?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Jim" <jim@.gordonferon.com> wrote in message news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I'm using SQL 2005 Express and I have a database that I've created a backup for, but when my ISP
>> tries to restore it with the full version of SQL 2005, they say the tables are missing. Any
>> ideas why I can restore it with the Express edition and they can't do it with the full one?
>> Thanks
>> Jim
>>
>>
>
>|||A database can never be completely empty, so this makes me wonder how your
ISP is determining it is 'empty'. My guess is that they are not SQL Server
Professionals, and are jumping to wild conclusions.
Perhaps they are just running sp_help to see a list of objects, or expanding
the tables folder in SSMS Object Explorer, and if they don't have the
appropriate permissions, they will get an empty list.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim" <jim@.gordonferon.com> wrote in message
news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
> I'm using Visual Web Developer with SQL 2005 Express. I have a database
> that I created for my website and everything works the way it should on my
> computer. My ISP says the only way they can create the database on their
> side is to restore it from a backup. I created a full backup by right
> clicking on the database and going through all the steps. When my ISP
> tries to restore it, they're telling me that the database is empty. They
> are using SQL 2005 Standard version 9.0.3 & my version is 9.00.2047.00.
> I'm using Win Xp 32bit. There are no error messages on either my side or
> their's when creating or restoring the tables.
> Jim
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>> Hi Jim
>> Can you give us more details?
>> What does 'full version of SQL 2005' mean? Is it standard or enterprise?
>> 32-bit or 64-bit? What OS?
>> WHO says tables are missing? What tables? Is there an error message? When
>> does it show up? What does it say exactly?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Jim" <jim@.gordonferon.com> wrote in message
>> news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I'm using SQL 2005 Express and I have a database that I've created a
>> backup for, but when my ISP tries to restore it with the full version of
>> SQL 2005, they say the tables are missing. Any ideas why I can restore
>> it with the Express edition and they can't do it with the full one?
>> Thanks
>> Jim
>>
>>
>
>|||Thanks for the suggestions. I ended up copying the data over to tables they
created.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OmZWtQdrHHA.4828@.TK2MSFTNGP04.phx.gbl...
>A database can never be completely empty, so this makes me wonder how your
>ISP is determining it is 'empty'. My guess is that they are not SQL Server
>Professionals, and are jumping to wild conclusions.
> Perhaps they are just running sp_help to see a list of objects, or
> expanding the tables folder in SSMS Object Explorer, and if they don't
> have the appropriate permissions, they will get an empty list.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim" <jim@.gordonferon.com> wrote in message
> news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
>> I'm using Visual Web Developer with SQL 2005 Express. I have a database
>> that I created for my website and everything works the way it should on
>> my computer. My ISP says the only way they can create the database on
>> their side is to restore it from a backup. I created a full backup by
>> right clicking on the database and going through all the steps. When my
>> ISP tries to restore it, they're telling me that the database is empty.
>> They are using SQL 2005 Standard version 9.0.3 & my version is
>> 9.00.2047.00. I'm using Win Xp 32bit. There are no error messages on
>> either my side or their's when creating or restoring the tables.
>> Jim
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>> Hi Jim
>> Can you give us more details?
>> What does 'full version of SQL 2005' mean? Is it standard or enterprise?
>> 32-bit or 64-bit? What OS?
>> WHO says tables are missing? What tables? Is there an error message?
>> When does it show up? What does it say exactly?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Jim" <jim@.gordonferon.com> wrote in message
>> news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I'm using SQL 2005 Express and I have a database that I've created a
>> backup for, but when my ISP tries to restore it with the full version
>> of SQL 2005, they say the tables are missing. Any ideas why I can
>> restore it with the Express edition and they can't do it with the full
>> one?
>> Thanks
>> Jim
>>
>>
>>
>>
>
>sql

Restore to SQL 2005?

Friday, March 23, 2012

Restore SQL Server Express Databases after a Disaster Recovery

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 manager 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 manager 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.

The error message, "Configuration block version 0 is not a valid version number" tells us that the configuration page of the master database, which is one of the first pages read when we start up, is invalid. It appears that for some reason, the master database is not being correctly restored by your DR process.

If you can tell us more about your process, what specific tools and scripts make up your backup/restore process, we can search for the root cause.

|||

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 if I am only looking at the Simple Backup as an option?

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. I have also run my restore scripts on the server with no problem restoring.

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.

Is it possible to restore the SQL Server Express from tape backup and then run restore scripts generated from the same tape backup up of the Master, Model, MSDB, etc. databases?

|||My SQLCMD scripts for backup and restore are all generated from the Sql Express Management Studio Express, simply by allowing it to write the code and then I mannually paste them into *.sql files that are later run by SQLCMD. I've tested all of them and they all work. I only have problems when I do a disaster recovery senario on the same server by restoring all the OS and data back to the server. After restoring the server, then I restore the databases with the scripts and .bak files that were previously backed up. They all restore with no errors. However, when I try to connect to SQL Server Express, it can't connect.|||

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. I'm having trouble getting this to work. I'll post when I get this resolved.

|||

OK. Got it.

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.

Can you please let me know what DR utility this is? I'd like to work with them to make it work better with SQL.

|||Thanks for your help Kevin, I am using HP one button disaster recovery with Tapeware backup software. It seems to work very well for me. The only thing it does'nt back up are log files and *.MDF files. Basically if its an open file then it might have trouble backing it up. Anyways here was my solution, which I feel is the least time consuming.

After a week of trying to figure out a working disaster recovery solution for sql express, I have finally succeeded.

I wanted to do a disaster recovery to the same server with a tape backup and then restore the Master.bak, Model.bak, MSDB.bak, and MyDatabase.bak, back to the SQLExpress DBMS. Let me start out by saying that you cannot do that unless you have a working copy of the Master.mdf on the server you are trying to restore to. The catch here is that the tape backup software will not back up the Master.mdf because its always being used by SQLExpress.

Now, I suppose I could have dettached the Master.mdf before I backed it up, but I decided to go a different route.

I found out that there is a directory called "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data" and in it is a copy of the same contents (except I think that they are the original files created automatically by the original install, in other words, not uptodate) found in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory where the files need to reside in order to do a restore to the database, which happened to be missing after I did my tape back up restore. I simply draged and dropped a copy of the missing *mdf, *ldf files and then I was able to launch the SQLExpress Studio Management Express and connect to the SQL Server. The only thing different was that the Model database was set to (Read Only) but that didn't concern me because I was going to restore all the Databases (Master, Model, MSDB, MyDatabase) anyway.

After that was solved, I could now run any *.sql restore scripts and have them work.

Next step is to restore the Master.mdf but you can only do that in single user mode.
So, I went into SQLServer Express, single user mode with the command prompt:
C:cd "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn: Sqlservr.exe -s SQLEXPRESS -m
Press<enter>
After it does its thing you need to open an additional command prompt window because it will say that its ready for connections in single user mode, but you will not be given a new cursor prompt.

So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MASTERFULLRESTORE.sql"

This is what my MASTERFULLRESTORE.sql restore script file looks like (I created it previously using the GUI in sql express and then saved it to a script file instead of clicking on OK):
RESTORE DATABASE [Master] FROM DISK = N'E:\DATABASEBACKUP\Master.bak' WITH FILE= 1, NOUNLOAD, REPLACE, STATS = 10
GO

The SQL restore script will work this time becuase I have a working instance SQLExpress thanks to the previous drag and drop restore of the Master.mdf from the Template Data directory.

After that you need to go into Services and start the 'SQLServer Service'.
Then simply go into SQLExpress like normal and restore the Model, MSDB, from the GUI interface by right clicking on each one and selecting restore and then point to your *bak files.

When it came time to restore 'MyDatabase', I could see it in SQLExpress Management Studio Express but it was just a shell of it with no contents inside. This shell of my database is from the Master.mdf restore. Unfortunately, even though I could see it in the SQLExpress listing, when I right clicked on it, hoping for the restore option, It was grayed out. So I closed SQL Express and ran my restore.sql script from the command prompt using SQLCMD.exe and it succeeded.
So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MYDATABASEFULLRESTORE.sql"

I then went back into SQLExpress and ran some queries to test and everything seems to be working now.

Thanks to everyone for helping to point me in the right direction to solve this problem.

Wednesday, March 21, 2012

Restore Sql Express (2005) to Sql Server 2000

Well, my understanding is this should be possible and easy -- use a backup created by Sql Express (via Management Studio Express), copy the backup to the Sql 2000 server, select Database Restore through EM, specify Restore from Device, point to the Sql Express generated backup file as the *only* device, and launch.

Doesn't work.

When I launch the restore, I get the following error dialog:

Too many backup devices for backup or restore. Only 64 are allowed. <yada-yada> .... OK

Only got one backup device selected. Is this possible or not?

I have resorted to sql script generation with data in the past, but that's taxes the server resources heavily.

TIA,

Rick

No ... its not possible.. you can't restore SQL Server 2005 backup in SQL Server 2000. You may follow these steps

(a) Create Script of Database Objects from 2005

(d) run the objects script in 2000

(e) use DTS to transfer data from 2005 to 2000

Madhu

|||Thanks. I assume you have to use DTS provided with full blown Sql Server 2005 (which ain't provided with SqlExpress)?
|||

Yes , SSIS(DTS of 2005) is not provided with Express edition. you can use sql server 2000 DTS and pull data from 2005 to 2000.

Madhu

|||Madju,

Okay ... so I've created the database objects in Sql Server 2000, created a System DSN to connect to the Sql Express database (different server).

Next, when I use 2000's DTS and specify SQL Native Client I am able to connect successfully to the Sql Express database. However, after only several seconds into the transer, I get the follow error from DTS:

Cannot perform requested task because full-text memory manager is not initialized

Not sure what that pertains to.

(later)
Okay, I've found it. It's the MSSearch service on the server that it needs running. I've started the service, stopped and restarted MS Sql Server serice, dependent services and EA says full-text support is running now. Guess what? DTS still yields the same error message! Any other idea, anyone? I'm running DTS from my workstation. I shouldn't have to run it from the server, should I?

(later)
For whatever reason, have to run DTS from the Sql Server 2000 server and not a client workstation. Done deal now.

|||Thanks. This was really helpful. I tried a similar approach by exporting the table defs and data from SQL Express 2005 using the data publishing wizard, but i ran into an error recreating the database on the SQL Server 2000 due to the limit of 64k for command lengths.

This method worked much smoother. I also had to run the DTS on the server itself for it to work.

Restore Sql Express (2005) to Sql Server 2000

Doesn't work.

When I launch the restore, I get the following error dialog:

Too many backup devices for backup or restore. Only 64 are allowed. <yada-yada> .... OK

Only got one backup device selected. Is this possible or not?

I have resorted to sql script generation with data in the past, but that's taxes the server resources heavily.

TIA,

Rick

No ... its not possible.. you can't restore SQL Server 2005 backup in SQL Server 2000. You may follow these steps

(a) Create Script of Database Objects from 2005

(d) run the objects script in 2000

(e) use DTS to transfer data from 2005 to 2000

Madhu

|||Thanks. I assume you have to use DTS provided with full blown Sql Server 2005 (which ain't provided with SqlExpress)?
|||

Yes , SSIS(DTS of 2005) is not provided with Express edition. you can use sql server 2000 DTS and pull data from 2005 to 2000.

Madhu

|||Madju,

Okay ... so I've created the database objects in Sql Server 2000, created a System DSN to connect to the Sql Express database (different server).

Cannot perform requested task because full-text memory manager is not initialized

Not sure what that pertains to.

(later)
For whatever reason, have to run DTS from the Sql Server 2000 server and not a client workstation. Done deal now.

This method worked much smoother. I also had to run the DTS on the server itself for it to work.

Monday, March 12, 2012

Restore Problem - Error 5243

Dear All,

SQL : SQL Express 2005

I having a problem when trying to restore a database. there were around 10 databases, but one is giving the following error and restore fails, will be great if someone helps..

I also tried taking the backup to another site having SQL 2005 Standard edition, the same error comes.

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server '.\SQL2K5'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8. (Microsoft SQL Server, Error: 5243)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=5243&LinkId=20476

BUTTONS:

I also installed Service Pack, no luck..HELP!!!

HI,

Refer http://support.microsoft.com/kb/916086 which has workaround and hot fix for the problem.

Hemantgiri S. Goswami

|||

Hi,

Thanks for the reply. I went through that already and hence I applied the service pack 1. NO LUCK!!!

Good you pointed the KB link, I am just little confused.It says about "Creating and drop temporary tables...." and what I am doing is Restoring the DB from a backup..Does it mean while restoring temporary tables are created ?

Time is running out, HELP!!

No reply on this from Microsoft Support team!!! where are u guys?

Regards,

Restore Problem - Error 5243

Dear All,

SQL : SQL Express 2005

I having a problem when trying to restore a database. there were around 10 databases, but one is giving the following error and restore fails, will be great if someone helps..

I also tried taking the backup to another site having SQL 2005 Standard edition, the same error comes.

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server '.\SQL2K5'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8. (Microsoft SQL Server, Error: 5243)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=5243&LinkId=20476

BUTTONS:

I also installed Service Pack, no luck..HELP!!!

HI,

Refer http://support.microsoft.com/kb/916086 which has workaround and hot fix for the problem.

Hemantgiri S. Goswami

|||

Hi,

Thanks for the reply. I went through that already and hence I applied the service pack 1. NO LUCK!!!

Time is running out, HELP!!

No reply on this from Microsoft Support team!!! where are u guys?

Regards,

Wednesday, March 7, 2012

Restore Not Working (2005->2005)

I did a backup using SQL Server 2005 Express on 1 machine, and when I tried to restore to SQL Server 2005 Developer Edition (included with VS 2005 Pro), it gives me the following error:

TITLE: Microsoft SQL Server Management Studio
----------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

----------
ADDITIONAL INFORMATION:

The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476

----------
BUTTONS:

OK
----------

I looked at the link and it is talking about SQL Server 7 and 2000. I read a few posts about not being able to restore 2005 to 2000, but I have 2005 on both machines. I'm not sure the best way to check, but I know I installed the SQL Server 2005 Developer edition to the new machine recently. Any ideas?

Thanks!

I figured this out. A separate software tool installed SQL Server 2000 for its own use, and when I installed 2005, somehow it did not install. I have fixed it.

Saturday, February 25, 2012

restore microsoft SQL express 2005 database to another host.

Hi,
Very new to microsoft SQL server 2005 express, so please forgive a
simple question.
I am trying to restore a backup taken on one machine to another. I have
installed 2005 express on both machines along with Microsfot server
management Studio Express.
Step and failures recieved.
1. backed up database bugnet with SQL Server management / database /
tasks / backup.
2. Copied backup to another machine.
3. Created database bugnet ( created same schema as original ).
4. Tried SQL server managment restore and received the following:
system.data.SqlClient.SqlError: the backup set holds a backup from
a database other than the existing 'bugnet'database.
Is there an easy way to restore a 2005 express database to another
machine
Any help is this bread 'n' butter task would be so welcome.
Thanks.
Hi,
The best way:
1. You delete the database created by you.
2. Right Click the databases on the server where you want to restore the
database.
3. Click the restore database.
4.Then one screen will come with the name as Restore database
a) In General>> "To database" you write the name of the database.
b) Select from device option, then select the backup file from where you
want to restore the database
c) Select the Backup sets to restore.
5. Then go to the "option" option in left hand side pannel of Restore
Database Screen
a) Here you have to specify the actual path of the MDF and LDF file,
because it will show you the path as per the server from where you took the
backup.
If the file structure is same for both the sql server you can click OK to
restore the database.
Note: If created the database, without delete database step you can carry on
with all other steps with in "option", you have to choose overwrite the
existing database.
Regards
Swaprakash
"lotstolearn" wrote:

> Hi,
> Very new to microsoft SQL server 2005 express, so please forgive a
> simple question.
> I am trying to restore a backup taken on one machine to another. I have
> installed 2005 express on both machines along with Microsfot server
> management Studio Express.
> Step and failures recieved.
> 1. backed up database bugnet with SQL Server management / database /
> tasks / backup.
> 2. Copied backup to another machine.
> 3. Created database bugnet ( created same schema as original ).
> 4. Tried SQL server managment restore and received the following:
> system.data.SqlClient.SqlError: the backup set holds a backup from
> a database other than the existing 'bugnet'database.
> Is there an easy way to restore a 2005 express database to another
> machine
> Any help is this bread 'n' butter task would be so welcome.
> Thanks.
>

restore microsoft SQL express 2005 database to another host.

Hi,
Very new to microsoft SQL server 2005 express, so please forgive a
simple question.
I am trying to restore a backup taken on one machine to another. I have
installed 2005 express on both machines along with Microsfot server
management Studio Express.
Step and failures recieved.
1. backed up database bugnet with SQL Server management / database /
tasks / backup.
2. Copied backup to another machine.
3. Created database bugnet ( created same schema as original ).
4. Tried SQL server managment restore and received the following:
system.data.SqlClient.SqlError: the backup set holds a backup from
a database other than the existing 'bugnet'database.
Is there an easy way to restore a 2005 express database to another
machine
Any help is this bread 'n' butter task would be so welcome.
Thanks.Hi,
The best way:
1. You delete the database created by you.
2. Right Click the databases on the server where you want to restore the
database.
3. Click the restore database.
4.Then one screen will come with the name as Restore database
a) In General>> "To database" you write the name of the database.
b) Select from device option, then select the backup file from where you
want to restore the database
c) Select the Backup sets to restore.
5. Then go to the "option" option in left hand side pannel of Restore
Database Screen
a) Here you have to specify the actual path of the MDF and LDF file,
because it will show you the path as per the server from where you took the
backup.
If the file structure is same for both the sql server you can click OK to
restore the database.
Note: If created the database, without delete database step you can carry on
with all other steps with in "option", you have to choose overwrite the
existing database.
Regards
Swaprakash
"lotstolearn" wrote:

> Hi,
> Very new to microsoft SQL server 2005 express, so please forgive a
> simple question.
> I am trying to restore a backup taken on one machine to another. I have
> installed 2005 express on both machines along with Microsfot server
> management Studio Express.
> Step and failures recieved.
> 1. backed up database bugnet with SQL Server management / database /
> tasks / backup.
> 2. Copied backup to another machine.
> 3. Created database bugnet ( created same schema as original ).
> 4. Tried SQL server managment restore and received the following:
> system.data.SqlClient.SqlError: the backup set holds a backup from
> a database other than the existing 'bugnet'database.
> Is there an easy way to restore a 2005 express database to another
> machine
> Any help is this bread 'n' butter task would be so welcome.
> Thanks.
>

restore microsoft SQL express 2005 database to another host.

Hi,
Very new to microsoft SQL server 2005 express, so please forgive a
simple question.
I am trying to restore a backup taken on one machine to another. I have
installed 2005 express on both machines along with Microsfot server
management Studio Express.
Step and failures recieved.
1. backed up database bugnet with SQL Server management / database /
tasks / backup.
2. Copied backup to another machine.
3. Created database bugnet ( created same schema as original ).
4. Tried SQL server managment restore and received the following:
system.data.SqlClient.SqlError: the backup set holds a backup from
a database other than the existing 'bugnet'database.
Is there an easy way to restore a 2005 express database to another
machine
Any help is this bread 'n' butter task would be so welcome.
Thanks.Hi,
The best way:
1. You delete the database created by you.
2. Right Click the databases on the server where you want to restore the
database.
3. Click the restore database.
4.Then one screen will come with the name as Restore database
a) In General>> "To database" you write the name of the database.
b) Select from device option, then select the backup file from where you
want to restore the database
c) Select the Backup sets to restore.
5. Then go to the "option" option in left hand side pannel of Restore
Database Screen
a) Here you have to specify the actual path of the MDF and LDF file,
because it will show you the path as per the server from where you took the
backup.
If the file structure is same for both the sql server you can click OK to
restore the database.
Note: If created the database, without delete database step you can carry on
with all other steps with in "option", you have to choose overwrite the
existing database.
Regards
Swaprakash
"lotstolearn" wrote:
> Hi,
> Very new to microsoft SQL server 2005 express, so please forgive a
> simple question.
> I am trying to restore a backup taken on one machine to another. I have
> installed 2005 express on both machines along with Microsfot server
> management Studio Express.
> Step and failures recieved.
> 1. backed up database bugnet with SQL Server management / database /
> tasks / backup.
> 2. Copied backup to another machine.
> 3. Created database bugnet ( created same schema as original ).
> 4. Tried SQL server managment restore and received the following:
> system.data.SqlClient.SqlError: the backup set holds a backup from
> a database other than the existing 'bugnet'database.
> Is there an easy way to restore a 2005 express database to another
> machine
> Any help is this bread 'n' butter task would be so welcome.
> Thanks.
>

Monday, February 20, 2012

Restore Master database to SQLExpress

Hello,

I have a fresh install of sqlExpress and Management Studio Express on my test server. I want to restore my master database from backup.

From the command prompt I set the Sqlservr -s SQLEXPRESS -m

Then I opened another comand prompt and ran my SQLCMD script to restore the Master Database.

here is the sql script:
RESTORE DATABASE [Master] FROM DISK = N'E:\COPLEYNEWSDATABASEBACKUP\Master.bak' WITH FILE = 1, MOVE N'mastlog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Master_1.ldf', NOUNLOAD, STATS = 10
GO

I recieve the following error.

Msg 3154, Level 16, State 4, Server COPLEYNEWS\SQLEXPRESS, Line 1
The backup set holds a backup of a database other than the existing 'Master' dat
abase.
Msg 3013, Level 16, State 1, Server COPLEYNEWS\SQLEXPRESS, Line 1

How do I restore a Master Database on SQL Express?

hi,

just in case I tried my self as well..

took a master database backup.. stopped the SQLExpress instance., started in singleuser mode (-m)..

opened another command window and ran

sqlcmd -S .\SQLExpress -Usa -Pxxxx

1>RESTORE DATABASE master

2>FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\m.bak'

3>GO

and succesfully restored the master database and shutted the instance down.. it worked like a charm...

are you sure you are restoring a dump taken from the same instance you are trying to restore to?

regards

|||

Thanks for the reply,

I have SQL Server 9.0.2047 that has the Master.bak file.

I am trying to restore that file on a SQL Server 9.0.1399.

My problem right now is that I know I need to restore on the SQL Server 9.0.2047 but I can't figure out which version of SQL Server Express to download.
I thought it was the SQL Server Express w/advanced Services. There is also the issue of service packs for SQL Express.

Can someone please give me the download link to the correct version of SQL Server 9.0.2047?

|||

hi,

9.0.1399 is SQL Server 2005 RTM, while 9.0.2047 is service pack 1..

you can get the "traditional" sp1 package of SQLExpress at http://www.microsoft.com/downloads/details.aspx?FamilyID=11350b1f-8f44-4db6-b542-4a4b869c2ff1&DisplayLang=en , while SQLExpress with Advanced Service has been relase only with sp1, http://www.microsoft.com/downloads/details.aspx?familyid=4C6BA9FD-319A-4887-BC75-3B02B5E48A40&displaylang=en

regards

|||

After a week of trying to figure out a working disaster recovery solution for sql express, I have finally succeeded.

Now, I suppose I could have dettached the Master.mdf before I backed it up, but I decided to go a different route.

After that was solved, I could now run any *.sql restore scripts and have them work.

The SQL restore script will work this time becuase I have a working instance SQLExpress thanks to the previous drag and drop restore of the Master.mdf from the Template Data directory.

I then went back into SQLExpress and ran some queries to test and everything seems to be working now.

Thanks to everyone for helping to point me in the right direction to solve this problem.

|||

hi,

After a week of trying to figure out a working disaster recovery solution for sql express, I have finally succeeded.
I wanted to do a disaster recovery to the same server with a tape backup and then restore the Master.bak, Model.bak, MSDB.bak, and MyDatabase.bak, back to the SQLExpress DBMS. Let me start out by saying that you cannot do that unless you have a working copy of the Master.mdf on the server you are trying to restore to. The catch here is that the tape backup software will not back up the Master.mdf because its always being used by SQLExpress.

I found out that there is a directory called "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data" and in it is a copy of the same contents (except I think that they are the original files created automatically by the original install, in other words, not uptodate) found in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory where the files need to reside in order to do a restore to the database, which happened to be missing after I did my tape back up restore. I simply draged and dropped a copy of the missing *mdf, *ldf files and then I was able to launch the SQLExpress Studio Management Express and connect to the SQL Server. The only thing different was that the Model database was set to (Read Only) but that didn't concern me because I was going to restore all the Databases (Master, Model, MSDB, MyDatabase) anyway.

yep... could be a viable solution.. only in SQLExpress as only this edition saves this \Template Data folder for User Instance use.. http://msdn2.microsoft.com/en-us/library/ms143684.aspx...
personally I think I'd go with the master rebuilded by the setup installer...

When it came time to restore 'MyDatabase', I could see it in SQLExpress Management Studio Express but it was just a shell of it with no contents inside. This shell of my database is from the Master.mdf restore. Unfortunately, even though I could see it in the SQLExpress listing, when I right clicked on it, hoping for the restore option, It was grayed out. So I closed SQL Express and ran my restore.sql script from the command prompt using SQLCMD.exe and it succeeded.

yep... your master restore has "pointers" to a database, your user database(s), but the files related to each database are not present in the \Data folder.. you can perhaps skip all this by just copying your mdf+ldf files (if you have a clean copy of them) into the folder.. but the path you followed is more correct.. you have a clean situation with your newest "granted" Transact-SQL backups..

regards

|||

Hi crowesql,

I did just that, but when I go to see if the restore actually happened, by selecting * from msdb..restorehistory there's no records there. How can I make sure I actually restored master?

Thanks

Wednesday, March 28, 2012

Restore using SQLDMO

Monday, March 26, 2012

Restore to SQL 2005?

Restore to SQL 2005?

Restore to SQL 2005?

Friday, March 23, 2012

Restore SQL Server Express Databases after a Disaster Recovery

Wednesday, March 21, 2012

Restore Sql Express (2005) to Sql Server 2000

Restore Sql Express (2005) to Sql Server 2000

Monday, March 12, 2012

Restore Problem - Error 5243

Restore Problem - Error 5243

Wednesday, March 7, 2012

Restore Not Working (2005->2005)

Saturday, February 25, 2012

restore microsoft SQL express 2005 database to another host.

restore microsoft SQL express 2005 database to another host.

restore microsoft SQL express 2005 database to another host.

Monday, February 20, 2012

Restore Master database to SQLExpress

restore master database

Blog Archive

About Me