Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

TITLE: Microsoft SQL Server Management Studio
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.

Section 5.8.1 of the SQL 2005 Readme file says:
The installation of these support objects can fail on a database that has
been attached or restored from another instance of SQL Server. This can occur
when the database owner name (stored in the database) is not a valid logon
for the instance of SQL Server the database is being attached or restored to.

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

|||Just in case anyone else experiences the same problem as I did I'll add one more comment. If your login is a domain account and you are working offline you may get this message until you connect to the domain.|||

Yeah!

It really works! but can I know why?

|||

It appears that the database must be converted to the compatibility level 90 (2005).. I tried all the other recommended commands, while retaining the SQL2000 compatibility level, and it would not work.

I will usually keep a dev data base at the compatibility level of the server it is hosted on, not automatically convert it to the level of my dev machine, but it looks like this is not going to be possible.. Someone please correct me if I am wrong as I would like to be able to continue doing this..

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

Either way, be advised that this change is not trouble-free for everyone: in the same thread, Harry Chou recommends a SQL Server 2005 Books Online topic that covers a variety of possible incompatibilities between SQL Server 2005 and previous versions: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm. These incompatibilities would become issues in a database after one of these approaches is used to make it compatible with SQL Server 2005.

I don't know if the Upgrade Wizard checks for these incompatibilities.

sql

Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

TITLE: Microsoft SQL Server Management Studio
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.

Section 5.8.1 of the SQL 2005 Readme file says:
The installation of these support objects can fail on a database that has
been attached or restored from another instance of SQL Server. This can occur
when the database owner name (stored in the database) is not a valid logon
for the instance of SQL Server the database is being attached or restored to.

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

|||Just in case anyone else experiences the same problem as I did I'll add one more comment. If your login is a domain account and you are working offline you may get this message until you connect to the domain.|||

Yeah!

It really works! but can I know why?

|||

It appears that the database must be converted to the compatibility level 90 (2005).. I tried all the other recommended commands, while retaining the SQL2000 compatibility level, and it would not work.

I will usually keep a dev data base at the compatibility level of the server it is hosted on, not automatically convert it to the level of my dev machine, but it looks like this is not going to be possible.. Someone please correct me if I am wrong as I would like to be able to continue doing this..

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

Either way, be advised that this change is not trouble-free for everyone: in the same thread, Harry Chou recommends a SQL Server 2005 Books Online topic that covers a variety of possible incompatibilities between SQL Server 2005 and previous versions: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm. These incompatibilities would become issues in a database after one of these approaches is used to make it compatible with SQL Server 2005.

I don't know if the Upgrade Wizard checks for these incompatibilities.

Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

TITLE: Microsoft SQL Server Management Studio
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.

Section 5.8.1 of the SQL 2005 Readme file says:
The installation of these support objects can fail on a database that has
been attached or restored from another instance of SQL Server. This can occur
when the database owner name (stored in the database) is not a valid logon
for the instance of SQL Server the database is being attached or restored to.

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

|||Just in case anyone else experiences the same problem as I did I'll add one more comment. If your login is a domain account and you are working offline you may get this message until you connect to the domain.|||

Yeah!

It really works! but can I know why?

|||

It appears that the database must be converted to the compatibility level 90 (2005).. I tried all the other recommended commands, while retaining the SQL2000 compatibility level, and it would not work.

I will usually keep a dev data base at the compatibility level of the server it is hosted on, not automatically convert it to the level of my dev machine, but it looks like this is not going to be possible.. Someone please correct me if I am wrong as I would like to be able to continue doing this..

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

Either way, be advised that this change is not trouble-free for everyone: in the same thread, Harry Chou recommends a SQL Server 2005 Books Online topic that covers a variety of possible incompatibilities between SQL Server 2005 and previous versions: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm. These incompatibilities would become issues in a database after one of these approaches is used to make it compatible with SQL Server 2005.

