New spin on SQL Server Express management.

Hi there - I asked the question about automated adminsitration on Express and was given some good solutions since it didn't have SSIS which is used to schedule admin tasks.

HOWEVER, unless I am wrong, I think I heard at PASS that in SP2 SQL Server Express will have maintenance plans -- and thus SSIS? Is this true -- can someone fill me in. I was happy when tooling came down to Express and automated maintenance would help as well.

Thanks

Hi LearningDBA,

You're only option for automated admin of an Express instance is to use the Windows Task scheduler to run SQLCMD scripts. I provided a brief demo for a TechNet webcast - http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032294987&EventCategory=5&culture=en-US&CountryCode=US. You can't create Maint Plans against a SQL Server Express instance because it requires SQL Agent.

Thank you,
Bill Ramos

sql

New Server Registration to External Database

Hi All,

Quick question ...

Is it possible to add an external server registration using SQL Server Management Studio Express CTP?

Reason being I have been doing some work from home and need to port the data back to the office who are on SQL 2000. I have a database with my web hosting company and wanted to export the data to the 'live' database so I could import to the office database - any ideas?

I've tried to connect using my login details and I know the live DB is operational as I can connect through my work system. I just can't seem to be able to do it on my system at home.

Any help would be greatly appreciated as it's been a real pain to get stuff transferred. I've so far had to generate SQL script to create the procs and tables for when I get to work - the proper down side is that I lose the content of the tables which not much right now but will be loads soon!

Many Thanks

--

I have just dicovered that the CTP management studio will not connect to SQL 7 - is there any alternative or does the new version compensate for this?

There is now way to connect to SQL Server 7 in Management Studio(Express Edition or otherwise).

If you want to move the data from 2005 to 2000/7 your best bet is BCP or DTS/SSIS

|||

I'm afraid you will have to excuse my ignorance here as I am not a terribly experienced user of SQL Server. I've only be developing for the last year and a half and have always been able to use Enterprise Manager at work as everything is SQL 2000. This fortunately also connects to SQL 7 but SQL 2005 Express rather conveniently appears to have no easy backward compatibility.

So - what is BCP, DTS/SSIS and where might I find them on SQL 2005 Management Studio CTP - if that's where I should be looking?

So far, in order to migrate the structure of a database from 2005 to 2000 I have had to generate SQL scripts to create the tables and procs. A bit of a nightmare as I lose all data this way. Will the above solution resolve this issue?

Many Thanks

|||

First step is to upgrade from the CTP to the production version...

If you have the scripts then you now need the data, there are 2 choices, use whats called a linked server(Books On Line can help you try this out) or you can generate text files using BCP. DTS/SSIS is not available if you are pushing the data from a 2005 server, however you could try pulling it from the 2005 to the 7 server on the 7 server, ie have DTS in 7.0 connect to 2005.

BTW in your statement above you are mixing up the tools and the products...

EM and QA come with SQL 7 and 2000, the 2000 versions work with 7 and 2000.

SSMS comes with SQL 2005, it works with 2005 and 2000.

Once you have a database in a higher version (2000 or 2005) there is no easy way to go back to an older version (7 or 2000)

New Server Registration to External Database

Hi All,

Quick question ...

Is it possible to add an external server registration using SQL Server Management Studio Express CTP?

Reason being I have been doing some work from home and need to port the data back to the office who are on SQL 2000. I have a database with my web hosting company and wanted to export the data to the 'live' database so I could import to the office database - any ideas?

I've tried to connect using my login details and I know the live DB is operational as I can connect through my work system. I just can't seem to be able to do it on my system at home.

Any help would be greatly appreciated as it's been a real pain to get stuff transferred. I've so far had to generate SQL script to create the procs and tables for when I get to work - the proper down side is that I lose the content of the tables which not much right now but will be loads soon!

Many Thanks

--

I have just dicovered that the CTP management studio will not connect to SQL 7 - is there any alternative or does the new version compensate for this?

There is now way to connect to SQL Server 7 in Management Studio(Express Edition or otherwise).

If you want to move the data from 2005 to 2000/7 your best bet is BCP or DTS/SSIS

|||

