How to Schedule MSDE Backup

Hi guys,
I have an application that runs on MSDE. I need to back up the database on a
dialy basis. The backup file gets written to this folder (C:\backup) and
then I have found a backup software that will automatically write it to a
DVD+RW, overwriting the previous backup file.
To make the schedule easy I will use 31 DVD+RWs, one for each day of the
month. That way I will always have at least a month worth of backups.
I was hoping that somebody could help me with a small SQL script that will
backup my database from Monday thru Saturday, automatically. Also it'd be
great if the backup file would contain a name and the date of that day.
Thanks a lot for your help.
Hi,
MSDE will not come with GUI. So u have to use SQLMAINT.exe. See the below
URL for more info.
http://msdn.microsoft.com/library/de...maint_19ix.asp
Thanks
Hari
SQL Server MVP
"Tom Bombadill" <Genius_poster@.yahoo.com> wrote in message
news:%23D3lQ7zsFHA.2792@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have an application that runs on MSDE. I need to back up the database on
> a dialy basis. The backup file gets written to this folder (C:\backup) and
> then I have found a backup software that will automatically write it to a
> DVD+RW, overwriting the previous backup file.
> To make the schedule easy I will use 31 DVD+RWs, one for each day of the
> month. That way I will always have at least a month worth of backups.
> I was hoping that somebody could help me with a small SQL script that will
> backup my database from Monday thru Saturday, automatically. Also it'd be
> great if the backup file would contain a name and the date of that day.
> Thanks a lot for your help.
>
|||Hi Hari,
Thanks for your attempt to help me. Unfortunately for your suggestion to
work, I have to install SP3a and for some reason I cannot successfully apply
that. I constantly get the "The instance name specified is invalid" error,
even though I did not specify an instance name and there's only one
installaiton of MSDE on my machine.
What do you suggest?
|||hi Tom,
Tom Bombadill wrote:
> Hi Hari,
> Thanks for your attempt to help me. Unfortunately for your suggestion
> to work, I have to install SP3a and for some reason I cannot
> successfully apply that. I constantly get the "The instance name
> specified is invalid" error, even though I did not specify an
> instance name and there's only one installaiton of MSDE on my machine.
> What do you suggest?
you can have a look at a free prj of mine, at the link following my sign,
qhich implements a UI similar to Enterprise Manager where you can define a
SQL Server job to backup your db and of course define your schedules as
desired...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>
> you can have a look at a free prj of mine, at the link following my
> sign, qhich implements a UI similar to Enterprise Manager where you
> can define a SQL Server job to backup your db and of course define
> your schedules as desired...
alternatively, you can prepare a cmd file like
<-->
OSQL -Usa -Pyour_pwd -Q"BACKUP DATABASE pubs TO DISK = 'C:\Pubs.bak' WITH
INIT" >c:\err.txt
<-->
and schedule it via standard OS AT or SCHTASKS, the native Win scheduler...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
You're an angel, thanks a lot!
I downloaded your interface and installed it. I scheduled a backup and will
be testing this thoroughly.
A couple of questions re the interface if you don't mind:
1- I don't want to accumulate backups to the same file, since I will be
taking a fresh full backup on a separate DVD+RW everyday. How do I make sure
the backups do not keep appending to the same file day after day? Do I do
that just by leaving the ADD box unchecked, in the backup properties?
2- How can make it so that the backup file contains the day of the backup?
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Andrea,
> You're an angel, thanks a lot!
you are wellcome.. thank you :D

> I downloaded your interface and installed it. I scheduled a backup
> and will be testing this thoroughly.
> A couple of questions re the interface if you don't mind:
> 1- I don't want to accumulate backups to the same file, since I will
> be taking a fresh full backup on a separate DVD+RW everyday. How do I
> make sure the backups do not keep appending to the same file day
> after day? Do I do that just by leaving the ADD box unchecked, in the
> backup properties?
leaving the "add to media" check box unchecked will add the "WITH INIT"
statement to the full backup statement, so that every backup set will be
initialized..

> 2- How can make it so that the backup file contains the day of the
> backup?
can you please expand on this? I'm sorry but I did not understand your
requirement..
and for DbaMgr2k related questions please feel free to contact me directly,
in order not to be OT on this public microsoft NG...
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
[vbcol=seagreen]
What I mean by that is to have the backup file name include the date the
backup was performed. I'll give you an example:
For today, it would be called "SJDB 09072005.bak"
For tomorrow, "SJDB 09082005.bak"
For the day after tomorrow, "SJDB 09092005.bak"
And so on...
I hope that makes sense!
Andrea, once again thank you for your service to the community and sharing
your hard work with others.
|||Also Andrea,
Do you know how long it would take for a full backup to run for a full size
MSDE database (2048 MB)?
I ask because I wanted to know how much time to allocate before I schedule
the copy of the backup file to DVD.
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Hi Andrea,
>
> What I mean by that is to have the backup file name include the date
> the backup was performed. I'll give you an example:
> For today, it would be called "SJDB 09072005.bak"
> For tomorrow, "SJDB 09082005.bak"
> For the day after tomorrow, "SJDB 09092005.bak"
> And so on...
> I hope that makes sense!

