backup sql with sripts

I have database in sql 'crmsource.mdf" I like to automate a backup for it.
OSQL -Usa -PmyPasword -n -Q "BACKUP DATABASE crmsource TO DISK =
'c:\backup\crmsource.dat_bak'"
I get an error:
[shared memory] SQL Server does not exist or access denied.
[shared memory]connectionOpen <connect>>.
Am I haveing this problem because the database is open? Is there a way to
backup without closing the database?
No you wont need to close the DB, you want to perform a hot backup. Hot
backups are done during the database is in use.
Make sure that you are heading for the right server with specifying the
server you wan to connect and issue the command via the
switch -SServername[\InstanceName].
If you aalready stuck with this, read the article
http://support.microsoft.com/default...;en-us;Q328306 that will
guide you to identify your problem.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"gemirkhanian@.hotmail.com"
<gemirkhanianhotmailcom@.discussions.microsoft.com> schrieb im Newsbeitrag
news:263E0B7C-D7AB-4474-A074-D3B177DD6CF3@.microsoft.com...
>I have database in sql 'crmsource.mdf" I like to automate a backup for it.
> OSQL -Usa -PmyPasword -n -Q "BACKUP DATABASE crmsource TO DISK =
> 'c:\backup\crmsource.dat_bak'"
> I get an error:
> [shared memory] SQL Server does not exist or access denied.
> [shared memory]connectionOpen <connect>>.
> Am I haveing this problem because the database is open? Is there a way to
> backup without closing the database?
|||Hello Jens
I created a batch file, content of the batch file is
OSQL -Sfs5-win\grouplink -Usa -Ppassword -n "BACKUP DATABASE crmsource TO
DISK" = 'c:\backup\crmsource.dat_bak'
I now get a syntax error.
I am still trying to automate a backup please help.
"Jens Sü?meyer" wrote:

> No you won′t need to close the DB, you want to perform a hot backup. Hot
> backups are done during the database is in use.
> Make sure that you are heading for the right server with specifying the
> server you wan to connect and issue the command via the
> switch -SServername[\InstanceName].
> If you aalready stuck with this, read the article
> http://support.microsoft.com/default...;en-us;Q328306 that will
> guide you to identify your problem.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "gemirkhanian@.hotmail.com"
> <gemirkhanianhotmailcom@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:263E0B7C-D7AB-4474-A074-D3B177DD6CF3@.microsoft.com...
>
>
|||What always helps in newsgroups is the exact error information, becasue
syntax errors are a wide range to search for, i guess in your examples you
didnt put the quotes right around that and that qour switch isnt mentioned
in a right way you, the best way is to use the -Q for the Query you want to
execute:
OSQL -Sfs5-win\grouplink -Usa -Ppassword -n -Q"BACKUP DATABASE crmsource TO
DISK = 'c:\backup\crmsource.dat_bak'"
That should work via Copy & Paste.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"gemirkhanian@.hotmail.com"
<gemirkhanianhotmailcom@.discussions.microsoft.com> schrieb im Newsbeitrag
news:65E020C3-67CB-4EDE-9E88-AF6B0994240B@.microsoft.com...[vbcol=seagreen]
> Hello Jens
> I created a batch file, content of the batch file is
> OSQL -Sfs5-win\grouplink -Usa -Ppassword -n "BACKUP DATABASE crmsource TO
> DISK" = 'c:\backup\crmsource.dat_bak'
> I now get a syntax error.
> I am still trying to automate a backup please help.
>
> "Jens Smeyer" wrote:

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign it
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than one
week?
I thank you for your help...
Regards,
Jamil
I would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||The following scripts will backup the database and name the backup along with
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =
@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
This allows you to specify how long you want the backup files to be retained.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign i
t
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than on
e
week?
I thank you for your help...
Regards,
JamilI would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil|||The following scripts will backup the database and name the backup along wit
h
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =
@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Current
ly
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week. Al
so
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil|||http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
This allows you to specify how long you want the backup files to be retained
.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s...testBackup.html
http://www.mindsdoor.net/SQLAdmin/s...testBackup.html
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Current
ly
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week. Al
so
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil

Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign it
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than one
week?
I thank you for your help...
Regards,
JamilI would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil|||The following scripts will backup the database and name the backup along with
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL FOR
SELECT Catalog_Name FROM Information_Schema.Schemata
WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD , NAME =@.Name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil|||http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
This allows you to specify how long you want the backup files to be retained.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s_CopyLatestBackup.html
http://www.mindsdoor.net/SQLAdmin/s_RestoreLatestBackup.html
"Jamil Ahsan" wrote:
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil

Backup into individual folders or create timestamps

Hi there,
I am using SQL 2000 SP2 and am trying to establish a viable backup
process. I want to be able to automate backing up databases and then
have them moved to a folder on a file server.
Since I have discovered that I can't backup to a mapped drive I am
having to backup locally. What I want to do though is backup daily and
have it create separate backups per day. I don't want to overwrite
what is currently there, I want it to create a backup called e.g.
Backup_220903, and then on Tuesday create another file called
Backup_230903 and so on.
After that I then need a reliable way of having the files copied
automatically to my remote file server
Does anyone know how I can do this'
Many thanks in advanceYou can backup directly to the network folder.
You need to use the full UNC name. Example :-
BACKUP DATABASE Dbname TO DISK = \\servername\sharename\backup.bak WITH INIT
--
HTH
Ryan Waight, MCDBA, MCSE
"Derek" <dryan@.legendware.co.uk> wrote in message
news:72938c9c.0309220639.55a36e94@.posting.google.com...
> Hi there,
> I am using SQL 2000 SP2 and am trying to establish a viable backup
> process. I want to be able to automate backing up databases and then
> have them moved to a folder on a file server.
> Since I have discovered that I can't backup to a mapped drive I am
> having to backup locally. What I want to do though is backup daily and
> have it create separate backups per day. I don't want to overwrite
> what is currently there, I want it to create a backup called e.g.
> Backup_220903, and then on Tuesday create another file called
> Backup_230903 and so on.
> After that I then need a reliable way of having the files copied
> automatically to my remote file server
> Does anyone know how I can do this'
> Many thanks in advance