I'm afraid you will have to excuse my ignorance here as I am not a terribly experienced user of SQL Server. I've only be developing for the last year and a half and have always been able to use Enterprise Manager at work as everything is SQL 2000. This fortunately also connects to SQL 7 but SQL 2005 Express rather conveniently appears to have no easy backward compatibility.

So - what is BCP, DTS/SSIS and where might I find them on SQL 2005 Management Studio CTP - if that's where I should be looking?

So far, in order to migrate the structure of a database from 2005 to 2000 I have had to generate SQL scripts to create the tables and procs. A bit of a nightmare as I lose all data this way. Will the above solution resolve this issue?

Many Thanks

|||

First step is to upgrade from the CTP to the production version...

If you have the scripts then you now need the data, there are 2 choices, use whats called a linked server(Books On Line can help you try this out) or you can generate text files using BCP. DTS/SSIS is not available if you are pushing the data from a 2005 server, however you could try pulling it from the 2005 to the 7 server on the 7 server, ie have DTS in 7.0 connect to 2005.

BTW in your statement above you are mixing up the tools and the products...

EM and QA come with SQL 7 and 2000, the 2000 versions work with 7 and 2000.

SSMS comes with SQL 2005, it works with 2005 and 2000.

Once you have a database in a higher version (2000 or 2005) there is no easy way to go back to an older version (7 or 2000)

New Query with Current Connection

I am missing "New Query with Current Connection" button in SQL Editor in the
SQL management studio. Few posts on the Web refer explain that this button
should allow me to open a new query in the existing window.
I've checked "Add Remove Buttons" for SQL editor but I still cannot find it.
Any ideas?
Bojan Kuhar wrote:
> I am missing "New Query with Current Connection" button in SQL Editor in the
> SQL management studio. Few posts on the Web refer explain that this button
> should allow me to open a new query in the existing window.
> I've checked "Add Remove Buttons" for SQL editor but I still cannot find it.
> Any ideas?
I'm not sure it can be done, but "Ctrl-N" will give you the "New query
with current connection".
Regards
Steen
|||Fantastic.
Now, one more. Is there something like "Open Query with Current Connection".
I'd like to avoid the Connect to Database Enginde dialog.
More-less like Qury Analyzer that allows you to open a new query in the
existing window.
Regards
Bojan
"Steen Persson (DK)" wrote:

> Bojan Kuhar wrote:
> I'm not sure it can be done, but "Ctrl-N" will give you the "New query
> with current connection".
> Regards
> Steen
>
|||My fault. The button was called New Query with Current Connection in one of
the betas. The released version renamed it to New Query. I changed most of
the references, but I missed a few. I think I got rid of them all by web
release 1 of Books Online though.
The New Query button will use the connection that has focus when you click
it. Either Object Explorer, or an existing Query Editor window. If your
current context has no connection, then it will pop up the Connect to Server
dialog box.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Bojan Kuhar" <BojanKuhar@.discussions.microsoft.com> wrote in message
news:01230DB5-BEE5-4452-B89E-470DCCE488F2@.microsoft.com...
>I am missing "New Query with Current Connection" button in SQL Editor in
>the
> SQL management studio. Few posts on the Web refer explain that this
> button
> should allow me to open a new query in the existing window.
> I've checked "Add Remove Buttons" for SQL editor but I still cannot find
> it.
> Any ideas?
|||Bojan Kuhar (BojanKuhar@.discussions.microsoft.com) writes:
> Fantastic.
> Now, one more. Is there something like "Open Query with Current
> Connection". I'd like to avoid the Connect to Database Enginde dialog.
> More-less like Qury Analyzer that allows you to open a new query in the
> existing window.
What Steen said, CTRL-N is the key. Provided one thing: under Tools->
Options->Keyboard select SQL 2000 instead of Standard.
The New Query button also works if you are in a query window or Object
Explorer, and so does File->New->Query with Current Connection.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

New Query Connect Dialog

The "Microsoft SQL Server Management Studio" opens a connection dialog with every new query. Please tell me there is setting to prevent this.

Thanx,

Greg

The popup of the dialog is related to your current context. If you have object explorer open and a server connected then the query will be connected to that server. If you have an existing query window connected it should connect to that server.

If working disconnected then you will get the pop up.

|||

Thank you for your reply, your answer does work nicely. It would seem this would be something you could define a default for. It is taking a little time to get used to the SQL 2005 tools after using SQL 2000 for so long.

