Discussion:
MSDE backup script question
(too old to reply)
Microsoft News
2009-04-08 16:14:05 UTC
Permalink
We are running the following script to backup an MSDE 2000 DB:

osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"

Our problem is that the above script *appends* and we want to *overwrite*
the DB every night. Can someone tell me what I need to do to change the
script to *overwrite*?

TIA,


Clayton
Kevin3NF
2009-04-08 16:40:18 UTC
Permalink
With Init
--
Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.

Twitter: Kevin3NF
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to *overwrite*
the DB every night. Can someone tell me what I need to do to change the
script to *overwrite*?
TIA,
Clayton
Richard Mueller [MVP]
2009-04-08 16:43:07 UTC
Permalink
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to *overwrite*
the DB every night. Can someone tell me what I need to do to change the
script to *overwrite*?
TIA,
Use the INIT option to overwrite. The default is NOINIT, which appends. See
this link:

http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Microsoft News
2009-04-09 14:58:33 UTC
Permalink
Hey guys,

I have tried the following but still can't get it to *overwrite*:

osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'" SKIP INIT

As you can see, I have addedd *SKIP* and *INIT* but it still appends. Any
other ideas?


TIA,


Clayton
Post by Richard Mueller [MVP]
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to *overwrite*
the DB every night. Can someone tell me what I need to do to change the
script to *overwrite*?
TIA,
Use the INIT option to overwrite. The default is NOINIT, which appends.
http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Iain Sharp
2009-04-09 15:26:03 UTC
Permalink
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH INIT "

regards

Iain
Post by Microsoft News
Hey guys,
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'" SKIP INIT
As you can see, I have addedd *SKIP* and *INIT* but it still appends. Any
other ideas?
TIA,
Clayton
Post by Richard Mueller [MVP]
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to *overwrite*
the DB every night. Can someone tell me what I need to do to change the
script to *overwrite*?
TIA,
Use the INIT option to overwrite. The default is NOINIT, which appends.
http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Richard Mueller [MVP]
2009-04-09 15:37:04 UTC
Permalink
First, the INIT option must be included in the SQL statement, so it must be
in the quoted string. Next, this is a "General With Option" so you should
use WITH INIT. I believe the syntax would be:

osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
Hey guys,
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'" SKIP INIT
As you can see, I have addedd *SKIP* and *INIT* but it still appends. Any
other ideas?
TIA,
Clayton
Post by Richard Mueller [MVP]
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to
*overwrite* the DB every night. Can someone tell me what I need to do
to change the script to *overwrite*?
TIA,
Use the INIT option to overwrite. The default is NOINIT, which appends.
http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Microsoft News
2009-04-09 18:19:46 UTC
Permalink
No luck with the following command:

osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"

It's still appending (my DB gets bigger every time I run the command).

Any other ideas?


Clayton
Post by Richard Mueller [MVP]
First, the INIT option must be included in the SQL statement, so it must
be in the quoted string. Next, this is a "General With Option" so you
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
Hey guys,
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'" SKIP INIT
As you can see, I have addedd *SKIP* and *INIT* but it still appends.
Any other ideas?
TIA,
Clayton
Post by Richard Mueller [MVP]
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to
*overwrite* the DB every night. Can someone tell me what I need to do
to change the script to *overwrite*?
TIA,
Use the INIT option to overwrite. The default is NOINIT, which appends.
http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Richard Mueller [MVP]
2009-04-09 19:22:47 UTC
Permalink
The syntax help in the SQL Server links are very difficult for me to read. I
got an error with a similar command "near INIT". I see now that WITH
parameters should be comma separated. The syntax should be:

osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP, INIT"

I use Windows Integrated Authentication (rather than SQL logins), so the
command I used was similar to:

osql -S MyServer\MyInstance -E -d Master -Q "BACKUP DATABASE MyDatabase TO
DISK = 'C:\MyFolder\MyTest.bak' WITH SKIP, INIT"