ok, you have to "edit" the backup statement of the generated job's step ...
assuming you are backing up pubs database to C:\ (replace C:\ with the
existing folder you want to backup to), you can provide the file name to
include the current date casting (
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp )
GETDATE() function result as required..
the final statement will look like:
DECLARE @.FileName nvarchar(25)
SET @.FileName = 'C:\' + 'pubs ' + CONVERT(varchar(10) , GETDATE() , 110 ) +
'.bak'
BACKUP DATABASE [pubs] TO DISK = @.FileName WITH INIT ,
NOUNLOAD ,
NAME = N'pubs BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT
as regard your date format, I'd prefer the standard ISO format, thats to say
YYYYMMDD (CONVERT using 112 style), as this data format , 20050908 (for
today) is more readable and sorts better then 09082005..
you can go further, in the "advanced tab", and specify you want to "output"
the result of the execution (like standard messages and/or errors, if any)
to a text file.. if you want to, specify a file name (a text file) in the
"Transact-SQL script command options" ->output file
this will output, in case of success, something like
<-->
Job 'BackUp DB ['pubs'] - #08/09/2005 12.25.12#' : Step 1, 'BackUp DB
['pubs']' : Began Executing 2005-09-08 12:37:16
53 percent backed up. [SQLSTATE 01000]
99 percent backed up. [SQLSTATE 01000]
Processed 224 pages for database 'pubs', file 'pubs' on file 1. [SQLSTATE
01000]
100 percent backed up. [SQLSTATE 01000]
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1. [SQLSTATE
01000]
BACKUP DATABASE successfully processed 225 pages in 0.242 seconds (7.586
MB/sec). [SQLSTATE 01000]
<-->
or the problem found, if any, you can inspect...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to schedule backup task

I'm new to sql server. I want to schedule a backup task. But everytime I did
it by right clicking the database -- All Tasks -- Backup database, then
ticking the "Schedule", next time I couldn't find where it is. It's always
unticked. I don't know where is my previous scheduled settings. Am I doing
it in the right way?
Thank u very much for your help.OK
In your SQL Server Server tree in Enterprise Manager you have
Management | SQL Server Agent | Jobs
You should find your job schedules in here
Also look in BOL (Books Online)
Subjects
Jobs (Scheduling, creating)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eomg9#0TDHA.1712@.TK2MSFTNGP11.phx.gbl...
> I'm new to sql server. I want to schedule a backup task. But everytime I
did
> it by right clicking the database -- All Tasks -- Backup database, then
> ticking the "Schedule", next time I couldn't find where it is. It's always
> unticked. I don't know where is my previous scheduled settings. Am I doing
> it in the right way?
> Thank u very much for your help.
>|||In addition to Allan's comments, make sure the SQL Server Agent service is
running and that it's set to run automatically. This service basically
manages all the automatic jobs inside sql server.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eomg9%230TDHA.1712@.TK2MSFTNGP11.phx.gbl...
> I'm new to sql server. I want to schedule a backup task. But everytime I
did
> it by right clicking the database -- All Tasks -- Backup database, then
> ticking the "Schedule", next time I couldn't find where it is. It's always
> unticked. I don't know where is my previous scheduled settings. Am I doing
> it in the right way?
> Thank u very much for your help.
>sql

How to Schedule Backup for SQL Server 2005 Express