I don't know if the Upgrade Wizard checks for these incompatibilities.

Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

TITLE: Microsoft SQL Server Management Studio
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.

Section 5.8.1 of the SQL 2005 Readme file says:
The installation of these support objects can fail on a database that has
been attached or restored from another instance of SQL Server. This can occur
when the database owner name (stored in the database) is not a valid logon
for the instance of SQL Server the database is being attached or restored to.

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

|||Just in case anyone else experiences the same problem as I did I'll add one more comment. If your login is a domain account and you are working offline you may get this message until you connect to the domain.|||

Yeah!

It really works! but can I know why?

|||

It appears that the database must be converted to the compatibility level 90 (2005).. I tried all the other recommended commands, while retaining the SQL2000 compatibility level, and it would not work.

I will usually keep a dev data base at the compatibility level of the server it is hosted on, not automatically convert it to the level of my dev machine, but it looks like this is not going to be possible.. Someone please correct me if I am wrong as I would like to be able to continue doing this..

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

Either way, be advised that this change is not trouble-free for everyone: in the same thread, Harry Chou recommends a SQL Server 2005 Books Online topic that covers a variety of possible incompatibilities between SQL Server 2005 and previous versions: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm. These incompatibilities would become issues in a database after one of these approaches is used to make it compatible with SQL Server 2005.

I don't know if the Upgrade Wizard checks for these incompatibilities.

Restored Database/Diagrams Support/SQL-Server 2005

Hello,

I have restored a database from SQL-server 2000 into SQL-server 2005 and
when I try to install diagram support, I receive this error:

TITLE: Microsoft SQL Server Management Studio
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.

Section 5.8.1 of the SQL 2005 Readme file says:
The installation of these support objects can fail on a database that has
been attached or restored from another instance of SQL Server. This can occur
when the database owner name (stored in the database) is not a valid logon
for the instance of SQL Server the database is being attached or restored to.

Changing the owner with the line below did not solve the problem:
ALTER AUTHORIZATION ON DATABASE::databaseTest TO userTest

Can anyone help me with this issue?

Thanks
/Ershad

you're missing just a few steps...

ALTER AUTHORIZATION ON DATABASE::[databaseTest] TO userTest
go
use [databaseTest]
go
EXECUTE AS USER = N'dbo' REVERT
go

|||You answer was very helpful. Thank you very much! I only nedded to execute the following before I could run you statements

EXEC sp_dbcmptlevel 'databaseTest', '90';

|||Just in case anyone else experiences the same problem as I did I'll add one more comment. If your login is a domain account and you are working offline you may get this message until you connect to the domain.|||

Yeah!

It really works! but can I know why?

|||

It appears that the database must be converted to the compatibility level 90 (2005).. I tried all the other recommended commands, while retaining the SQL2000 compatibility level, and it would not work.

I will usually keep a dev data base at the compatibility level of the server it is hosted on, not automatically convert it to the level of my dev machine, but it looks like this is not going to be possible.. Someone please correct me if I am wrong as I would like to be able to continue doing this..

|||

????

?? ???????? ??? ??????.

??? ?? ????? ??????

|||

"Clint" reports an alternative in a thread on this topic at http://geekswithblogs.net/takenote/archive/2006/02/06/68381.aspx:

Actually, you can just go to the database properties, go to the Options page, and change the compatibility to SQL Server 2005.

This seems to have worked for me.

Either way, be advised that this change is not trouble-free for everyone: in the same thread, Harry Chou recommends a SQL Server 2005 Books Online topic that covers a variety of possible incompatibilities between SQL Server 2005 and previous versions: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm. These incompatibilities would become issues in a database after one of these approaches is used to make it compatible with SQL Server 2005.

I don't know if the Upgrade Wizard checks for these incompatibilities.

Restore Newbie

