Schedule Backup databases

I am a total newbie on SQL Server 2000. Now we have a lot of databases stored in SQL server, and logins etc. I think it should be really easy, but I just can't find it. How can I just schedule a SQL-server backup, so a backup of the complete SQL server data, all databases etc. And how can you run such a task at a moment you want it to? I used local packages and then transfer databases task to transfer the databases, but it all seems so tedious.
Thanks a lot, I'd appreciate it..MJB
You will have to create a job.
Go to Management- SQL Server Agent - Jobs
In case you will want to run the job manually then just right click on the
job and click Start Job.
"MJB" <anonymous@.discussions.microsoft.com> wrote in message
news:726525AF-5406-4FA5-BD2A-514319B16A15@.microsoft.com...
> I am a total newbie on SQL Server 2000. Now we have a lot of databases
stored in SQL server, and logins etc. I think it should be really easy, but
I just can't find it. How can I just schedule a SQL-server backup, so a
backup of the complete SQL server data, all databases etc. And how can you
run such a task at a moment you want it to? I used local packages and then
transfer databases task to transfer the databases, but it all seems so
tedious.
> Thanks a lot, I'd appreciate it...
>|||Hi,
A very simple method to backup your database is to use a maintenance plan.
You can create these from the enterprise manager. However I would recommend
using your own jobs, so maybe you should brush up your T-SQL.
Jo S.
"MJB" <anonymous@.discussions.microsoft.com> schreef in bericht
news:726525AF-5406-4FA5-BD2A-514319B16A15@.microsoft.com...
> I am a total newbie on SQL Server 2000. Now we have a lot of databases
stored in SQL server, and logins etc. I think it should be really easy, but
I just can't find it. How can I just schedule a SQL-server backup, so a
backup of the complete SQL server data, all databases etc. And how can you
run such a task at a moment you want it to? I used local packages and then
transfer databases task to transfer the databases, but it all seems so
tedious.
> Thanks a lot, I'd appreciate it...
>|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNAME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"MJB" <anonymous@.discussions.microsoft.com> wrote in message
news:726525AF-5406-4FA5-BD2A-514319B16A15@.microsoft.com...
> I am a total newbie on SQL Server 2000. Now we have a lot of databases
stored in SQL server, and logins etc. I think it should be really easy, but
I just can't find it. How can I just schedule a SQL-server backup, so a
backup of the complete SQL server data, all databases etc. And how can you
run such a task at a moment you want it to? I used local packages and then
transfer databases task to transfer the databases, but it all seems so
tedious.
> Thanks a lot, I'd appreciate it...
>|||Thanks all for the reply's. That was all I needed, running fine now. Thanks
Cheers