How do you schedule to backup database in SQL Server 2005 Express? I tried
following the steps mentioned at http://support.microsoft.com/kb/q241397, but
there are errors .
Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
myBackupScript.sql -n, I received timeout error, telling me that it might be
that my server doesn't allow remote access. So, I open SQL Client Tools, and
run the SQL statements. Then it told me that all of the mentioned stored
procedures does not exist.
Please help> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might be
> that my server doesn't allow remote access.
Use Surface Area Configuration to enable remote access.
> So, I open SQL Client Tools, and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
The procedures are in the msdb database, so you need to be there to run them, or qualifying the proc
name.
But Express doesn't come with Agent, so the steps in this KB doesn't make sense to me for 2005
Express.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397, but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might be
> that my server doesn't allow remote access. So, I open SQL Client Tools, and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Then is there no way to schedule backup? If that's so the log file will very
soon reach its limit and everyone won't be able to use the database, am I
right?
"Tibor Karaszi" wrote:
> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> > myBackupScript.sql -n, I received timeout error, telling me that it might be
> > that my server doesn't allow remote access.
> Use Surface Area Configuration to enable remote access.
>
> > So, I open SQL Client Tools, and
> > run the SQL statements. Then it told me that all of the mentioned stored
> > procedures does not exist.
> The procedures are in the msdb database, so you need to be there to run them, or qualifying the proc
> name.
> But Express doesn't come with Agent, so the steps in this KB doesn't make sense to me for 2005
> Express.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> > How do you schedule to backup database in SQL Server 2005 Express? I tried
> > following the steps mentioned at http://support.microsoft.com/kb/q241397, but
> > there are errors .
> >
> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> > myBackupScript.sql -n, I received timeout error, telling me that it might be
> > that my server doesn't allow remote access. So, I open SQL Client Tools, and
> > run the SQL statements. Then it told me that all of the mentioned stored
> > procedures does not exist.
> >
> > Please help
>
>|||Hi,
To allow remote access remove the database name from namedpipes in
network configuration.All the stored procedure exists in master
database.
U can schedule them as u schedule it in sql server2000. butu have to
use master.storedprocedure name.
HTH
from
Doller|||>remove the database name from namedpipes in network configuration?
Under NamedPipes, it's either enable it or disable it. What do you mean by
removing the name?
"doller" wrote:
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
I run sql statements at master, and msdb, but it returns me this message,
"SQLServerAgent is not currently running so it cannot be notified of this
action." But there is no SQLServerAgent for this version.
"doller" wrote:
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
No! Express doesn't come with Agent.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"doller" <sufianarif@.gmail.com> wrote in message
news:1145431836.282134.66530@.v46g2000cwv.googlegroups.com...
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> Then is there no way to schedule backup?
You can use the scheduler that comes with Windows. Together with SQLCMD.EXE, for instance.
> If that's so the log file will very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
Well, you can set the database to simple recovery. But of course, there are other more important
reasons to do backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:58C5672C-ABC2-4EFA-BB1C-AD5232648F20@.microsoft.com...
> Then is there no way to schedule backup? If that's so the log file will very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
> "Tibor Karaszi" wrote:
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it might be
>> > that my server doesn't allow remote access.
>> Use Surface Area Configuration to enable remote access.
>>
>> > So, I open SQL Client Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned stored
>> > procedures does not exist.
>> The procedures are in the msdb database, so you need to be there to run them, or qualifying the
>> proc
>> name.
>> But Express doesn't come with Agent, so the steps in this KB doesn't make sense to me for 2005
>> Express.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
>> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
>> > How do you schedule to backup database in SQL Server 2005 Express? I tried
>> > following the steps mentioned at http://support.microsoft.com/kb/q241397, but
>> > there are errors .
>> >
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it might be
>> > that my server doesn't allow remote access. So, I open SQL Client Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned stored
>> > procedures does not exist.
>> >
>> > Please help
>>|||Here are some links you might find useful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp
Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
User Instances
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/emsqlexcustapp.asp
Embedding Express in Apps
--
Andrew J. Kelly SQL MVP
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Cool, Jasper!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uBvSyh6YGHA.1888@.TK2MSFTNGP02.phx.gbl...
> Automating Database maintenance in SQL 2005 Express Edition Part I
> http://www.sqldbatips.com/showarticle.asp?ID=27
> Automating Database maintenance in SQL 2005 Express Edition Part II
> http://www.sqldbatips.com/showarticle.asp?ID=29
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>|||Way cool. If you want to combine this with internal scheduling that doesn't
use the Windows scheduler, you could replace the brain-dead backup command
with your SP in this:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uJaD788YGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Cool, Jasper!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uBvSyh6YGHA.1888@.TK2MSFTNGP02.phx.gbl...
>> Automating Database maintenance in SQL 2005 Express Edition Part I
>> http://www.sqldbatips.com/showarticle.asp?ID=27
>> Automating Database maintenance in SQL 2005 Express Edition Part II
>> http://www.sqldbatips.com/showarticle.asp?ID=29
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com|||Express databases by default use the SIMPLE recovery model so there
shouldn't be a problem with log files filling up unless you change this.
This DOESN'T mean you don't have to back up SQL Express databases - only
that log file backups probably aren't an issue.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:58C5672C-ABC2-4EFA-BB1C-AD5232648F20@.microsoft.com...
> Then is there no way to schedule backup? If that's so the log file will
> very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
> "Tibor Karaszi" wrote:
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it
>> > might be
>> > that my server doesn't allow remote access.
>> Use Surface Area Configuration to enable remote access.
>>
>> > So, I open SQL Client Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned
>> > stored
>> > procedures does not exist.
>> The procedures are in the msdb database, so you need to be there to run
>> them, or qualifying the proc
>> name.
>> But Express doesn't come with Agent, so the steps in this KB doesn't make
>> sense to me for 2005
>> Express.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
>> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
>> > How do you schedule to backup database in SQL Server 2005 Express? I
>> > tried
>> > following the steps mentioned at
>> > http://support.microsoft.com/kb/q241397, but
>> > there are errors .
>> >
>> > Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
>> > myBackupScript.sql -n, I received timeout error, telling me that it
>> > might be
>> > that my server doesn't allow remote access. So, I open SQL Client
>> > Tools, and
>> > run the SQL statements. Then it told me that all of the mentioned
>> > stored
>> > procedures does not exist.
>> >
>> > Please help
>>

How to Schedule Backup for SQL Server 2005 Express

How do you schedule to backup database in SQL Server 2005 Express? I tried
following the steps mentioned at http://support.microsoft.com/kb/q241397, bu
t
there are errors .
Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
myBackupScript.sql -n, I received timeout error, telling me that it might b
e
that my server doesn't allow remote access. So, I open SQL Client Tools, and
run the SQL statements. Then it told me that all of the mentioned stored
procedures does not exist.
Please help> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
be
> that my server doesn't allow remote access.
Use Surface Area Configuration to enable remote access.

> So, I open SQL Client Tools, and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
The procedures are in the msdb database, so you need to be there to run them
, or qualifying the proc
name.
But Express doesn't come with Agent, so the steps in this KB doesn't make se
nse to me for 2005
Express.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
be
> that my server doesn't allow remote access. So, I open SQL Client Tools, a
nd
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Then is there no way to schedule backup? If that's so the log file will very
soon reach its limit and everyone won't be able to use the database, am I
right?
"Tibor Karaszi" wrote:

> Use Surface Area Configuration to enable remote access.
>
> The procedures are in the msdb database, so you need to be there to run th
em, or qualifying the proc
> name.
> But Express doesn't come with Agent, so the steps in this KB doesn't make
sense to me for 2005
> Express.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "wrytat" <wrytat@.discussions.microsoft.com> wrote in message
> news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
>
>|||Hi,
To allow remote access remove the database name from namedpipes in
network configuration.All the stored procedure exists in master
database.
U can schedule them as u schedule it in sql server2000. butu have to
use master.storedprocedure name.
HTH
from
Doller|||>remove the database name from namedpipes in network configuration?
Under NamedPipes, it's either enable it or disable it. What do you mean by
removing the name?
"doller" wrote:

> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
I run sql statements at master, and msdb, but it returns me this message,
"SQLServerAgent is not currently running so it cannot be notified of this
action." But there is no SQLServerAgent for this version.
"doller" wrote:

> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
No! Express doesn't come with Agent.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"doller" <sufianarif@.gmail.com> wrote in message
news:1145431836.282134.66530@.v46g2000cwv.googlegroups.com...
> Hi,
> To allow remote access remove the database name from namedpipes in
> network configuration.All the stored procedure exists in master
> database.
> U can schedule them as u schedule it in sql server2000. butu have to
> use master.storedprocedure name.
> HTH
> from
> Doller
>|||> Then is there no way to schedule backup?
You can use the scheduler that comes with Windows. Together with SQLCMD.EXE,
for instance.

> If that's so the log file will very
> soon reach its limit and everyone won't be able to use the database, am I
> right?
Well, you can set the database to simple recovery. But of course, there are
other more important
reasons to do backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:58C5672C-ABC2-4EFA-BB1C-AD5232648F20@.microsoft.com...[vbcol=seagreen]
> Then is there no way to schedule backup? If that's so the log file will ve
ry
> soon reach its limit and everyone won't be able to use the database, am I
> right?
> "Tibor Karaszi" wrote:
>|||Here are some links you might find useful:
http://msdn.microsoft.com/library/d...r />
rview.asp
Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn.microsoft.com/sql/defau... />
erinst.asp
User Instances
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/a.../05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn.microsoft.com/sql/expre...qlexcustapp.asp
Embedding Express in Apps
Andrew J. Kelly SQL MVP
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help|||Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:F0CD668A-70C8-43DE-B93B-76E709FE8152@.microsoft.com...
> How do you schedule to backup database in SQL Server 2005 Express? I tried
> following the steps mentioned at http://support.microsoft.com/kb/q241397,
> but
> there are errors .
> Firstly at command prompt when I execute OSQL -Usa -PmyPasword -i
> myBackupScript.sql -n, I received timeout error, telling me that it might
> be
> that my server doesn't allow remote access. So, I open SQL Client Tools,
> and
> run the SQL statements. Then it told me that all of the mentioned stored
> procedures does not exist.
> Please help

How to run backup in EM?

