Backup SQL using MSDE Writer

How do I backup my SQL Databases using MSDE Writers?

I do not see MSDE Writers under NT Backup.

Thanks

Santhosh

You also don't see any of the other writers. They generally aren't directly used by a backup user.

The backup app may use them behind the scenes. The important thing is whether the backup app correctly backs up your SQL database. If you back up a volume containing a SQL database, the MSDE writer should be invoked behind the scenes automatically as part of the VSS snapshot done by NTBackup.

Backup SQL Database

The menu item "Backup SQL Database" does not appear as a
choice under Database Utilities when running an Access
2003 Project connected to an MSDE 2000 database on the
local machine. Why?
hi,
JBMoore wrote:
> The menu item "Backup SQL Database" does not appear as a
> choice under Database Utilities when running an Access
> 2003 Project connected to an MSDE 2000 database on the
> local machine. Why?
I've no particulary skill with Access, but the menu item is available to me
even when connected to a particular database with "public" role membership
only, as I thought that under circumstances of being member of
db_backupoperator database role at least was the reason of the menu item not
being shown... but is not the case.. ok the backup failed for insufficient
privileges :D:D
perhaps you've better post the question on Office NG...
sorry
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

backup restored to an MSDE

Does Microsoft officially support restoring a standard edition DB to the MSDE?
Thanks, and a link would be great....
burt_king@.yahoo.com
Sure, this works.
|||I know it works; is it officially supported?
burt_king@.yahoo.com
"Jens" wrote:

> Sure, this works.
>
|||Sure, thats a normal upgrade / upscaling path.
HTH, jens Suessmeyer.
|||I can't find any Microsoft resource --Books on line or other-- that agrees
with your statement that it's supported. Can you point me to a resource?
burt_king@.yahoo.com
"Jens" wrote:

> Sure, thats a normal upgrade / upscaling path.
> HTH, jens Suessmeyer.
>

backup restored to an MSDE

Does Microsoft officially support restoring a standard edition DB to the MSD
E?
Thanks, and a link would be great....
burt_king@.yahoo.comSure, this works.|||I know it works; is it officially supported?
--
burt_king@.yahoo.com
"Jens" wrote:

> Sure, this works.
>|||Sure, thats a normal upgrade / upscaling path.
HTH, jens Suessmeyer.|||I can't find any Microsoft resource --Books on line or other-- that agrees
with your statement that it's supported. Can you point me to a resource?
burt_king@.yahoo.com
"Jens" wrote:

> Sure, thats a normal upgrade / upscaling path.
> HTH, jens Suessmeyer.
>sql

backup restored to an MSDE

Does Microsoft officially support restoring a standard edition DB to the MSDE?
Thanks, and a link would be great....
--
burt_king@.yahoo.comSure, this works.|||I know it works; is it officially supported?
--
burt_king@.yahoo.com
"Jens" wrote:
> Sure, this works.
>|||Sure, thats a normal upgrade / upscaling path.
HTH, jens Suessmeyer.|||I can't find any Microsoft resource --Books on line or other-- that agrees
with your statement that it's supported. Can you point me to a resource?
burt_king@.yahoo.com
"Jens" wrote:
> Sure, thats a normal upgrade / upscaling path.
> HTH, jens Suessmeyer.
>

Backup or copy database

Is it really so hard to backup.
First let me say: I dont have any expensive programs lide VS, only the MSDE desktop engine and Web Matrix and a Community Starter Kit v1.0 VBSDK.
But i have the free SQLExec version 1 from laplas-soft.com. It looks like you are in full control with this program, but when according to help file i try to backup i get: "Device error or device off-line. See the SQL Server error log for more details". Of course i start up by connecting to the database.

Any ideas?You need to create a backup device first. From books online:

<quote>
A. Back up the entire MyNwind database

Note The MyNwind database is shown for illustration only.

This example creates a logical backup device in which a full backup of the MyNwind database is placed.

-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1

</quote>|||Sorry, but i'm new into .net and i dont know how to make such a device without more details.
I can see that it is not a .bat file i have to make, though.|||You would need to run this script through osql. ALternately, the data only could be backed up using bcp, a command line tool that can be placed in a batch file. Find BCP.EXE on your machine, run BCP /? for details on what it can do.|||I would much prefer to backup the whole database instead of data only. Because i want to copy the backup'ed database too. Can i find this osql?
And thanks..

I mean, i would like to just work on the remote databaseses. So if i can backup/copy it remotely, that may be best.|||Can't he just copy the *.MDF and *.LDF files his site is using?

Regards

Wayne Phipps|||OSQL is a program that is installed whenever MSDE or SQL Server is installed. You should search Deja.com or similar for all the details of using it, or maybe even usethis link to download the SQL Server Books Online.|||Well, yes, but that implies stopping and restarting the database, which I generally presume folks do not want to do. In addition, he would want to explicitly detatch the database, to reliably be able to reattach.|||Thanks for the help so far. I will look into it, and hope you will return. Its a bit late evening here in Denmark, so goodnight.|||Well, it seems from my experience with this, that it could take me a week to learn just to make a simple backup. I know it is not as the easy days with an Acces database, but maybe i have to try the way Wayne Phipps suggested. I dont mind shutting down the site(s) an early morning for a few minutes.

On the other side. It seems a simple thing with OSQL. Found this on deva (or google.com):
"
You can use the OSQL command prompt utility. For example:

OSQL -S MyServer -E -Q "BACKUP DATABASE MyDatabase TO
DISK='\\MyLaptop\BackupShare\MyDatabase.bak' WITH INIT, STATS=5"

Note that the SQL Server service account will need permissions to the
share. Alternatively, you can backup to a local file and then copy the
backup file to the remote machine.