I also have a named instance. I connect to the Master database to perform
backups. When I tested the above repeatedly the date/time changed, but not
the size. I have not had this problem in the past because I name my backups
after the date/time of the backup, so a new backup is created each time. I
need to delete old ones periodically, but I want to have at least several
backups.
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
It's still appending (my DB gets bigger every time I run the command).
Any other ideas?
Clayton
Post by Richard Mueller [MVP]
First, the INIT option must be included in the SQL statement, so it must
be in the quoted string. Next, this is a "General With Option" so you
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
Hey guys,
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'" SKIP INIT
As you can see, I have addedd *SKIP* and *INIT* but it still appends.
Any other ideas?
TIA,
Clayton
Post by Richard Mueller [MVP]
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to
*overwrite* the DB every night. Can someone tell me what I need to do
to change the script to *overwrite*?
TIA,
Use the INIT option to overwrite. The default is NOINIT, which appends.
http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Richard Mueller [MVP]
2009-04-09 20:44:43 UTC
Permalink
I'm out of ideas. I can only confirm that for me without the WITH options
each backup is appended to the file and it grows larger each time I run the
command. As soon as I add "WITH SKIP, INIT" and run it again, all backups
are replaced by the latest backup and the file goes back to the original
size.
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Man, still no luck,
Everytime I run the command my BD gets bigger. Here is the last command I
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP, INIT"
Can you think of anything else?
TIA,
Clayton
Post by Richard Mueller [MVP]
The syntax help in the SQL Server links are very difficult for me to
read. I got an error with a similar command "near INIT". I see now that
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP, INIT"
I use Windows Integrated Authentication (rather than SQL logins), so the
osql -S MyServer\MyInstance -E -d Master -Q "BACKUP DATABASE MyDatabase
TO DISK = 'C:\MyFolder\MyTest.bak' WITH SKIP, INIT"
I also have a named instance. I connect to the Master database to perform
backups. When I tested the above repeatedly the date/time changed, but
not the size. I have not had this problem in the past because I name my
backups after the date/time of the backup, so a new backup is created
each time. I need to delete old ones periodically, but I want to have at
least several backups.
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
It's still appending (my DB gets bigger every time I run the command).
Any other ideas?
Clayton
Post by Richard Mueller [MVP]
First, the INIT option must be included in the SQL statement, so it
must be in the quoted string. Next, this is a "General With Option" so
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
Hey guys,
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'" SKIP INIT
As you can see, I have addedd *SKIP* and *INIT* but it still appends.
Any other ideas?
TIA,
Clayton
Post by Richard Mueller [MVP]
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to
*overwrite* the DB every night. Can someone tell me what I need to
do to change the script to *overwrite*?
TIA,
Use the INIT option to overwrite. The default is NOINIT, which
http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Microsoft News
2009-04-09 20:59:08 UTC
Permalink
Thanks for all you help dude!


Clayton
Post by Richard Mueller [MVP]
I'm out of ideas. I can only confirm that for me without the WITH options
each backup is appended to the file and it grows larger each time I run
the command. As soon as I add "WITH SKIP, INIT" and run it again, all
backups are replaced by the latest backup and the file goes back to the
original size.
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Man, still no luck,
Everytime I run the command my BD gets bigger. Here is the last command
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP, INIT"
Can you think of anything else?
TIA,
Clayton
Post by Richard Mueller [MVP]
The syntax help in the SQL Server links are very difficult for me to
read. I got an error with a similar command "near INIT". I see now that
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP, INIT"
I use Windows Integrated Authentication (rather than SQL logins), so the
osql -S MyServer\MyInstance -E -d Master -Q "BACKUP DATABASE MyDatabase
TO DISK = 'C:\MyFolder\MyTest.bak' WITH SKIP, INIT"
I also have a named instance. I connect to the Master database to
perform backups. When I tested the above repeatedly the date/time
changed, but not the size. I have not had this problem in the past
because I name my backups after the date/time of the backup, so a new
backup is created each time. I need to delete old ones periodically, but
I want to have at least several backups.
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
It's still appending (my DB gets bigger every time I run the command).
Any other ideas?
Clayton
Post by Richard Mueller [MVP]
First, the INIT option must be included in the SQL statement, so it
must be in the quoted string. Next, this is a "General With Option" so
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
Hey guys,
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'" SKIP INIT
As you can see, I have addedd *SKIP* and *INIT* but it still appends.
Any other ideas?
TIA,
Clayton
Post by Richard Mueller [MVP]
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to
*overwrite* the DB every night. Can someone tell me what I need to
do to change the script to *overwrite*?
TIA,
Use the INIT option to overwrite. The default is NOINIT, which
http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Microsoft News
2009-04-09 20:11:16 UTC
Permalink
Man, still no luck,