I'm running SQL Server 2000 on Win2k Server. I've right clicked on a
database, selected all task and backup database. How do I run this backup
manually to check it?
Thanks,
BrettBrett,
Did you mean to ask you want to check if the backup file is valid? If so,
try a restore.You can do it via EM (all tasks | restore database) or through
the RESTORE DATABASE command in Query analyzer.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Brett" <myaccount@.cygen.com> wrote in message
news:OevbiKBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> I'm running SQL Server 2000 on Win2k Server. I've right clicked on a
> database, selected all task and backup database. How do I run this backup
> manually to check it?
> Thanks,
> Brett
>|||I haven't run the backup yet. There is not file to check.
Thanks,
Brett
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uImszPBaDHA.2336@.TK2MSFTNGP09.phx.gbl...
> Brett,
> Did you mean to ask you want to check if the backup file is valid? If so,
> try a restore.You can do it via EM (all tasks | restore database) or
through
> the RESTORE DATABASE command in Query analyzer.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Brett" <myaccount@.cygen.com> wrote in message
> news:OevbiKBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > I'm running SQL Server 2000 on Win2k Server. I've right clicked on a
> > database, selected all task and backup database. How do I run this
backup
> > manually to check it?
> >
> > Thanks,
> > Brett
> >
> >
>|||Brett,
Ok.So you can backup the database in the way you mentioned in EM.Are you
facing any problems?The equivalent T-SQL command is BACKUP DATABASE.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Brett" <myaccount@.cygen.com> wrote in message
news:%23S$LMUBaDHA.1832@.TK2MSFTNGP09.phx.gbl...
> I haven't run the backup yet. There is not file to check.
> Thanks,
> Brett
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:uImszPBaDHA.2336@.TK2MSFTNGP09.phx.gbl...
> > Brett,
> >
> > Did you mean to ask you want to check if the backup file is valid? If
so,
> > try a restore.You can do it via EM (all tasks | restore database) or
> through
> > the RESTORE DATABASE command in Query analyzer.
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Brett" <myaccount@.cygen.com> wrote in message
> > news:OevbiKBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > > I'm running SQL Server 2000 on Win2k Server. I've right clicked on a
> > > database, selected all task and backup database. How do I run this
> backup
> > > manually to check it?
> > >
> > > Thanks,
> > > Brett
> > >
> > >
> >
> >
>|||I'm asking how to backup the database. It is scheduled but I'd like to back
up before the scheduled date.
Brett
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23QkNLWBaDHA.2520@.TK2MSFTNGP09.phx.gbl...
> Brett,
> Ok.So you can backup the database in the way you mentioned in EM.Are you
> facing any problems?The equivalent T-SQL command is BACKUP DATABASE.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Brett" <myaccount@.cygen.com> wrote in message
> news:%23S$LMUBaDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > I haven't run the backup yet. There is not file to check.
> >
> > Thanks,
> > Brett
> >
> > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > news:uImszPBaDHA.2336@.TK2MSFTNGP09.phx.gbl...
> > > Brett,
> > >
> > > Did you mean to ask you want to check if the backup file is valid? If
> so,
> > > try a restore.You can do it via EM (all tasks | restore database) or
> > through
> > > the RESTORE DATABASE command in Query analyzer.
> > >
> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com
> > >
> > > "Brett" <myaccount@.cygen.com> wrote in message
> > > news:OevbiKBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > > > I'm running SQL Server 2000 on Win2k Server. I've right clicked on
a
> > > > database, selected all task and backup database. How do I run this
> > backup
> > > > manually to check it?
> > > >
> > > > Thanks,
> > > > Brett
> > > >
> > > >
> > >
> > >
> >
> >
>|||Brett,
Try this syntax in Query analyzer..
BACKUP DATABASE <databasename>
TO DISK='c:\data\database.BAK'
Since you already have the sql job scheduled, just right click and start the
job to run it manually.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Brett" <myaccount@.cygen.com> wrote in message
news:u2ms6uBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> I'm asking how to backup the database. It is scheduled but I'd like to
back
> up before the scheduled date.
> Brett
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:%23QkNLWBaDHA.2520@.TK2MSFTNGP09.phx.gbl...
> > Brett,
> >
> > Ok.So you can backup the database in the way you mentioned in EM.Are you
> > facing any problems?The equivalent T-SQL command is BACKUP DATABASE.
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Brett" <myaccount@.cygen.com> wrote in message
> > news:%23S$LMUBaDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > I haven't run the backup yet. There is not file to check.
> > >
> > > Thanks,
> > > Brett
> > >
> > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > news:uImszPBaDHA.2336@.TK2MSFTNGP09.phx.gbl...
> > > > Brett,
> > > >
> > > > Did you mean to ask you want to check if the backup file is valid?
If
> > so,
> > > > try a restore.You can do it via EM (all tasks | restore database) or
> > > through
> > > > the RESTORE DATABASE command in Query analyzer.
> > > >
> > > > --
> > > > Dinesh.
> > > > SQL Server FAQ at
> > > > http://www.tkdinesh.com
> > > >
> > > > "Brett" <myaccount@.cygen.com> wrote in message
> > > > news:OevbiKBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > > > > I'm running SQL Server 2000 on Win2k Server. I've right clicked
on
> a
> > > > > database, selected all task and backup database. How do I run this
> > > backup
> > > > > manually to check it?
> > > > >
> > > > > Thanks,
> > > > > Brett
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I see the job listed. When I right click and start job, nothing happens.
Any suggestions?
Brett
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:u7%23XK1BaDHA.1640@.TK2MSFTNGP10.phx.gbl...
> Brett,
> Try this syntax in Query analyzer..
> BACKUP DATABASE <databasename>
> TO DISK='c:\data\database.BAK'
> Since you already have the sql job scheduled, just right click and start
the
> job to run it manually.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Brett" <myaccount@.cygen.com> wrote in message
> news:u2ms6uBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > I'm asking how to backup the database. It is scheduled but I'd like to
> back
> > up before the scheduled date.
> >
> > Brett
> > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > news:%23QkNLWBaDHA.2520@.TK2MSFTNGP09.phx.gbl...
> > > Brett,
> > >
> > > Ok.So you can backup the database in the way you mentioned in EM.Are
you
> > > facing any problems?The equivalent T-SQL command is BACKUP DATABASE.
> > >
> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com
> > >
> > > "Brett" <myaccount@.cygen.com> wrote in message
> > > news:%23S$LMUBaDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > > I haven't run the backup yet. There is not file to check.
> > > >
> > > > Thanks,
> > > > Brett
> > > >
> > > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > > news:uImszPBaDHA.2336@.TK2MSFTNGP09.phx.gbl...
> > > > > Brett,
> > > > >
> > > > > Did you mean to ask you want to check if the backup file is valid?
> If
> > > so,
> > > > > try a restore.You can do it via EM (all tasks | restore database)
or
> > > > through
> > > > > the RESTORE DATABASE command in Query analyzer.
> > > > >
> > > > > --
> > > > > Dinesh.
> > > > > SQL Server FAQ at
> > > > > http://www.tkdinesh.com
> > > > >
> > > > > "Brett" <myaccount@.cygen.com> wrote in message
> > > > > news:OevbiKBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > > > > > I'm running SQL Server 2000 on Win2k Server. I've right clicked
> on
> > a
> > > > > > database, selected all task and backup database. How do I run
this
> > > > backup
> > > > > > manually to check it?
> > > > > >
> > > > > > Thanks,
> > > > > > Brett
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Brett,
Well, "nothing happens", does not mean anything - success or failure.The job
wont give you a dialog box mentioning the status upon completion.You can
look into the code inside the sql job, see where the backup is being made
and check the filesystem whether that file was created.Right click on the
job | refresh and then check the job history whether it ran
successfully.Also, did you try the syntax that I mentioned?
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Brett" <myaccount@.cygen.com> wrote in message
news:O7RtggCaDHA.656@.tk2msftngp13.phx.gbl...
> I see the job listed. When I right click and start job, nothing happens.
> Any suggestions?
> Brett
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:u7%23XK1BaDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > Brett,
> >
> > Try this syntax in Query analyzer..
> >
> > BACKUP DATABASE <databasename>
> > TO DISK='c:\data\database.BAK'
> >
> > Since you already have the sql job scheduled, just right click and start
> the
> > job to run it manually.
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Brett" <myaccount@.cygen.com> wrote in message
> > news:u2ms6uBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > > I'm asking how to backup the database. It is scheduled but I'd like
to
> > back
> > > up before the scheduled date.
> > >
> > > Brett
> > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > news:%23QkNLWBaDHA.2520@.TK2MSFTNGP09.phx.gbl...
> > > > Brett,
> > > >
> > > > Ok.So you can backup the database in the way you mentioned in EM.Are
> you
> > > > facing any problems?The equivalent T-SQL command is BACKUP DATABASE.
> > > >
> > > > --
> > > > Dinesh.
> > > > SQL Server FAQ at
> > > > http://www.tkdinesh.com
> > > >
> > > > "Brett" <myaccount@.cygen.com> wrote in message
> > > > news:%23S$LMUBaDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > > > I haven't run the backup yet. There is not file to check.
> > > > >
> > > > > Thanks,
> > > > > Brett
> > > > >
> > > > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > > > news:uImszPBaDHA.2336@.TK2MSFTNGP09.phx.gbl...
> > > > > > Brett,
> > > > > >
> > > > > > Did you mean to ask you want to check if the backup file is
valid?
> > If
> > > > so,
> > > > > > try a restore.You can do it via EM (all tasks | restore
database)
> or
> > > > > through
> > > > > > the RESTORE DATABASE command in Query analyzer.
> > > > > >
> > > > > > --
> > > > > > Dinesh.
> > > > > > SQL Server FAQ at
> > > > > > http://www.tkdinesh.com
> > > > > >
> > > > > > "Brett" <myaccount@.cygen.com> wrote in message
> > > > > > news:OevbiKBaDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > > > > > > I'm running SQL Server 2000 on Win2k Server. I've right
clicked
> > on
> > > a
> > > > > > > database, selected all task and backup database. How do I run
> this
> > > > > backup
> > > > > > > manually to check it?
> > > > > > >
> > > > > > > Thanks,
> > > > > > > Brett
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