If you are a member of Experts-Exchange.com please visit this link and post your answer to receive the allotted points. If after 48 hours or so I have not seen your post there I will post your answer for the benefit of others and close the question in that forum.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21773295.html

Regards,

Greg

|||Done, haven't answered a question on EE for a while|||

EE Saves my bacon from time to time:)

Your blog site is very kewl, you must be a busy person.

Sent an email to your yahoo address Returned Undeliverable.

|||I think this is semi-related to your thread, so I hope it's cool to post this here. If you have a query window in SSMS, and drag and drop one or more script files on it, it prompts you for the database to connect to, for each file you dropped. In 2000 query analyzer, it will automatically connect each file to the same server and database. Do you know of a way to accomplish this in 2005?|||I don't believe so.

New Query Connect Dialog

The "Microsoft SQL Server Management Studio" opens a connection dialog with every new query. Please tell me there is setting to prevent this.

Thanx,

Greg

The popup of the dialog is related to your current context. If you have object explorer open and a server connected then the query will be connected to that server. If you have an existing query window connected it should connect to that server.

If working disconnected then you will get the pop up.

|||

Thank you for your reply, your answer does work nicely. It would seem this would be something you could define a default for. It is taking a little time to get used to the SQL 2005 tools after using SQL 2000 for so long.

If you are a member of Experts-Exchange.com please visit this link and post your answer to receive the allotted points. If after 48 hours or so I have not seen your post there I will post your answer for the benefit of others and close the question in that forum.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21773295.html

Regards,

Greg

|||Done, haven't answered a question on EE for a while|||

EE Saves my bacon from time to time:)

Your blog site is very kewl, you must be a busy person.

Sent an email to your yahoo address Returned Undeliverable.

|||I think this is semi-related to your thread, so I hope it's cool to post this here. If you have a query window in SSMS, and drag and drop one or more script files on it, it prompts you for the database to connect to, for each file you dropped. In 2000 query analyzer, it will automatically connect each file to the same server and database. Do you know of a way to accomplish this in 2005?|||I don't believe so.

New Installation can't connect to default database

I installed SQL Server 2005. Connected to an external dB on a different
server just fine. Now am trying to connect (via SQL Server Management Studio
)
to the default local dB. I get the following error:
Cannot connect to <server name in here>
A connection was successfully established with the server, but then an error
occurred during the login process (provider shared memory provider error 0 -
no process is on the other end of the pipe). Microsoft SQL Server error 23
3.
any ideas what's wrong?Run the SQL Server Configuration Manager and click on the Protocols for that
instance. Check to make sure that you enable Shared Memory.
Andrew J. Kelly SQL MVP
"ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
news:5298A83E-8980-46E7-BC6F-DB283673B3E0@.microsoft.com...
>I installed SQL Server 2005. Connected to an external dB on a different
> server just fine. Now am trying to connect (via SQL Server Management
> Studio)
> to the default local dB. I get the following error:
> Cannot connect to <server name in here>
> A connection was successfully established with the server, but then an
> error
> occurred during the login process (provider shared memory provider error
> 0 -
> no process is on the other end of the pipe). Microsoft SQL Server error
> 233.
> any ideas what's wrong?|||Shared memory is enabled as is TCP/IP. Shared Pipes was not, and I enabled
that. VIA remains disabled. I can't start the SQL Serveer Agent, either. It
is set to Manual and if I try to start it, I get a message that it started
and stopped again.
"Andrew J. Kelly" wrote:

> Run the SQL Server Configuration Manager and click on the Protocols for th
at
> instance. Check to make sure that you enable Shared Memory.
> --
> Andrew J. Kelly SQL MVP
>
> "ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
> news:5298A83E-8980-46E7-BC6F-DB283673B3E0@.microsoft.com...
>
>|||What do the error and Agent logs say?
Andrew J. Kelly SQL MVP
"ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
news:4C86F2E6-A2CF-486B-8967-873B8A2D7F1B@.microsoft.com...[vbcol=seagreen]
> Shared memory is enabled as is TCP/IP. Shared Pipes was not, and I enabled
> that. VIA remains disabled. I can't start the SQL Serveer Agent, either.
> It
> is set to Manual and if I try to start it, I get a message that it started
> and stopped again.
> "Andrew J. Kelly" wrote:
>|||There was an application event (MSSQLSERVER) that said that the max number o
f
2 user connections had been reached and the connection was closed
"Andrew J. Kelly" wrote:

> What do the error and Agent logs say?
> --
> Andrew J. Kelly SQL MVP
>
> "ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
> news:4C86F2E6-A2CF-486B-8967-873B8A2D7F1B@.microsoft.com...
>
>|||I re-installed SQL and opened the default db first and now it seems to be
working. Very weird.
"Andrew J. Kelly" wrote:

> What do the error and Agent logs say?
> --
> Andrew J. Kelly SQL MVP
>
> "ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
> news:4C86F2E6-A2CF-486B-8967-873B8A2D7F1B@.microsoft.com...
>
>

New Installation can't connect to default database

I installed SQL Server 2005. Connected to an external dB on a different
server just fine. Now am trying to connect (via SQL Server Management Studio)
to the default local dB. I get the following error:
Cannot connect to <server name in here>
A connection was successfully established with the server, but then an error
occurred during the login process (provider shared memory provider error 0 -
no process is on the other end of the pipe). Microsoft SQL Server error 233.
any ideas what's wrong?
Run the SQL Server Configuration Manager and click on the Protocols for that
instance. Check to make sure that you enable Shared Memory.
Andrew J. Kelly SQL MVP
"ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
news:5298A83E-8980-46E7-BC6F-DB283673B3E0@.microsoft.com...
>I installed SQL Server 2005. Connected to an external dB on a different
> server just fine. Now am trying to connect (via SQL Server Management
> Studio)
> to the default local dB. I get the following error:
> Cannot connect to <server name in here>
> A connection was successfully established with the server, but then an
> error
> occurred during the login process (provider shared memory provider error
> 0 -
> no process is on the other end of the pipe). Microsoft SQL Server error
> 233.
> any ideas what's wrong?
|||Shared memory is enabled as is TCP/IP. Shared Pipes was not, and I enabled
that. VIA remains disabled. I can't start the SQL Serveer Agent, either. It
is set to Manual and if I try to start it, I get a message that it started
and stopped again.
"Andrew J. Kelly" wrote:

> Run the SQL Server Configuration Manager and click on the Protocols for that
> instance. Check to make sure that you enable Shared Memory.
> --
> Andrew J. Kelly SQL MVP
>
> "ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
> news:5298A83E-8980-46E7-BC6F-DB283673B3E0@.microsoft.com...
>
>
|||What do the error and Agent logs say?
Andrew J. Kelly SQL MVP
"ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
news:4C86F2E6-A2CF-486B-8967-873B8A2D7F1B@.microsoft.com...[vbcol=seagreen]
> Shared memory is enabled as is TCP/IP. Shared Pipes was not, and I enabled
> that. VIA remains disabled. I can't start the SQL Serveer Agent, either.
> It
> is set to Manual and if I try to start it, I get a message that it started
> and stopped again.
> "Andrew J. Kelly" wrote:
|||There was an application event (MSSQLSERVER) that said that the max number of
2 user connections had been reached and the connection was closed
"Andrew J. Kelly" wrote:

> What do the error and Agent logs say?
> --
> Andrew J. Kelly SQL MVP
>
> "ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
> news:4C86F2E6-A2CF-486B-8967-873B8A2D7F1B@.microsoft.com...
>
>
|||I re-installed SQL and opened the default db first and now it seems to be
working. Very weird.
"Andrew J. Kelly" wrote:

> What do the error and Agent logs say?
> --
> Andrew J. Kelly SQL MVP
>
> "ChristieD" <ChristieD@.discussions.microsoft.com> wrote in message
> news:4C86F2E6-A2CF-486B-8967-873B8A2D7F1B@.microsoft.com...
>
>

New Installation - Can Not Connet to Server On My PC

I have just installed SQL Express and Server Management Studio on my PC. I also have IIS 5.0 on WinXP. When I open Management Studio and try to connect the sI get the following error message:- Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider) .

This happens if I change the server, change the authentication method or try my user name or the 'sa ' user. To make things even more frustrating I did the same installation on my laptop earlier this year with no problems.

Has anyone any answers to my problem?

Thanks

hi,