Everytime I run the command my BD gets bigger. Here is the last command I
ran:

osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP, INIT"

Can you think of anything else?

TIA,


Clayton
Post by Richard Mueller [MVP]
The syntax help in the SQL Server links are very difficult for me to read.
I got an error with a similar command "near INIT". I see now that WITH
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP, INIT"
I use Windows Integrated Authentication (rather than SQL logins), so the
osql -S MyServer\MyInstance -E -d Master -Q "BACKUP DATABASE MyDatabase TO
DISK = 'C:\MyFolder\MyTest.bak' WITH SKIP, INIT"
I also have a named instance. I connect to the Master database to perform
backups. When I tested the above repeatedly the date/time changed, but not
the size. I have not had this problem in the past because I name my
backups after the date/time of the backup, so a new backup is created each
time. I need to delete old ones periodically, but I want to have at least
several backups.
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
It's still appending (my DB gets bigger every time I run the command).
Any other ideas?
Clayton
Post by Richard Mueller [MVP]
First, the INIT option must be included in the SQL statement, so it must
be in the quoted string. Next, this is a "General With Option" so you
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak' WITH SKIP INIT"
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Post by Microsoft News
Hey guys,
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'" SKIP INIT
As you can see, I have addedd *SKIP* and *INIT* but it still appends.
Any other ideas?
TIA,
Clayton
Post by Richard Mueller [MVP]
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to
*overwrite* the DB every night. Can someone tell me what I need to
do to change the script to *overwrite*?
TIA,
Use the INIT option to overwrite. The default is NOINIT, which
http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
Dan Guzman
2009-04-09 11:56:40 UTC
Permalink
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Also consider changing the password of the sa account to a strong one. This
is a common vulnerability that will be exploited by malware.
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to *overwrite*
the DB every night. Can someone tell me what I need to do to change the
script to *overwrite*?
TIA,
Clayton
Microsoft News
2009-04-09 13:41:12 UTC
Permalink
Hey Dan,

How would I change the password on MSDE 2000?



Clayton
Post by Dan Guzman
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Also consider changing the password of the sa account to a strong one.
This is a common vulnerability that will be exploited by malware.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
Post by Microsoft News
osql -Usa -P -n -Q "Backup Database pdticket To Disk =
'E:\mssql7\backup\pdticket.bak'"
Our problem is that the above script *appends* and we want to *overwrite*
the DB every night. Can someone tell me what I need to do to change the
script to *overwrite*?
TIA,
Clayton
Andrea Montanari
2009-04-09 14:33:11 UTC
Permalink
hi Clayton,
Post by Microsoft News
How would I change the password on MSDE 2000?
http://support.microsoft.com/kb/321081/en-us

osql.exe -Usa -P -SComputerName\InstanceName -Q "EXEC master..sp_password
@old=NULL, @new='newPwd', @loginame='sa'"

regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
http://www.hotelsole.com - http://www.hotelsolericcione.de
--------- remove DMO to reply
Microsoft News
2009-04-09 14:55:50 UTC
Permalink
Thanks Andrea!


Clayton
Post by Andrea Montanari
hi Clayton,
Post by Microsoft News
How would I change the password on MSDE 2000?
http://support.microsoft.com/kb/321081/en-us
osql.exe -Usa -P -SComputerName\InstanceName -Q "EXEC master..sp_password
@old=NULL, @new='newPwd', @loginame='sa'"
regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
http://www.hotelsole.com - http://www.hotelsolericcione.de
--------- remove DMO to reply
Loading...