I am trying to install and run a prototype VB.net application for review
I have desktop edition of SQL2K installed and running.
I have a .bak file that I need to load for the applications data
How do I do restore?
I've seen posts where one runs the restore command, but I cannot find an executable called restore.exe?Allan,
It's a T-SQL command.
RESTORE DATABASE.
Or, you can do it from Enterprise Manager.
Either way, look at RESTORE DATABASE in Books Online.
James Hokes
"Allan" <anonymous@.discussions.microsoft.com> wrote in message
news:E467A676-703B-4636-BAE0-19B4BB6DD55B@.microsoft.com...
> I am trying to install and run a prototype VB.net application for review
> I have desktop edition of SQL2K installed and running.
> I have a .bak file that I need to load for the applications data
> How do I do restore?
> I've seen posts where one runs the restore command, but I cannot find an
executable called restore.exe?
>
>

Restore msdb and master db from files.

Hi all,
My SQL Server crashed and I don't have any backup. So, I install new server, I attach user databases, everythink ok, but i need restore master and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there any way to do it?
Thanks a lot
Systemspecialist,
Build your SQL Server directory structure *exactly* the same as it was
set up on the old server, and place the .mdf and .ldf files in the same
locations, then start SQL Server. It should just start up. Make sure you
have the same service pack applied on the new box as you did on the old,
before copying the data files over.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Systemspecialist wrote:

> Hi all,
> My SQL Server crashed and I don't have any backup. So, I install new server, I attach user databases, everythink ok, but i need restore master and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there any way to do it?
> Thanks a lot
|||Hi,
I was about to say the same, but Systemspecialist has already installed,
applied service pack and attached the user databases.
The steps you supposed to do is:-
1. Copy all the MDF and LDF (Including system databases) to a safe directory
2. Install SQl server in same directory as old and apply the same service
pack as old
3. Stop SQL server and SQL Agent
4. Copy all the MDF and LDF files to the same folder
5. STart sql server.
This will start the sql server with the same setup as old.
Thanks
Hari
MCDBA
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:up6u5nJREHA.3300@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Systemspecialist,
> Build your SQL Server directory structure *exactly* the same as it was
> set up on the old server, and place the .mdf and .ldf files in the same
> locations, then start SQL Server. It should just start up. Make sure you
> have the same service pack applied on the new box as you did on the old,
> before copying the data files over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Systemspecialist wrote:
server, I attach user databases, everythink ok, but i need restore master
and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf.
Is there any way to do it?
>
|||Thanks Hari, you're saying the same thing as me, just better.
Hari wrote:
> Hi,
> I was about to say the same, but Systemspecialist has already installed,
> applied service pack and attached the user databases.
> The steps you supposed to do is:-
> 1. Copy all the MDF and LDF (Including system databases) to a safe directory
> 2. Install SQl server in same directory as old and apply the same service
> pack as old
> 3. Stop SQL server and SQL Agent
> 4. Copy all the MDF and LDF files to the same folder
> 5. STart sql server.
> This will start the sql server with the same setup as old.
> Thanks
> Hari
> MCDBA
>

Restore msdb and master db from files.