having modified the authentication property (and restarted the service), did you enable the sa login?.. verify in the Instance->Security->Logins->sa Properties (Status tab) the login is enabled..

regards

New Install VS 2005 Pro & SQL Server 2005 Dev Problem

VS looks OK and MSSQLSERVER and SQLEXPRESS etc. show in 'Services/Local' as started/automatic.

I am trying to do Management Studio Tutorial. For the change from SEM.

The problem is in opening SQL Server Management Studio there is a dialog

'Connect to Server' showing Type = Database engine, Authentication = Windows Authentication, Server name = blank.

When I enter Server name = MSSQLSERVER and press Connect I get an error message 'Cannot connect to MSSQLSERVER… Error 53.

How do I get to see the tutorial and recognise the SQL?

XP.Pro on a big fast machine for Web development all on the one machine

Some experience of SQL Server on W2K and W2K3 with VS. Notice the SQL Server does not show in the task bar as before.

To answer my own question in case it helps others.

This looks like Catch22 at first sight.

In 'Connect to Server' dialog Click 'Options'.

See a change to two tabs, 'Local' and 'Connection Properties'.

Click 'Server Name' and browse for more. Click.

Shows Browse for Servers and my two instances, with names different to what shows in Services. Is this for security?

Select, click Connect and the SSMC shows with 7 items, hurray.

The annoying thing is there is nothing to indicate how to do this on the dialog.

The Help topic of the dialog name does not help, as usual.

Polite comment. I notice there are few queries similar to my question in this forum. Does this mean I am unusual or people have a better place to go for such.

I will certainly be a frequent user of the forum for Web applications in VS/VB.

|||Thank you for sharing your solution. it may help others in the future.

Derekfoxes wrote:

Polite comment. I notice there are few queries similar to my question in this forum. Does this mean I am unusual or people have a better place to go for such.

There are a lot of people here who are very conversant with SQL Server. But installation and setup for SQL Server 2005 is relatively new and we're not yet used to all of the common pitfalls and their solutions. TheSQL Server newsgroups are probably the best place for that level of question as more of the SQL Server MVPs tend to hang out there, or perhaps theSQL Server Forums at the MSDN Forums.|||

Thanks for the reply and the two sites.

Trying to install Adventure Works now.

Tiny point, the forum ack and notifications are arriving duplicated.

|||

Derekfoxes wrote:

Tiny point, the forum ack and notifications are arriving duplicated.

Thanks for the alert; we areaware of the problem and the source code update should be forthcoming.

New Install of Sql server Express fails to restore Master database

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?

the error indicates the backupset does not contain a backup of master. do 'restore headeronly' and verify its contents.

aslo, you will want to name the system database as "master" (all lower case) and do not include the "move" option as part of your system database restore.

|||

Thanks for the reply,

I change the *.sql restore script to:

RESTORE DATABASE [Master] FROM DISK = N'E:\COPLEYNEWSDATABASEBACKUP\Master.bak' WITH FILE= 1, NOUNLOAD, REPLACE, STATS = 10

GO

Now I get the error that I am restoring a Master.bak file to a SQL Server 9.0.1399 and I need to restore it to a SQL Server 9.0.2047 server.

So, right know 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?

Thanks in advance.

|||

version 9.0.1399 is RTM and 9.0.2047 is sp1.

you want to download the latest from site. it's sp1 patched.

http://msdn.microsoft.com/vstudio/express/sql/download/

|||

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.

New database not included in "Back Up Database Task"

I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.

I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.

Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.

Thanks for your help,

Jonathan.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Hi,

I tested this on a SQL SP2 build 3175 and could not reproduce the issue.

Any newly added database automatically shows-up in the list.

I advise to patch and re-test.

Regards,

Philippe

|||After doing further tests, I determined that not all newly created databases are excluded from the backup. However, it appears that databases that were created on this SQL Server 2005 database and then had data restored from a .bak file from a SQL Server 7 database server are being excluded. I'm guessing it's a permission problem.

Can someone tell me exactly what users and/or roles need to be added to my databases in order for them to be backed up via the "Back UP Database Task"? Alternatively, if there's another non-permission solution, please let me know.

Thanks,

Jonathan.

|||