See the Books Online for details.
"
Where can i see the books online? The download link doesnt work, nothing happens.

Can someone give me directions to make it work for an online sql database?
It looks like some autentification is missing in the above?|||Is it really so hard to backup.
Well, it seems from my experience with this, that it could take me a week to learn just to make a simple backup.

A backup of a database (SQL) shouldn't be as hard as that. You have not got correct approach yet, otherwise, you will feel the backup is just as easy as 1, 2, 3.

First, let me clarify your situation:
1. You wanted a (SQL) database backup. Thus, this issue is not related to something likeVS, ..., Web Matrix or a Community Starter Kit v1.0 VBSDK.

2. You have a MSDE desktop engine. As my knowledge, it doesn't have a Enterprise Manager as SQL7/2000 do. However, you do have a SQLExec that is somewhat similar to SQL2000 Enterprise Manager.

I am not fimiliar with MSDE and SQLExec, I will give you possible solutions for SQL Server 7/2000. These solutions will probably be suitable for your case. Otherwise, we start further discussion from there.

Method 1: SQL server backup using Enterprise Manager
1. Open SQL Enterprise Manger, right click the database you wanted to backup, say, Northwind, --> All tasks --> Back Database ... --> lefe click to open the window "SQL Server Backup - Northwind".

2. To add the destination of your backup, click the "Add" button to open "Select Backup Destination" sub-window: there will be a default directory in the "File name" box. For SQL2000, it would be "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\"

YouNEED to specify the file name:
a. you could look up a file from your disk by clicking the browse button "..." at the right of the box;
b. if you made the backup first time and you didn't have a backup file in your disk anywhere, youNEED just simply to add a fine name into the box, say Northwind.bak (for Northwind database). Now, the "File Name" box would have the file name as

"C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak"

3. Click "OK" button in "Select Backup Destination" window to close it. Then click "OK" button in your "SQL Server Backup - Northwind" window, you have done your backup SUCCEESSFULLY.

For your case, if you used SQLExec from laplas-soft.com, it would be only one window like this:"Database Backup" window of SQLExec, where the destination box includes directory as

"D:\DBB\SQL7\mcp_23\"

If you clicked the "Apply" button now, I guess, you would probably got the error "Device error or device off-line. See the SQL Server error log for more details" that is exactly the error you could get in SQL Enterprise Manage without specifying the destination file name but only directory.

Type a fine name under the directory or another full path file name into the desitination box, click "Apply" button, you have 95% chance to have your job done, I assume laplas-soft.com made its SQLExec's behavior as same or close as SQL Server. If this was true, you probably would need five minutes to read this suggestion and another five minutes to do your test. 10 minutes, instead of one week, you have your job done. ;)

More, in the next post.|||Thanks very much for taking time with this ghan from NYC. I am greatful to get all the help here at the forums.

Now i got a backup!!!
Everything seemes to have its tricks to fix.

Now what to do with it?
I would like to restore that backup. Restore could may be putting it on the remote server instead of the current. Some day it may be neccecery.
And if possible make new databases from this .bak file.
I loooked twice in the SQLExec for a restore function, but found none.

Waiting for input_|||Oh, it seems you got your solution within less than 10 minutes. :)

For completeness, I will first finish my previous discussion about the backup topic in the next post, then go to your restore issue. Be a little bit patient, you will get your solution for your new question in another 10 minutes.|||Continue fromMethod 1:

So, the point is you need to specify your backup device, or the backup file (wiht full path name) in your case.

Back to SQL Server 6.5, there was a "Backup Devices" Directory (also a "Database Devices" directory), at the same level of "Databases" directory in Enterprise Manager, then user would explicitly to make a backup device over there.

From SQL 7, these two directories ("Backup Devices" and "Database Devices") have been gone. When you make a new database, you do not need to create a "Database Device" first, SQL Server make it for you automatically (as a yourDatabaseName.mdf file for your database and a yourDatabaseName.ldf file for your database log file). However, when you backup a database, you have to make your backup device manually in some extent.

Besides the way, I mentioned in the previous post, you could create a backup device as follows:
1. Manually created a empty backup file anywhere in your disk, say, under
"C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\"

create a new empty "test.bak" file (in fact, it could be any name and suffix). Then, you could look up the device file from your backup process.

2. In the "Select Backup Destination" window, discussed in the previous post, there is another choice "Backup device", from there, you could choose <New Backup Device> and create a new device. But, it is nothing more than the way I have introduced in the previous post.

3. You could use Transac-SQL to do it, as Douglas mentioned by system SP "sp_addumpdevice".

Method 2: SQL Server backup using Detach/Attach methods
As mentioned by Wayne Phipps.

The Detach/Attach methods are available for SQL 2000 in both Enterprise Manager choice and Transac-SQL choice, but only Transac-SQL choice for SQL 7 as

sp_detach_db and sp_attach_db

See details from the thread246218.

For this method, beside it could be used as an alternative to backup/restore, it is mostly used as an alternative to import/export functions in SQL EM.

Method 3: SQL Server backup using Transac-SQL code

You could get info from SQL online, Backup. Although one could do the job in that way, but, as a programmer, I have never done it by myself.|||Para7,

Actually, you have already had your solution for restore issue from my previous post. Especially, from the thread246218 for details.

Basically, that uses the Detach/Attach methods. But, you could use backup/restore to do the trick also, see my nest post in another 10 minutes.

Backup options with MSDE

Does MSDE support the full, complete set of backup options that SQL Server
does? If not, what options does MSDE support and are they available via the
Enterprise Manager interface?
Thanks in advance.
hi,
collectivedc wrote:
> Does MSDE support the full, complete set of backup options that SQL
> Server does? If not, what options does MSDE support and are they
> available via the Enterprise Manager interface?
> Thanks in advance.
MSDE supports all the kind of backup expesed by Enterprise Manager...
only a caveat... usually, you are not allowed to use EM with MSDE..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backup options in maintenance plan