Hi all
My SQL Server crashed and I don't have any backup. So, I install new server, I attach user databases, everythink ok, but i need restore master and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there any way to do it
Thanks a lotSystemspecialist,
Build your SQL Server directory structure *exactly* the same as it was
set up on the old server, and place the .mdf and .ldf files in the same
locations, then start SQL Server. It should just start up. Make sure you
have the same service pack applied on the new box as you did on the old,
before copying the data files over.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Systemspecialist wrote:
> Hi all,
> My SQL Server crashed and I don't have any backup. So, I install new server, I attach user databases, everythink ok, but i need restore master and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there any way to do it?
> Thanks a lot|||Hi,
I was about to say the same, but Systemspecialist has already installed,
applied service pack and attached the user databases.
The steps you supposed to do is:-
1. Copy all the MDF and LDF (Including system databases) to a safe directory
2. Install SQl server in same directory as old and apply the same service
pack as old
3. Stop SQL server and SQL Agent
4. Copy all the MDF and LDF files to the same folder
5. STart sql server.
This will start the sql server with the same setup as old.
Thanks
Hari
MCDBA
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:up6u5nJREHA.3300@.TK2MSFTNGP09.phx.gbl...
> Systemspecialist,
> Build your SQL Server directory structure *exactly* the same as it was
> set up on the old server, and place the .mdf and .ldf files in the same
> locations, then start SQL Server. It should just start up. Make sure you
> have the same service pack applied on the new box as you did on the old,
> before copying the data files over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Systemspecialist wrote:
> > Hi all,
> > My SQL Server crashed and I don't have any backup. So, I install new
server, I attach user databases, everythink ok, but i need restore master
and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf.
Is there any way to do it?
> >
> > Thanks a lot
>|||Thanks Hari, you're saying the same thing as me, just better.
Hari wrote:
> Hi,
> I was about to say the same, but Systemspecialist has already installed,
> applied service pack and attached the user databases.
> The steps you supposed to do is:-
> 1. Copy all the MDF and LDF (Including system databases) to a safe directory
> 2. Install SQl server in same directory as old and apply the same service
> pack as old
> 3. Stop SQL server and SQL Agent
> 4. Copy all the MDF and LDF files to the same folder
> 5. STart sql server.
> This will start the sql server with the same setup as old.
> Thanks
> Hari
> MCDBA
>

Restore msdb and master db from files.

Hi all,
My SQL Server crashed and I don't have any backup. So, I install new server,
I attach user databases, everythink ok, but i need restore master and msdb
databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there a
ny way to do it?
Thanks a lotSystemspecialist,
Build your SQL Server directory structure *exactly* the same as it was
set up on the old server, and place the .mdf and .ldf files in the same
locations, then start SQL Server. It should just start up. Make sure you
have the same service pack applied on the new box as you did on the old,
before copying the data files over.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Systemspecialist wrote:

> Hi all,
> My SQL Server crashed and I don't have any backup. So, I install new serve
r, I attach user databases, everythink ok, but i need restore master and msd
b databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there
any way to do it?
> Thanks a lot|||Hi,
I was about to say the same, but Systemspecialist has already installed,
applied service pack and attached the user databases.
The steps you supposed to do is:-
1. Copy all the MDF and LDF (Including system databases) to a safe directory
2. Install SQl server in same directory as old and apply the same service
pack as old
3. Stop SQL server and SQL Agent
4. Copy all the MDF and LDF files to the same folder
5. STart sql server.
This will start the sql server with the same setup as old.
Thanks
Hari
MCDBA
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:up6u5nJREHA.3300@.TK2MSFTNGP09.phx.gbl...
> Systemspecialist,
> Build your SQL Server directory structure *exactly* the same as it was
> set up on the old server, and place the .mdf and .ldf files in the same
> locations, then start SQL Server. It should just start up. Make sure you
> have the same service pack applied on the new box as you did on the old,
> before copying the data files over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Systemspecialist wrote:
>
server, I attach user databases, everythink ok, but i need restore master
and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf.
Is there any way to do it?[vbcol=seagreen]
>|||Thanks Hari, you're saying the same thing as me, just better.
Hari wrote:
> Hi,
> I was about to say the same, but Systemspecialist has already installed,
> applied service pack and attached the user databases.
> The steps you supposed to do is:-
> 1. Copy all the MDF and LDF (Including system databases) to a safe directo
ry
> 2. Install SQl server in same directory as old and apply the same service
> pack as old
> 3. Stop SQL server and SQL Agent
> 4. Copy all the MDF and LDF files to the same folder
> 5. STart sql server.
> This will start the sql server with the same setup as old.
> Thanks
> Hari
> MCDBA
>

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.

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.

|||

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