You must change the compatibility mode of your Database to SQL server 2005 this way it will allow all the features of SQL server 2005 to be used.... such as the backup.

Cheers

Byblyk

|||

Yep, that does it. For any others that run into the same problem and need a bit more handholding, open up "SQL Server Management Studio". Under "Databases", select right click your database and select "Properties". Under the "Options" page, set the "Compatability level" to "SQL Server 2005 (90)".

Note, it's important to make sure you check what changes to the compatability level mean before setting them willy nilly. Here's a useful guide:

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Thanks Byblyk!

New database not included in "Back Up Database Task"

I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.

I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.

Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.

Thanks for your help,

Jonathan.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Hi,

I tested this on a SQL SP2 build 3175 and could not reproduce the issue.

Any newly added database automatically shows-up in the list.

I advise to patch and re-test.

Regards,

Philippe

|||After doing further tests, I determined that not all newly created databases are excluded from the backup. However, it appears that databases that were created on this SQL Server 2005 database and then had data restored from a .bak file from a SQL Server 7 database server are being excluded. I'm guessing it's a permission problem.

Can someone tell me exactly what users and/or roles need to be added to my databases in order for them to be backed up via the "Back UP Database Task"? Alternatively, if there's another non-permission solution, please let me know.

Thanks,

Jonathan.

|||

You must change the compatibility mode of your Database to SQL server 2005 this way it will allow all the features of SQL server 2005 to be used.... such as the backup.

Cheers

Byblyk

|||

Yep, that does it. For any others that run into the same problem and need a bit more handholding, open up "SQL Server Management Studio". Under "Databases", select right click your database and select "Properties". Under the "Options" page, set the "Compatability level" to "SQL Server 2005 (90)".

Note, it's important to make sure you check what changes to the compatability level mean before setting them willy nilly. Here's a useful guide:

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Thanks Byblyk!

New database not included in "Back Up Database Task"

I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.

I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.

Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.

Thanks for your help,

Jonathan.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Hi,

I tested this on a SQL SP2 build 3175 and could not reproduce the issue.

Any newly added database automatically shows-up in the list.

I advise to patch and re-test.

Regards,

Philippe

|||After doing further tests, I determined that not all newly created databases are excluded from the backup. However, it appears that databases that were created on this SQL Server 2005 database and then had data restored from a .bak file from a SQL Server 7 database server are being excluded. I'm guessing it's a permission problem.

Can someone tell me exactly what users and/or roles need to be added to my databases in order for them to be backed up via the "Back UP Database Task"? Alternatively, if there's another non-permission solution, please let me know.

Thanks,

Jonathan.

|||

You must change the compatibility mode of your Database to SQL server 2005 this way it will allow all the features of SQL server 2005 to be used.... such as the backup.

Cheers

Byblyk

|||

Yep, that does it. For any others that run into the same problem and need a bit more handholding, open up "SQL Server Management Studio". Under "Databases", select right click your database and select "Properties". Under the "Options" page, set the "Compatability level" to "SQL Server 2005 (90)".

Note, it's important to make sure you check what changes to the compatability level mean before setting them willy nilly. Here's a useful guide:

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Thanks Byblyk!

New Data Connection Problem in VS.NET 2005

I have all of the prerequisites installed:

- SQL Server 2005 Compact Edition

- Visual Studio 2005 SP1 (Standard Edition)

- SQL Server Management Studio Express SP2

When trying to add a new Compact Edition SQL DB data connection from within Visual Studio ("SQL Mobile", "SQL Compact...", etc.), nothing is available for it. I have Access, ODBC, SQL Server, SQL DB File, Oracle, and Other. Even when selecting other, there are no Compact/Mobile types available.

Please Help...I've been going crazy trying to get this option.

Thank you

Anybody? Anybody at all?|||

I guess you'll have to settle for me.

I too have had problems installing the lastest SP of Visual Studio (VS) which includes the latest SQL Server Compact Edition (SQLCe) binaries. The problem is that you must make sure that if you've installed any CTPs they must be scrubbed off first. So, assuming you've done that or never installed the beta CTPs (which were used to implement SQL Server Everywhere Edition), you need to first install VS 2005 SP1 from http://msdn2.microsoft.com/en-us/vstudio/bb265237.aspx. Next, you might also need to install the SQLCe MSI to get the bit installed in the GAC. These bits can be found here http://www.microsoft.com/sql/editions/compact/downloads.mspx. As shown in my EBook "Hitchhiker's Guide to SQL Server 2005 Compact Edition", you can also download the SDK but you should now see the SQL Server Compact Edition as one of the choices when creating a Data Connection.