I am trying to create a maintenance plan to backup my SQL Desktop Engine
(MSDE 2000) databases using enterprise manager. Everything goes fine in the
maintenance wizard until I get to the "Specify Backup Disk Directory"
screen. On this screen, the drop down box next to "Remove files older
than:" is blank, where it would normally say days, weeks, etc. If I finish
making the plan, when I go back in the path is gone as well as the schedule.
This is only happening on our MSDE SP3 installations. We also have SQL
Server 2000 SP3 and MSDE 7 installed in our organization on different
servers. They do not have this problem. I found someone else with this
problem on the internet at http://www.webservertalk.com/t1108273.html but the
solution describes how to execute an existing maintenance plan. I want to
know why I can't create one using EM.
OK, I found the solution for this (at least the workaround until Microsoft
fixes the problem). You need to add a registry entry to the server running
MSDE. Details can be found at:
http://www.webservertalk.com/t739692.html
"Josh Rolfe" <someone@.microsoft.com> wrote in message
news:O2xOX$JNFHA.1096@.tk2msftngp13.phx.gbl...
>I am trying to create a maintenance plan to backup my SQL Desktop Engine
>(MSDE 2000) databases using enterprise manager. Everything goes fine in
>the maintenance wizard until I get to the "Specify Backup Disk Directory"
>screen. On this screen, the drop down box next to "Remove files older
>than:" is blank, where it would normally say days, weeks, etc. If I finish
>making the plan, when I go back in the path is gone as well as the
>schedule. This is only happening on our MSDE SP3 installations. We also
>have SQL Server 2000 SP3 and MSDE 7 installed in our organization on
>different servers. They do not have this problem. I found someone else
>with this problem on the internet at http://www.webservertalk.com/t1108273.html
>but the solution describes how to execute an existing maintenance plan. I
>want to know why I can't create one using EM.
>

backup on SQL2005EE