How to restore x.bak.dap file

Hi,

I have a backup file say x.bak.dap

I want to restore this file which has the .dap extension

How to do it?

Please suggest me

Thanks in advance,

Senthil N

If it's a SQL Server backup, you restore the same as any other backup file. SQL Server doesn't really care what the extension is, the file just needs to be a SQL Server backup file. It's a pretty standard practice to use bak for the database backup file extension though.

If you aren't sure about the file itself, try to just view the files in the backup file with:

restore filelistonly

from disk = 'X:\PathToFile\x.bak.dap'

-Sue

|||

Hi,

Now I have the bakup file and I tried to restore 'Test' database from the Query Analyzer (master db).

I am getting the following error

Msg 3154, Level 16, State 4, Line 1

The backup set holds a backup of a database other than the existing 'Test' database.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Please help me.

Thanks in advance,

Senthil N.

|||

Hi Sue,

Thank you for ur reply.

I tried the command and i am getting the logical & Physical file name.

But still i am getting error.

see my reply.

|||

It may be that your backup file contains more than one set of backups:

Try running:

restore headeronly

from disk = 'X:\PathToFile\x.bak.dap'

And if this is the case, you'll need to look at WITH FILE option in BOL for your restore.

Hope this helps!

|||

I haven't actually been able to replicate your error but there does seem to be a fair few people who have had this issue. It looks like adding the WITH REPLACE clause to the RESTORE statement which overwrites the existing database does the trick.

|||

You probably want to read up on the with move and replace options for restoring a database. To include the move and replace options in your restore statement, it would be something like:

Restore database YourDBName

From disk = 'X:\PathToFile\x.bak.dap'

with move 'LogicalDataFileName' to 'X:\NewLocation\YourDBName.mdf',

move 'LogicalLogFileName' to 'X:\NewLocation\YourDBName.ldf',

replace

-Sue

|||

Thanks Sue,

It's working after adding the replace option.

Regards,

Senthil.N

|||

Thank you Rich,

My problem sorted out after adding the restore options.

Regards,

Senthil.N

How to restore x.bak.dap file

Hi,

I have a backup file say x.bak.dap

I want to restore this file which has the .dap extension

How to do it?

Please suggest me

Thanks in advance,

Senthil N

If it's a SQL Server backup, you restore the same as any other backup file. SQL Server doesn't really care what the extension is, the file just needs to be a SQL Server backup file. It's a pretty standard practice to use bak for the database backup file extension though.

If you aren't sure about the file itself, try to just view the files in the backup file with:

restore filelistonly

from disk = 'X:\PathToFile\x.bak.dap'

-Sue

|||

Hi,

Now I have the bakup file and I tried to restore 'Test' database from the Query Analyzer (master db).

I am getting the following error

Msg 3154, Level 16, State 4, Line 1

The backup set holds a backup of a database other than the existing 'Test' database.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Please help me.

Thanks in advance,

Senthil N.

|||

Hi Sue,

Thank you for ur reply.

I tried the command and i am getting the logical & Physical file name.

But still i am getting error.

see my reply.

|||

It may be that your backup file contains more than one set of backups:

Try running:

restore headeronly

from disk = 'X:\PathToFile\x.bak.dap'

And if this is the case, you'll need to look at WITH FILE option in BOL for your restore.

Hope this helps!

|||

I haven't actually been able to replicate your error but there does seem to be a fair few people who have had this issue. It looks like adding the WITH REPLACE clause to the RESTORE statement which overwrites the existing database does the trick.

|||