hth

|||APipeDream

I am having the same problem - did you get anywhere with it ?
|||

No, that's the order I used already, and I don't have any CTPs installed. I also have the SDK installed.

|||

So am I.

I uninstalled/re-installed all componenets still no luck.

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Gotta create a DDEX Provider?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1477140&SiteID=1

|||

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Thanks This worked:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

New Data Connection Problem in VS.NET 2005

I have all of the prerequisites installed:

- SQL Server 2005 Compact Edition

- Visual Studio 2005 SP1 (Standard Edition)

- SQL Server Management Studio Express SP2

When trying to add a new Compact Edition SQL DB data connection from within Visual Studio ("SQL Mobile", "SQL Compact...", etc.), nothing is available for it. I have Access, ODBC, SQL Server, SQL DB File, Oracle, and Other. Even when selecting other, there are no Compact/Mobile types available.

Please Help...I've been going crazy trying to get this option.

Thank you

Anybody? Anybody at all?|||

I guess you'll have to settle for me.

I too have had problems installing the lastest SP of Visual Studio (VS) which includes the latest SQL Server Compact Edition (SQLCe) binaries. The problem is that you must make sure that if you've installed any CTPs they must be scrubbed off first. So, assuming you've done that or never installed the beta CTPs (which were used to implement SQL Server Everywhere Edition), you need to first install VS 2005 SP1 from http://msdn2.microsoft.com/en-us/vstudio/bb265237.aspx. Next, you might also need to install the SQLCe MSI to get the bit installed in the GAC. These bits can be found here http://www.microsoft.com/sql/editions/compact/downloads.mspx. As shown in my EBook "Hitchhiker's Guide to SQL Server 2005 Compact Edition", you can also download the SDK but you should now see the SQL Server Compact Edition as one of the choices when creating a Data Connection.

hth

|||APipeDream

I am having the same problem - did you get anywhere with it ?
|||

No, that's the order I used already, and I don't have any CTPs installed. I also have the SDK installed.

|||

So am I.

I uninstalled/re-installed all componenets still no luck.

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Gotta create a DDEX Provider?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1477140&SiteID=1

|||

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Thanks This worked:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

New Data Connection Problem in VS.NET 2005

I have all of the prerequisites installed:

- SQL Server 2005 Compact Edition

- Visual Studio 2005 SP1 (Standard Edition)

- SQL Server Management Studio Express SP2

When trying to add a new Compact Edition SQL DB data connection from within Visual Studio ("SQL Mobile", "SQL Compact...", etc.), nothing is available for it. I have Access, ODBC, SQL Server, SQL DB File, Oracle, and Other. Even when selecting other, there are no Compact/Mobile types available.

Please Help...I've been going crazy trying to get this option.

Thank you

Anybody? Anybody at all?|||

I guess you'll have to settle for me.

I too have had problems installing the lastest SP of Visual Studio (VS) which includes the latest SQL Server Compact Edition (SQLCe) binaries. The problem is that you must make sure that if you've installed any CTPs they must be scrubbed off first. So, assuming you've done that or never installed the beta CTPs (which were used to implement SQL Server Everywhere Edition), you need to first install VS 2005 SP1 from http://msdn2.microsoft.com/en-us/vstudio/bb265237.aspx. Next, you might also need to install the SQLCe MSI to get the bit installed in the GAC. These bits can be found here http://www.microsoft.com/sql/editions/compact/downloads.mspx. As shown in my EBook "Hitchhiker's Guide to SQL Server 2005 Compact Edition", you can also download the SDK but you should now see the SQL Server Compact Edition as one of the choices when creating a Data Connection.

hth

|||APipeDream

I am having the same problem - did you get anywhere with it ?
|||

No, that's the order I used already, and I don't have any CTPs installed. I also have the SDK installed.

|||

So am I.

I uninstalled/re-installed all componenets still no luck.

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Gotta create a DDEX Provider?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1477140&SiteID=1

|||

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Thanks This worked:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.