I install SQL 2005 Express Edition (upgrade from SQL 7.0 MSDE)
When I execute:
BACKUP DATABASE [Market2000] TO DISK = N'C:\PROGRA~1\MARKET~1\file.bak'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
I received:
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 4504 pages for database 'Market2000', file 'Pusta_Data' on file 1.
100 percent processed.
Processed 1 pages for database 'Market2000', file 'Pusta_Log' on file 1.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'mirror_count'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_password_protected'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'mirror'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'flags'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'unicode_locale'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'unicode_compare_style'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'collation_name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_password_protected'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'recovery_model'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'has_bulk_logged_data'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_snapshot'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_readonly'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_single_user'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'has_backup_checksums'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_damaged'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'begins_log_chain'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'has_incomplete_metadata'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_force_offline'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_copy_only'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'first_recovery_fork_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'last_recovery_fork_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'fork_point_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'database_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'family_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state_desc'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'create_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'drop_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'file_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_only_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_write_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'backup_size'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'filegroup_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_readonly'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_present'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state_desc'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'create_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'drop_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'file_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_only_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_write_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'backup_size'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'filegroup_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_readonly'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_present'.
Msg 3009, Level 16, State 1, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
BACKUP DATABASE successfully processed 4505 pages in 8.069 seconds (4.573
MB/sec).
What is wrong? msdb?Piotr Maciejewski (gbbsoft@.qdnet.pl) writes:
> I install SQL 2005 Express Edition (upgrade from SQL 7.0 MSDE)
> When I execute:
> BACKUP DATABASE [Market2000] TO DISK =
> N'C:\PROGRA~1\MARKET~1\file.bak'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> I received:
>...
> Processed 1 pages for database 'Market2000', file 'Pusta_Log' on file 1.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'mirror_count'.
>...
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> BACKUP DATABASE successfully processed 4505 pages in 8.069 seconds (4.573
> MB/sec).
>
> What is wrong? msdb?
Indeed. It looks as if the definition of msdb is still on SQL7 and misses
new columns added in SQL 2000 and SQL 2005. I have no idea how that could
have happened. A possible workaround could be to install a new fresh
instance of SQL 2005, and then attach the databasees to that instance.
But then you would have to migrate users from the old instance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.se> napisal w wiadomosci
news:Xns97077C2E3945Yazorman@.127.0.0.1...
> Indeed. It looks as if the definition of msdb is still on SQL7 and misses
> new columns added in SQL 2000 and SQL 2005. I have no idea how that could
> have happened. A possible workaround could be to install a new fresh
> instance of SQL 2005, and then attach the databasees to that instance.
> But then you would have to migrate users from the old instance.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
You right.
You idea works, but it is't sollution always.
Let's look at my post on microsoft.public.sqlserver.setup called: "Re: SQL
7.0 MSDE -> SQL 2005 Express".
There is more errors (miising files in setup, don't register SQLDMO, after
register it don't want to work well) when upgrading from SQL 7.0 MSDE to SQL
2005. I think than it hadn't be tested!|||Piotr Maciejewski (gbbsoft@.qdnet.pl) writes:
> You right.
> You idea works, but it is't sollution always.
> Let's look at my post on microsoft.public.sqlserver.setup called: "Re:
> SQL 7.0 MSDE -> SQL 2005 Express". There is more errors (miising files
> in setup, don't register SQLDMO, after register it don't want to work
> well) when upgrading from SQL 7.0 MSDE to SQL 2005. I think than it
> hadn't be tested!
I didn't look at your post in .setup. (Setup issues are always so difficult
to assist with. :-(
But I did get the tip that you may have to run instmsdb.sql to fix MSDB.
I was also told that msdb was not included in MSDE 1.0 by default. This
may be why Setup ignored it. If you do SELECT * FROM sys.databases,
which dbid does msdb have?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Backup multiple database wiht msde in a sql script problem

HI,
I'm new to sql and i would like to run a script to make a backup of msde database, i have modified this script but i'm getting some errors running it. Could any first tell me if my @.command syntaxe is correct if not what would be a way of setting a schedu
led job to run a backup of multiple database in one job.
Here are the errors i'm getting
C:\Documents and Settings\ncsadmin>osql -U sa -i c:\smslab3.sql -n
Password:
Msg 119, Level 15, State 1, Server SMSLAB3, Line 7
Must pass parameter number 5 and subsequent parameters as '@.name = value'.
After the form '@.name = value' has been used, all subsequent parameters must be
passed in the form '@.name = value'.
Msg 119, Level 15, State 1, Server SMSLAB3, Line 7
Must pass parameter number 5 and subsequent parameters as '@.name = value'.
After the form '@.name = value' has been used, all subsequent parameters must be
passed in the form '@.name = value'.
Msg 156, Level 15, State 1, Server SMSLAB3, Line 10
Incorrect syntax near the keyword 'EXEC'.
Thanks
--This Transact-SQL script creates a backup job and calls sp_start_job to run the job.
-- Create job.
-- You may specify an e-mail address, commented below, and/or pager, etc.
-- For more details about this option or others, see SQL Server Books Online.
USE msdb
EXEC sp_add_job @.job_name = 'MSDEBACKUPJOB',
@.enabled = 1,
@.description = 'MSDEBACKUPJOB',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'raould.traore@.mcgill.ca'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Data',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE EDMSO TO DISK = ''c:\MSDESQLBACKUP\EDMSO.dat_bak''','BACKUP DATABASE master TO DISK = ''c:\MSDESQLBACKUP\master.dat_bak''','BACKUP DATABASE model TO DISK = ''c:\MSDESQLBACKUP\model.dat_bak''','BACKUP DATABASE msdb TO DISK = ''c
:\MSDESQLBACKUP\msdb.dat_bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Log',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG EDMSO TO DISK = ''c:\MSDESQLBACKUP\EDMSO.log_bak''','BACKUP LOG master TO DISK = ''c:\MSDESQLBACKUP\master.log_bak''','BACKUP LOG model TO DISK = ''c:\MSDESQLBACKUP\model.log_bak''','BACKUP LOG msdb TO DISK = ''c:\MSDESQLBACKUP\msdb
.log_bak''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'MSDEBACKUPJOB', @.server_name = N'(local)'
-- Run job. Starts the job immediately.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'MSDEBACKUPJOB',
@.name = 'ScheduledBackup_msdb',
@.freq_type = 4, --daily
@.freq_interval = 1, --once
@.active_start_time = '153000' --(10:30 am) 24hr HHMMSS.
Ralph,
I have modified your script slightly (below) and it should now work. There
was a problem with the number of single quotes which meant that the command
wasn't enclosed in a string. This applied to both commands.
Regards,
Paul Ibison
EXEC sp_add_job @.job_name = 'MSDEBACKUPJOB',
@.enabled = 1,
@.description = 'MSDEBACKUPJOB',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'raould.traore@.mcgill.ca'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Data',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE EDMSO TO DISK =
''c:\MSDESQLBACKUP\EDMSO.dat_bak'',BACKUP DATABASE master TO DISK =
''c:\MSDESQLBACKUP\master.dat_bak'',BACKUP DATABASE model TO DISK =
''c:\MSDESQLBACKUP\model.dat_bak'',BACKUP DATABASE msdb TO DISK =
''c:\MSDESQLBACKUP\msdb.dat_bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Log',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG EDMSO TO DISK =
''c:\MSDESQLBACKUP\EDMSO.log_bak'',BACKUP LOG master TO DISK =
''c:\MSDESQLBACKUP\master.log_bak'',BACKUP LOG model TO DISK =
''c:\MSDESQLBACKUP\model.log_bak'',BACKUP LOG msdb TO DISK =
''c:\MSDESQLBACKUP\msdb.log_bak''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'MSDEBACKUPJOB', @.server_name = N'(local)'
-- Run job. Starts the job immediately.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'MSDEBACKUPJOB',
@.name = 'ScheduledBackup_msdb',
@.freq_type = 4, --daily
@.freq_interval = 1, --once
@.active_start_time = '153000' --(10:30 am) 24hr HHMMSS.

Backup multiple database wiht msde in a sql script problem

HI,
I'm new to sql and i would like to run a script to make a backup of msde dat
abase, i have modified this script but i'm getting some errors running it. C
ould any first tell me if my @.command syntaxe is correct if not what would b
e a way of setting a schedu
led job to run a backup of multiple database in one job.
Here are the errors i'm getting
C:\Documents and Settings\ncsadmin>osql -U sa -i c:\smslab3.sql -n
Password:
Msg 119, Level 15, State 1, Server SMSLAB3, Line 7
Must pass parameter number 5 and subsequent parameters as '@.name = value'.
After the form '@.name = value' has been used, all subsequent parameters must
be
passed in the form '@.name = value'.
Msg 119, Level 15, State 1, Server SMSLAB3, Line 7
Must pass parameter number 5 and subsequent parameters as '@.name = value'.
After the form '@.name = value' has been used, all subsequent parameters must
be
passed in the form '@.name = value'.
Msg 156, Level 15, State 1, Server SMSLAB3, Line 10
Incorrect syntax near the keyword 'EXEC'.
Thanks
---
--This Transact-SQL script creates a backup job and calls sp_start_job to ru
n the job.
-- Create job.
-- You may specify an e-mail address, commented below, and/or pager, etc.
-- For more details about this option or others, see SQL Server Books Online
.
USE msdb
EXEC sp_add_job @.job_name = 'MSDEBACKUPJOB',
@.enabled = 1,
@.description = 'MSDEBACKUPJOB',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'raould.traore@.mcgill.ca'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Data',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE EDMSO TO DISK = ''c:\MSDESQLBACKUP\EDMSO.dat_bak
''','BACKUP DATABASE master TO DISK = ''c:\MSDESQLBACKUP\master.dat_bak''','
BACKUP DATABASE model TO DISK = ''c:\MSDESQLBACKUP\model.dat_bak''','BACKUP
DATABASE msdb TO DISK = ''c
:\MSDESQLBACKUP\msdb.dat_bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Log',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG EDMSO TO DISK = ''c:\MSDESQLBACKUP\EDMSO.log_bak''','
BACKUP LOG master TO DISK = ''c:\MSDESQLBACKUP\master.log_bak''','BACKUP LOG
model TO DISK = ''c:\MSDESQLBACKUP\model.log_bak''','BACKUP LOG msdb TO DIS
K = ''c:\MSDESQLBACKUP\msdb
.log_bak''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'MSDEBACKUPJOB', @.server_name = N'(local)'
-- Run job. Starts the job immediately.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'MSDEBACKUPJOB',
@.name = 'ScheduledBackup_msdb',
@.freq_type = 4, --daily
@.freq_interval = 1, --once
@.active_start_time = '153000' --(10:30 am) 24hr HHMMSS.
---Ralph,
I have modified your script slightly (below) and it should now work. There
was a problem with the number of single quotes which meant that the command
wasn't enclosed in a string. This applied to both commands.
Regards,
Paul Ibison
EXEC sp_add_job @.job_name = 'MSDEBACKUPJOB',
@.enabled = 1,
@.description = 'MSDEBACKUPJOB',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'raould.traore@.mcgill.ca'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Data',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE EDMSO TO DISK =
''c:\MSDESQLBACKUP\EDMSO.dat_bak'',BACKUP DATABASE master TO DISK =
''c:\MSDESQLBACKUP\master.dat_bak'',BACKUP DATABASE model TO DISK =
''c:\MSDESQLBACKUP\model.dat_bak'',BACKUP DATABASE msdb TO DISK =
''c:\MSDESQLBACKUP\msdb.dat_bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Log',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG EDMSO TO DISK =
''c:\MSDESQLBACKUP\EDMSO.log_bak'',BACKUP LOG master TO DISK =
''c:\MSDESQLBACKUP\master.log_bak'',BACKUP LOG model TO DISK =
''c:\MSDESQLBACKUP\model.log_bak'',BACKUP LOG msdb TO DISK =
''c:\MSDESQLBACKUP\msdb.log_bak''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'MSDEBACKUPJOB', @.server_name = N'(local)'
-- Run job. Starts the job immediately.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'MSDEBACKUPJOB',
@.name = 'ScheduledBackup_msdb',
@.freq_type = 4, --daily
@.freq_interval = 1, --once
@.active_start_time = '153000' --(10:30 am) 24hr HHMMSS.

Backup MSE with SingleConnection

Hi all
I have a single connection msde. is it possible to backup the open DB?
Or must be the backuped db closed?Hi
Yes ,it is. You can BACKUP DATABASE while the database is in used. RESTORE
you cannot (If I remember well you will be able to RESTORE the database
online but in SQL Server 2005)
"mib4" <mib4@.discussions.microsoft.com> wrote in message
news:20250D4A-C2EA-4E82-837A-268B54A268DA@.microsoft.com...
> Hi all
> I have a single connection msde. is it possible to backup the open DB?
> Or must be the backuped db closed?

Backup MSDE with NetBackup?

Has anyone been able to to successfully backup/restore MSDE databases via
Veritas NetBackup 5.0?
We backup SQL Server with its own Backup commands. It deals with "dirty"
data so you don't have to shutdown the server. These can be executed from
SQL-DMO or OSQL/ISQL scripts. See TSQL help for lots of examples.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Eric N. Darby" <eric@.NOSPAMNOWAY.com> wrote in message
news:%23IxiyVOeEHA.3680@.TK2MSFTNGP11.phx.gbl...
> Has anyone been able to to successfully backup/restore MSDE databases via
> Veritas NetBackup 5.0?
>
>

Backup MSDE database from .bat file

Hi,
I have seen hundreds of suggestions on how to backup a MSDE database on
the net, most of them have different approaches.
Does it exist an 'approved' way of doing this?
I need to run a .bat file which backs up my MSDE database (i.e. stops
the server, performs copying and the starts the MSDE engine again).
Thanks for hints on where to find the 'bulletproof' way of doing this.
I also need to know how to restore such a backup ;-)
Thanks if there is a kind soul somewhere
best regards
Tor
What you are doing is called a "cold" backup. Which doesn=B4t check the
integrity of the backup afterwards. If you are not concerned about
second or minutes of performance in your backup time frame you can do a
"hot" backup which does a normal backup on SQL Server (Sqls server
sercice running) and additionally can do a verify afterwards the
backup.
Look for the BACKUP statement in the BOL for more information.
Depending on how time critical your backup and your data is, I would
also consider using a transactional / differential backup on top of the
Full backup. This allows you (with the right recovery mode) to do a
restore point-in-time. Look for Restore and backup considerations in
the BOL for further information.
HTH, Jens Suessmeyer.
|||Jens,
Thanks for your quick reply. As this is a small database I assume that
it wouldn't take too long to do a 'hot' backup. However this is a MSDE
install made from a third party application and I don't know what 'BOL'
is.
If you or someone else can point me in a direction where I can find
approved scripts for backup and restore in my sceanrio I would
appreciate that. I don't want to find out when it is too late that the
performed scripts were inadequate ;-|
Thanks again
Tor
|||hi,
bushtor wrote:
> Jens,
> Thanks for your quick reply. As this is a small database I assume
> that it wouldn't take too long to do a 'hot' backup. However this is
> a MSDE install made from a third party application and I don't know
> what 'BOL' is.
> If you or someone else can point me in a direction where I can find
> approved scripts for backup and restore in my sceanrio I would
> appreciate that. I don't want to find out when it is too late that
> the performed scripts were inadequate ;-|
http://support.microsoft.com/default...-US;q325003#12
have a look at the entire article about oSql.exe "uses" so that you can pass
a .sql file to oSql.exe itself from a bat file to perform the required
task..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks for the
http://support.microsoft.com/default...-US;q325003#12
link.
I played a bit with the osql commands but I couldn't get it to work due
to lack of access rights.
This MSDE backup should be run by a 'normal' user with no admin
priviledges. I hoped to put the necessary commands in a .bat file.
However the logged in user did not have the appropriate rights when I
tried to execute the osql backup command. Can I somehow explicit input
the db's 'sa' password and execute:
osql -E -q "EXEC sp_grantdbaccess 'sa', 'sapassword'"
osql -E -q "BACKUP DATABASE mydb TO DISK = 'C:\mydb.bak'"
-- logout command ?
... or how do I do this to have an unpliviledged user to perform a
backup?
regards
Tor
|||hi,
bushtor wrote:
> tried to execute the osql backup command. Can I somehow explicit
> input the db's 'sa' password and execute:
> osql -E -q "EXEC sp_grantdbaccess 'sa', 'sapassword'"
> osql -E -q "BACKUP DATABASE mydb TO DISK = 'C:\mydb.bak'"
> -- logout command ?
define a SQL Server Login for backup operations and map it to a database
user (and make it) member of the db_backupoperator fixed role..
(http://msdn.microsoft.com/library/de...urity_6ndx.asp)
this will "limit" permissions and privileges for that login so that you can
create a bat file including username and password in clear..
osql -S(Local)\InstanceName -Uthe_backup_user -Pthe_wpd -Q "BACKUP DATABASE
mydb TO DISK = 'C:\mydb.bak'" >c:\BckLog.txt
oSql is case sensitive for the supplyed parameters..
http://msdn.microsoft.com/library/de..._osql_1wxl.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> skrev i melding
news:44rt36F3liojU1@.individual.net...

> define a SQL Server Login for backup operations and map it to a database
> user (and make it) member of the db_backupoperator fixed role..
>
(http://msdn.microsoft.com/library/de...-us/adminsql/a
d_security_6ndx.asp)
> this will "limit" permissions and privileges for that login so that you
can
> create a bat file including username and password in clear..
> osql -S(Local)\InstanceName -Uthe_backup_user -Pthe_wpd -Q "BACKUP
DATABASE
> mydb TO DISK = 'C:\mydb.bak'" >c:\BckLog.txt
Hi,
Thanks for the example. But can you elaborate '(Local)\InstanceName' ? Is
this a literal parameter or should I replace it with something?
Will 'the _backup_user' be logged in with MSDE until the computer restarts
or is this a persistent db user from the first time this command is run?
And we can run this command once a day without problems?
What will the restore command look like for a backup like this?
Thanks again for comments on this issue
best regards
Tor
|||Just an additional question to my post just above this one:
Is 'the_backup_user' in the command above the windows user name (who has no
admin rights)?
Isn't it necessary to specify the MSDE SA username and its password
somewhere with these commands?
Thanks for clarification on this in addition to my issues in the post above
regards
Tor
|||hi,
bushtor wrote:
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> skrev i melding
> news:44rt36F3liojU1@.individual.net...
> (http://msdn.microsoft.com/library/de...-us/adminsql/a
> d_security_6ndx.asp)
> Hi,
> Thanks for the example. But can you elaborate '(Local)\InstanceName'
> ? Is this a literal parameter or should I replace it with something?
it's not a literal.. SQL Server 2000 grants you to install one or more
instances of the engine per machine.. you can have up to 16 instances per
machine where only 1 can be the default instance and all the other will be
named instances
http://msdn.microsoft.com/library/de...setup_2xmb.asp
default instances are "known" to users as "(Local)" for local connections
and "ComputerName" for remote connections...
named instances are "known" to users as "(Local)\InstanceName" for local
connections and "ComputerName\InstanceName" for remote connections...

> Will 'the _backup_user' be logged in with MSDE until the computer
> restarts or is this a persistent db user from the first time this
> command is run?

> Is 'the_backup_user' in the command above the windows user name (who
> has no admin rights)?
>
the "the_backup_user" is intended as a SQL Server Login, so that you do not
have to reboot, disconnect or switch to that Windows account.. there will
be no corresponding Windows account but just a SQL Server login you provide
for this quite-admin task..

> And we can run this command once a day without problems?
yes... or even schedule it via AT/SCHTASKS...

> What will the restore command look like for a backup like this?
osql -S(Local)\InstanceName -Uthe_Restore_user -Pthe_wpd -Q "RESTORE
DATABASE [MyDbl] FROM DISK = N'c:\MydB,bak' WITH FILE = 1" >c:\RestLog.txt
to restore a database the logged user has to be member of the sysadmin or
dbcreatore server role or be member of the db_owner of the database to be
restored..
no one must be connected to the database while restoring it..
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
usually the restore operation is not performed on a "routine" base but only
on demand when required, so you usually do not set up an apposit script for
it but perform it if and when required to.. and in this case you can use
"sa" SQL Server login as well without the security hole to store it's
credential (the pwd) in a bat file (which is not a good idea indeed :D)

> Isn't it necessary to specify the MSDE SA username and its password
> somewhere with these commands?
as long as you use another (the_backup_user) login/user with enogth
permissions you are not required to use "sa"... "sa" is a special "system"
SQL Server login member of the sysadim server role and can perform whatever
tast on a SQL Server instance... usually you should provide a policy of
least privileges and thus only grant the permissions as required, no more,
better less :D (you can later adjust)... and I will always stress this
politics :D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote
news:453od6F4oru9U1@.individual.net...

> it's not a literal.. SQL Server 2000 grants you to install one or more
> instances of the engine per machine.. you can have up to 16 instances per
> machine where only 1 can be the default instance and all the other will be
> named instances
Will the name(s) of the(se) instance(s) be listed in the small MSDE SQL
'manager' applet opened from the systray icon?
I haven't had access to the concerned computer for a week but I will try
this tomorrow. Thanks a lot for all your hints.
Tor

Backup MSDE database

Hi,

we have about 12 databases in an MSDE server.

We can install the enterprise manager, but cant get the maintenance plans to work. (fields stay empty and setting dont get saved)
I've searched a bit, but couldn't fix it.

No problem, i created a job, with 12 T-SQL commands, and the backups are working perfect.

Now we wanne backup with a history (we take a backup every 4 hours of the day), because now they always get overwritten.

So im trying to change my transactSQL to create dynamic files.

The code i'm using right now :

BACKUP DATABASE PW0001A00 TO DISK = 'C:\SQLBACKUP\PW0001A00.BAK' WITH INIT, NOUNLOAD, NOSKIP, STATS=10, NOFORMAT

I've tried different things like

BACKUP DATABASE PW0001A00 TO DISK = 'C:\SQLBACKUP\PW0001A00' + DATEPART(month, GETDATE()) + '.BAK' WITH INIT, NOUNLOAD, NOSKIP, STATS=10, NOFORMAT

but nothing worked.

so basically I wanne be able to create dynamic filenames in the above command.

You got any ideas/solutions.

tnx.Take Umachander's http://www.umachandar.com/technical/SQL70Scripts/Main30.htm solution in this case.|||thank you.

i'll try that solution. looks good. :-)

Backup MSDE database

How add backup job schedule MSDE database with sqldmo(vb.net). Freq_type =
daily and file name (.bak and .log) is different for all days.
hi Viktor,
Viktor Zadro wrote:
> How add backup job schedule MSDE database with sqldmo(vb.net).
> Freq_type = daily and file name (.bak and .log) is different for all
> days.
hi Viktor,
as you are doing a scheduled backup job, you should resort on
Transact-SQL...
create your job and job schedules as needed and, regarding the step, add a
T-SQL step with a command that will auto-modify the backup file name at each
execution like
SET NOCOUNT ON
DECLARE @.fName VARCHAR(256)
SELECT @.fName = CONVERT(VARCHAR(19), getdate() , 120)
SELECT @.fName = REPLACE( @.fName , ' ' , '_')
SELECT @.fName = REPLACE( @.fName , '-' , '_')
SELECT @.fName = REPLACE( @.fName , ':' , '_')
SELECT @.fName = 'C:\' + @.fName + '.bak'
SELECT @.fName
BACKUP DATABASE [a] TO DISK = @.fName WITH INIT ,
NOUNLOAD ,
NAME = N'a BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT
that will backup to
C:\2005_01_13_12_50_25.bak
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

BACKUP MSDE

WE HAVE A COLLECTION OF STUDENT MANAGEMENT MODULES THAT
USE THE MICROSOFT SQL SERVER DESKTOP ENGINE (MSDE). OUR
USER BASE IS MOSTLY IT ILLITERATE SO WE NEED TO DEVELOP A
SIMPLE ONE CLICK PROCEDURE FOR BACKING UP THE DATABASE
AND SOME OTHER USER FILES LIKE WORD ABD EXCEL. IS IT
POSSIBLE TO USE MICROSOFT BACKUP FOR THIS PROCESS AND IF
POSSIBLE, IS IT NECESSARY TO STOP THE SERVER WHILE DOING
SO.You will have to write an application that sends the native BACKUP command
to SQL Server, to backup the required databses. You could also make use of
SQL DMO object model for this purpose.
And no, the databases and server can be online while the backups are
happening.
See SQL Server 2000 Books Online for more information on BACKUP command.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"JOHN HAMILTON" <anonymous@.discussions.microsoft.com> wrote in message
news:fefa01c3f226$7f71cf70$a501280a@.phx.gbl...
WE HAVE A COLLECTION OF STUDENT MANAGEMENT MODULES THAT
USE THE MICROSOFT SQL SERVER DESKTOP ENGINE (MSDE). OUR
USER BASE IS MOSTLY IT ILLITERATE SO WE NEED TO DEVELOP A
SIMPLE ONE CLICK PROCEDURE FOR BACKING UP THE DATABASE
AND SOME OTHER USER FILES LIKE WORD ABD EXCEL. IS IT
POSSIBLE TO USE MICROSOFT BACKUP FOR THIS PROCESS AND IF
POSSIBLE, IS IT NECESSARY TO STOP THE SERVER WHILE DOING
SO.|||MANY THANKS NARAYANA...

backup MSDE

Hello,

i'm trying to make a backup for my MSDE database with that .bat file:

cd C:\Program Files\Microsoft SQL Server\80\Tools\Binn
BACKUP DATABASE db1 TO DISK = 'C:\MSDE-backup\db1.bak'
pause

I get the following error:
The filename, directory name, or a volume label is incorrect

What i know for sure is that db1 exist and C:\MSDE-backup\db1.bak exist too.

When we download sql2kdesksp3.exe, did we have everything to perform BACKUP and RESTORE or where is my mistake ?

MSDE database work properly and i can connect to it. I have sysadmin permission.

Thanks.don't you have to run this using osql?

cs|||it is not what I did ? where is my mistake ?|||you have to run the BACKUP command through osql.exe I don't see that in your batch file.

cs|||is it possible you give me the correct batch file because i don't understand. thanks.|||In your first line, you CD (change directory).

In your second line you are trying to call some program called "backup", which doesn't exist.

You need to write:

osql.exe <other stuff here
which will call osql.exe and pass in the appropriate parameters.

Cheers
Ken|||That does not work:

cd C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
osql.exe
BACKUP DATABASE db1 TO DISK = 'C:\MSDE-backup\db1.bak'
pause|||What Ken is saying is that the BACKUP command is not a program or DOS command. It's a SQL command. As such, you have to have a way to send that command to SQL Server. OSQL.EXE is a program that accepts a SQL command as one of its parameters.

Try this...
cd C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
osql.exe -q "BACKUP DATABASE db1 TO DISK = 'C:\MSDE-backup\db1.bak'" -E
pause|||I had the good and fast response of another forum:

cd C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
osql -S servername -E -Q "BACKUP DATABASE db1 TO DISK = 'C:\MSDE-backup\db1.bak'"
pause

thanks anyway|||I first tried m33b example with no luck then ronba's AND IT WORKED.

Thanks for saving my sleepless night ;)...<|||Can this .bat file also be modified to restore??|||osql is a command-line utility for executing most any SQL command. As such, you should be able to build a RESTORE command that would do what you want.|||Thanks, but is it not just a matter of replacing the word BACKUP with RESTORE and moving the paths a little around ?
But I see there is special options in the command...|||take a look at that link below, that perhaps will help you:

http://support.microsoft.com/default.aspx?scid=kb;en-us;325003|||There are quite a few options on the SQL restore command. Commands for replacing an existing database, moving the file to a new location, etc. You'd definitly want to find the documentation on the RESTORE command to get it to do exactly what you want.

BACKUP MSDE

WE HAVE A COLLECTION OF STUDENT MANAGEMENT MODULES THAT
USE THE MICROSOFT SQL SERVER DESKTOP ENGINE (MSDE). OUR
USER BASE IS MOSTLY IT ILLITERATE SO WE NEED TO DEVELOP A
SIMPLE ONE CLICK PROCEDURE FOR BACKING UP THE DATABASE
AND SOME OTHER USER FILES LIKE WORD ABD EXCEL. IS IT
POSSIBLE TO USE MICROSOFT BACKUP FOR THIS PROCESS AND IF
POSSIBLE, IS IT NECESSARY TO STOP THE SERVER WHILE DOING
SO.You will have to write an application that sends the native BACKUP command
to SQL Server, to backup the required databses. You could also make use of
SQL DMO object model for this purpose.
And no, the databases and server can be online while the backups are
happening.
See SQL Server 2000 Books Online for more information on BACKUP command.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"JOHN HAMILTON" <anonymous@.discussions.microsoft.com> wrote in message
news:fefa01c3f226$7f71cf70$a501280a@.phx.gbl...
WE HAVE A COLLECTION OF STUDENT MANAGEMENT MODULES THAT
USE THE MICROSOFT SQL SERVER DESKTOP ENGINE (MSDE). OUR
USER BASE IS MOSTLY IT ILLITERATE SO WE NEED TO DEVELOP A
SIMPLE ONE CLICK PROCEDURE FOR BACKING UP THE DATABASE
AND SOME OTHER USER FILES LIKE WORD ABD EXCEL. IS IT
POSSIBLE TO USE MICROSOFT BACKUP FOR THIS PROCESS AND IF
POSSIBLE, IS IT NECESSARY TO STOP THE SERVER WHILE DOING
SO.|||MANY THANKS NARAYANA...

