New to SQL - Backup Fails

Bear with me, as I am new to SQL and have quite simply been thrown into this situation.

I have set up a simple backup to DVD that fails. The log file is attached. I'm sure that there's an obvious answer here, but I don't know what to look for. Until we find a new database guy, I'm it.

Thansk so much in advance for any insight you can provide!

TomWhy to a DVD(?) Why not to disk? It's faster...

And if you're it (Like in tag?) then why not use the maintenance wizard?|||well, your errors relate to what you're asking in the first command, - set db to single user mode. if there is at least one more connection (which can be even yours0 the command will fail. you can write a simple script that would create a script that kills all connections to that database. fire that script before running sqlmaint and set a job step dependency "on success" with sqlmaint step.|||Just to be clear: ms_sql_dba is right; your job is failing because your database is not in single user mode. SQL backups can run with multiple users logged in, but in your case, the first task in your job is to "repair" the database.

I'm guessing that you are using the maintenance wizard; if so, skip the Integrity page on the maintenance wizard (make sure nothing is checked).

After doing that, your job should run okay.

Regards,

hmscott|||Thanks to all or you for your help.

Skipping the integrity page in the maintenance wizard cleared it up!

Hopefully I won't have to do this very long!

Regards,

Tom

new to notifications services and want example of setting up email notifications

I am new to SQL 2005 and want to setup a notification service to email me when a job fails. How do I begin to set that up? Any basic examples I can check out?

This forum is dedicate to SQL Server Notification Services; you'll probably have more luck getting responses in the SQL Server Tools forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1) or the microsoft.public.sqlserver.server newsgroup.

HTH...

sql

new to notifications services and want example of setting up email notifications

I am new to SQL 2005 and want to setup a notification service to email me when a job fails. How do I begin to set that up? Any basic examples I can check out?

This forum is dedicate to SQL Server Notification Services; you'll probably have more luck getting responses in the SQL Server Tools forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1) or the microsoft.public.sqlserver.server newsgroup.

HTH...

new MSDE install fails with "instance name specified is invalid"

I am trying to put SQL Server 2000 and MSDE on same local machine. I install
ed 2000 Developer (from VS Tools for Office)using "typical" install level, u
sing the LocalSystem Account, so no password required. When I run C:\sql2ksp
3\MSDE\setup.exe (generated
by Office 2003 disk), the first thing that happens is dialog box that says "
instance name specified is invalid" and the setup is cancelled. When I tried
to do the MSDE install before the 2000, I got a "Strong SA password is requ
ired - use SAPWD switch to
supply the same", so I installed the 2000 and then came back to try MSDE, if
only because I couldn't figure out how to get Online Books going to find ou
t about how to supply SAPWD. There is no other instance of 2000, and none of
MSDE, on the machine. I t
hought the 2000 instance was unnamed. (?)
I am an almost complete rookie about SQL Server. Any help on instance name w
ould be appreciated, or the SAPWD which may be waiting for me. Thanks !hi Bob,
"Bob P." <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:111B62D3-B753-4A78-88BD-7309173DFE4F@.microsoft.com...
> I am trying to put SQL Server 2000 and MSDE on same local machine. I
installed 2000
> Developer (from VS Tools for Office)using "typical" install level, using
the LocalSystem
>Account, so no password required. When I run C:\sql2ksp3\MSDE\setup.exe
(generated by
>Office 2003 disk), the first thing that happens is dialog box that says
"instance name
>specified is invalid" and the setup is cancelled. When I tried to do the
MSDE install before
>the 2000, I got a "Strong SA password is required - use SAPWD switch to
supply the
>same", so I installed the 2000 and then came back to try MSDE, if only
because I couldn't
>figure out how to get Online Books going to find out about how to supply
SAPWD. There is
>no other instance of 2000, and none of MSDE, on the machine. I thought the
2000 instance
>was unnamed. (?)
> I am an almost complete rookie about SQL Server. Any help on instance name
would be
> appreciated, or the SAPWD which may be waiting for me. Thanks !
the first SQL Server instance you installed hase become the default instance
becouse you did not provide instance name...
in order to install other SQL Server and/or MSDE instancese, they must be
"named" instances...
please see
http://msdn.microsoft.com/library/d...
4xl.asp
for further info about all parameters accepted by the setup.exe installer
bootstrap of MSDE...
you can download Books On Line for free from
http://www.microsoft.com/sql/techin.../2000/books.asp
hth
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.6.0 - DbaMgr ver 0.52.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||See the sp3 readme for the valid options for setup.
814130 INF: How to Secure Network Connectivity for SQL Server 2000 Local
http://support.microsoft.com/?id=814130
829925 Additions to the SQL Server 2000 Desktop Engine (MSDE) Release A
http://support.microsoft.com/?id=829925
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

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.