You probably want to read up on the with move and replace options for restoring a database. To include the move and replace options in your restore statement, it would be something like:

Restore database YourDBName

From disk = 'X:\PathToFile\x.bak.dap'

with move 'LogicalDataFileName' to 'X:\NewLocation\YourDBName.mdf',

move 'LogicalLogFileName' to 'X:\NewLocation\YourDBName.ldf',

replace

-Sue

|||

Thanks Sue,

It's working after adding the replace option.

Regards,

Senthil.N

|||

Thank you Rich,

My problem sorted out after adding the restore options.

Regards,

Senthil.N

How to Restore the msdb DatBase

Hi,

i have reinstalled the SQL Server before it i have taken backup of both master and
msdb database.Because there were many scduled jobs.
After Installation i want recreate those scduled job.
That i think i can get by restoring msdb.
But it is not restoring how can i restore it.

Or there is any way to get the all scduled job list so that i can recreate it manully.

Regards
Sanjay

Make sure the SQL Server Agent is stopped which uses the msdb database, then the restore should work fine. As a best practise I always get the scripts of the scheduled jobs in addition to the backup of the msdb database. This allows me to execute single script for job creation.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||

Hi jens,

i am tryning restore after stopping SQL Server Agent is stopped.

But it is giving the error massage that

"The backup of the system database on device E:\EcoSystemDatabasebackup\msdb_2006070200.BAK can not be restored because it was created by a different version of the server (134218488) than this server (134219767) .RESTORE DATABASE is terminating abnormally.

How can i restore it .while i am able to restore it in another server by different name.

Soisthere is any way to collect information of secduled job form there and create it in re quired server .

it is possible or not ?

if possible how can i do it.

Regards

Sanjay

|||

This isn't replication related, so moving to Engine forum.

By the way, what version of sql server did you take the backup from, and what version are you restoring it to (do select @.@.version)? As the error message says, if you're trying to restore to an earlier version of sql server, it won't work.

|||

Sorry, to come back that late. As greg already said, you have a problem concerning your different versions of your database (might have a service pack or hotfix installed meanwhile ?).

This is what a kn article is telling about the problem: http://support.microsoft.com/kb/264474/EN-US/

Another option would be to transfer the data via DTS, although I never tried that I would make sure to have a backup of the current (running msdb) first.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi guys,

As i saw your goal it is to recreate the same jobs on another MS Sql Server.

This can be done very simple without using the backup/restore mechanism. Just start Enterprise Manager from the server where you have all the jobs.

Go in the tree in Management/SQL Server Agent/Jobs and from the context menu choose generate SQL Script. You will get a nice file with all your jobs.

This script file you will need to run it on the new SQL Server to create all the jobs there.

I hope will help.

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgrade
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com

How to restore Sql Server database?

Hi，
I have a backup file in the database server,and I want to restore database using the backup in client.What should I do?

Thanks for any word.

Search Books Online for "RESTORE DATABASE" You can do the restore by sending a SQL Command in ADO.NET.
RESTORE DATABASE {database_name |@.database_name_var}
[ FROM < backup_device > [,...n] ]
[ WITH
[ RESTRICTED_USER ]
[ [,] FILE= {file_number |@.file_number} ]
[ [,] PASSWORD ={password|@.password_variable} ]
[ [,] MEDIANAME ={media_name|@.media_name_variable} ]
[ [,] MEDIAPASSWORD ={mediapassword|@.mediapassword_variable} ]
[ [,] MOVE'logical_file_name' TO'operating_system_file_name']
[,...n]
[ [,] KEEP_REPLICATION ]
[ [,] { NORECOVERY | RECOVERY | STANDBY=undo_file_name} ]
[ [,] { NOREWIND | REWIND } ]
[ [,] { NOUNLOAD | UNLOAD } ]
[ [,] REPLACE ]
[ [,] RESTART ]
[ [,] STATS [=percentage] ]
]

how to restore sql server 2000 db backup into sql server 2005 express edition

Hi Friends,

I have installed SQL server 2005 Express Edition and SQL Server management

I have a SQL server 2000 db backup file. I try create a new database in my SQL server 2005 express Edition and try restore that backup file from device, it only searching for file with *.bak and *.tm extension! I tried generate backup file with .bak extension and tried restore into SQl server 2005 express edition but still it is not allowing to do so! I also tried copy my database's data file and log file and paste it under SQL server 2005 express edition Data folder and still not able to read the tables.

Is that any ways to do restoring for this SQL server 2000 backup file into SQL server 2005 express edition! Anybody can help me on this please...:eek:Can you please be a little more specific, with maybe some error codes, etc? I don't have any express edition experience, but if you give us some error codes or the scripts you use for the restore, we might be able to help you.|||Hi Friend, Thanks for your help!

Well, I guess i found the way. I believe it is because the sql 2005 doesn't support the sql 2000 backup file, Im not sure! So i tried attached the database from sql server 2000 into sql server 2005 then it can already.After that i hv tried take backup from sql server 2005 itself and then restore it back ,it is can be restored successfully! Thank God finally i got a way to do it ...phew

How to restore SQL 7 DB to SQL 2005

Hi, All
I am trying to restore SQL 2000 DB into SQL 2005 Database.
I backup the database from SQL 2000.

From Management Suit, I try to restore database but I can not see network drive from there even though SQL serveris running under network account.
I could see network drive from SQL 2000 or at least I can type path to find backup file. However I can not do this..