Backup Maintenance Plan -> MSDE

Hi
I have a problem creating a backup maintenance plan on MDSE 2000 using the
wizard. I want the backup to be written to disk and not into the default
backup directory. First I set the schedule to Sundays at 02:00 and in the
next prompt I set it to write the backup to D:\SQLBUP. So far everything
looks ok but when I try to set that old files should be removed, I can only
set the number but not of what (hours, days, weeks etc). That dropdown menu
is completely blank. So I skip it and set the file extension to BAK. I finish
the rest of the wizard with the default values.
When the maintenance plan is finished I go back and look at its properties.
Strangely enough the path I set for the backup is now blank and so is the
schedule!
Even if I try to set it again in the properties and then click ok and open
it again it is once more lost!
Can anybody tell me why?
Is it because it is MSDE or...?
Regards
Wayne
I have the same Problem. Do you have an answer?
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I don't know why the gui is doing that (do you have the latest service packs
installed?) but it should be easy enough to fix. Since the removal of old
backups is not stored in metadata tables you can edit this pretty easy. It
is simply an option to the xp_sqlmaint call.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
9EEB1492-1A6C-4F16-B752-D86A325127CA -VrfyBackup -BkUpMedia DISK -BkUpDB
"C:\Data\Backups" -DelBkUps 4WEEKS -BkExt "BAK"'
The above is what the job step looks like for a Full backup plan that I just
created with a delete of 4 weeks. check out BooksOnLine under "sqlmaint
utility" for details on what each of the options are. So just edit the job
to be the way you want it with regards to the delete part.
Andrew J. Kelly SQL MVP
"Gaston Oxman" <oxmangas@.hotmail.com> wrote in message
news:%23SektOLEFHA.1564@.TK2MSFTNGP09.phx.gbl...
> I have the same Problem. Do you have an answer?
> Thanks
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!