My Q is:
1. How can I restore this SQL 2000 db to SQL 2005 using network path?
2. Since backup is SQL 2000 database, when I restore into SQL 2005, does restore upgrade system tables and other schema to SQL 2005 as well?
If not, what is the best practice to upgrade this database into SQL 2005?
Upgrading SQL 2000 current server to SQL 2005 is not an option at this point.
Eventually porduction server will be scrup and install SQL 2005 and then restore DBs into production machine...

Thx in advance
Jay
1. We support restoring from network paths. You need to ensure the sql service has access to the path. The GUI would also need access to the network path. You might try using an explicit UNC rather than drive letters.

2. We automatically upgrade the system tables as part of the restore (or attach).|||

Steve Schmidt

Thanks for the post here. But can you lay it down in plain english. What I mean is I need a step by step. What I have done is I have the backup for my SQL Server 2000 databases. I have created the tables in SQL Server 2005. Then I go to restore the files using the Restore Database in SQL Server 2005. I select the newly created database in the To Database on the general tab. Select From File on the general tab and browse for the backup file which I find. Check the restore check box then hit OK.

I get the following error.

// Start Error Message //

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'VM-WIN03SERVER'. (Microsoft.SqlServer.Smo)

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

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'shoplist' database. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
// End Error Message //

If I do an import it works just fine but you loss all your key info and other important elements just like when you do a import from SQL Server 2000 to SQL Server 2000.

|||

Your procedure is correct.

There is an "overwrite existing database" option on the "options" page of the restore dialog. You need to check it to avoid the error.

The error indicates that the backup set was not created by backing up the current database you plan to overwrite. So if you are sure that you have the correct backup set, check that box and the restore will procede.

Hope that helps.

|||

Hi,

I was having a similar problem. I am trying to restore a .bak file from another sql server 2000. We are looking at moving all the DB's to the new server running sql 2005.

I have tried a restore using the managment studion via right click on the databases and also tried making a new data base that I may restore into like the example above. I both cases I get an error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'SQLSERVPRO2'. (Microsoft.SqlServer.Smo)

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

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\Robert_Taylor_Sys.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

What the problem was here was the paths where the origional data files are not same in sql 2005 x64. Under the options section there is a place to redirect the files to restore.

|||

I am getting the same error msg. We did backup in SQL 2005 itself and wanted to try the restore operation but throws the same error. I did choose "force overwrite existing db" but still the same.

Any idea?

|||Anyone had solution to this? I'm experiencing the same error.|||

This error:

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\Robert_Taylor_Sys.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

indicates that restore can not find the path:

C:\Program Files\Microsoft SQL Server\MSSQL\Data

You either need to create the path yourself, or:

1. use "WITH MOVE" in the T-SQL

or

2. use the GUI to specify an alternate location for each database file

|||

To restore a SQL 2000 database on SQL 2005

create a new database in 2005 with the same name and compatibility set for SQL 2000 or the version you want to restore from.

restore this database with options 'overwrite existing database'

i did this from one server to another, but it is also possible from a .bak file ofcourse.

gtx. YAK

|||I have done this with about 30+ DB. The fastest way and most reliable was to back up the DB from the SQL 2000 machine on to the SQL 2005 machine via EM. Then just login to the sql 2005 machine and use the restore database from SSMS. This way you can insure all of your tables will stay exactly the way that you had them ie identities. I have tried many different ways and this is by far the most straight forward. Of course you could script the backup and the restore if you are going to be doing this often or have more than one DB to convert/move.|||

I am trying to restore a database from SQL7 to SQL2005 but get a message:

System.data.sqlclient.sqlerror: The backup set holds a backup of a database other than the existing database.

I have tried the restore and the inport option for the sql2005 database.

****Update

I used the overwrite option with a complete backup file. The procesws seamed to work but now I have a (6.5 Compatible) next to the dadabase name and there is nothing (tables, views, security, etc..) associated with the database. Now I can not delete or rename the database. Those options are not supported: 6.5 database compatibility level is not supported.

Any ideas?

|||

Okay since this forum has failed to answer the question, here's the process in SSMS.

1) Go to restore database

2) Select the database that you want to back up to

3) Locate the backup file on disk. You may have to put it into the MSSQL Server -> MSSQL.1-> MSSQL -> Backup Folder. It must be a .bak file.

4) Select the back that want to restore from the available backups.

5) Go to the top left "options" property and when you do that select "overrite existing database".

6) Now make sure that the path to the files on database to be restored are correct in this same dialog view. Look at the paths to the database file and the log file and make damn sure that they are the correct ones for the database to be restored. The problem here is that those paths are going to be for the filesystem that the backup came from, not the one you are goning to put the restore onto. That's the big problem here.

|||

it working with my good

just go options then chang the path for .mdf and ldf file

like : C:\Program Files\Microsoft SQL Server\MSSQL\Data\newname.mdf

C:\Program Files\Microsoft SQL Server\MSSQL\Data\newname.ldf

choose new name for the database

clear all check box overwrite options

|||

Guys,

I have experienced challenges that only Microsoft can supply,

When all looks fine and it still doesn't work then it is time to copy and paste. I experienced similar problems and found when I copied the file path from windows explorer directly into the "options" -> "restore database files" ->"Restore As", it worked. I did not know that it was case sensitive, if not my DB was just tempremental.

If you follow the previous steps and copy and paste then it will hopefully work.

|||

Hey "Reformed ", yes it worked the way you told us. :) Thanks it was a great help!

The only thing that was not correct was the path ... which you highlighted.

Thanks again!