Problem removing old back up files....

I am having problems with removing the update files from a folder. When
a do a new backup, I want it to overwrite the old backup files with the
new one. Apparently the code I am using is wrong. Any suggestions?Hi,
Would you be able to post your code so that people can see if they can
see anything with it or if it's fine?
If you are using BACKUP statements then you have to add "INIT" to the
"WITH" section. This will overwrite whatever backup device (e.g. the
file name) that you specify in the statement. For example:
BACKUP DATABASE Test_DB
TO DISK = 'C:\Test_DB_Backup.bak'
WITH INIT
WIll overwrite the previous contents of Test_DB_Backup.bak. Note that
there are some caveats with using INIT (namely that it won't overwrite
in certain situations) but these are described in SQL Server Books
Online in the topic about the BACKUP statement.
Hope that helps a bit|||EXECUTE master.dbo.xp_sqlmaint N'-PlanID
88FF9022-F638-4939-99FF-2FD57140382C -Rpt "C:\Program Files\Microsoft
SQL Server\MSSQL\LOG\<Backup Sever Name> Backup4.txt" -DelTxtRpt 2WEEKS
-WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\<Backup Server
Name>\Backup Do Not Touch\<SQL Server Name>" -DelBkUps 1DAYS
-CrBkSubDir -BkExt "BAK"'
There is the one we are using.|||OK, I see...There is an Database Maintenance Plan in place that will
take a backup of the desired database and delete backups older than one
day.
I don't believe that the sqlmaint utility (the utility that will be
called by xp_sqlmaint) has a switch that orders backups to be
overwritten. If you want to take backups but overwrite them each day
then you'll probably need to define your own SQL Server Agent job that
executes the following T-SQL as per whatever schedule you desire:
BACKUP DATABASE <DB_Name>
TO DISK = '<Path Of Backup File (includes the file name)>'
WITH INIT -- Force an overwrite most of the time (some caveats as
described in Books Online)
BTW, I think that generally it is a good idea to keep a few backup
files just in case something goes wrong with the backup you are
currently taking there is something to fall back on.
Hope that helps a bit

Problem recovering old database

> I need to resurrect an old info database from a ms sql 7 server backup
(generated from a sql

quote:

---

> maintenance plan). I have recovered the master, model and msdb databases

---

as well as the old info

quote:

---

> database. Unfortunately the sa password has been lost and once I recover

---

the master DB, I cannot

quote:

---

> log on to the query analyser to get the info I need. I have tried both sql

---

authentication and nt windows

quote:

---

> authentication.
> can anyone point me in the right direction to reset or recover?
> Thanks
> Si

---

The error messages that I get when trying to log into querry analyser are:
Using Windows NT Authentication
Unable to connect to Server \\SQL-SERVER1:
Server: Msg 18456, Level16, State1
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
'SQL-SERVER1\administrator'
----
--
Using SQL Authentication
Unable to connect to Server \\SQL-SERVER1:
Server: Msg 18456, Level16, State1
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'sa'
Thanks
si
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:zFzg4Zn6DHA.568@.cpmsftngxa07.phx.gbl...

quote:

---

> What error message do you get when you attempt to login using the sa
> account?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

---

Any reason you need the system databases? You can restore the the old user
database to a new system. You will need to use sp_change_users_login to
remap the usernames to logins, but the data should be just fine. You can
even restore to an SQL 2000 host system.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John Smith" <so@.funtime.com> wrote in message
news:1075904525.255819@.smtp-1.griffin.com...

quote:

---

> (generated from a sql
databases[QUOTE]
> as well as the old info
recover[QUOTE]
> the master DB, I cannot
sql[QUOTE]
> authentication and nt windows
> The error messages that I get when trying to log into querry analyser are:
> Using Windows NT Authentication
> Unable to connect to Server \\SQL-SERVER1:
> Server: Msg 18456, Level16, State1
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
> 'SQL-SERVER1\administrator'
> ----

---

--

quote:

---

> --
> Using SQL Authentication
> Unable to connect to Server \\SQL-SERVER1:
> Server: Msg 18456, Level16, State1
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'sa'
> Thanks
> si
>
> "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
> news:zFzg4Zn6DHA.568@.cpmsftngxa07.phx.gbl...
rights.[QUOTE]
>
>

---

|||I want to restore the server "as was" for a period of time. I thought I
could just follow the notes supplied by microsoft on moving databases to a
new server. They recommend that the system databases are first restored.
Thanks for the help
si
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:ugObu9y6DHA.4012@.tk2msftngp13.phx.gbl...

quote:

---

> Any reason you need the system databases? You can restore the the old

---

user

quote:

---

> database to a new system. You will need to use sp_change_users_login to
> remap the usernames to logins, but the data should be just fine. You can
> even restore to an SQL 2000 host system.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "John Smith" <so@.funtime.com> wrote in message
> news:1075904525.255819@.smtp-1.griffin.com...
> databases
> recover
> sql
are:[QUOTE]
> ----
> --
'sa'[QUOTE]
> rights.
>

---

Problem recovering old database

> I need to resurrect an old info database from a ms sql 7 server backup
(generated from a sql
> maintenance plan). I have recovered the master, model and msdb databases
as well as the old info
> database. Unfortunately the sa password has been lost and once I recover
the master DB, I cannot
> log on to the query analyser to get the info I need. I have tried both sql
authentication and nt windows
> authentication.
>
> can anyone point me in the right direction to reset or recover?
>
> Thanks
>
> Si
The error messages that I get when trying to log into querry analyser are:
Using Windows NT Authentication
Unable to connect to Server \\SQL-SERVER1:
Server: Msg 18456, Level16, State1
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
'SQL-SERVER1\administrator'
----
--
Using SQL Authentication
Unable to connect to Server \\SQL-SERVER1:
Server: Msg 18456, Level16, State1
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'sa'
Thanks
si
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:zFzg4Zn6DHA.568@.cpmsftngxa07.phx.gbl...
> What error message do you get when you attempt to login using the sa
> account?
>
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>Any reason you need the system databases? You can restore the the old user
database to a new system. You will need to use sp_change_users_login to
remap the usernames to logins, but the data should be just fine. You can
even restore to an SQL 2000 host system.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John Smith" <so@.funtime.com> wrote in message
news:1075904525.255819@.smtp-1.griffin.com...
> > I need to resurrect an old info database from a ms sql 7 server backup
> (generated from a sql
> > maintenance plan). I have recovered the master, model and msdb
databases
> as well as the old info
> > database. Unfortunately the sa password has been lost and once I
recover
> the master DB, I cannot
> > log on to the query analyser to get the info I need. I have tried both
sql
> authentication and nt windows
> > authentication.
> >
> > can anyone point me in the right direction to reset or recover?
> >
> > Thanks
> >
> > Si
> The error messages that I get when trying to log into querry analyser are:
> Using Windows NT Authentication
> Unable to connect to Server \\SQL-SERVER1:
> Server: Msg 18456, Level16, State1
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
> 'SQL-SERVER1\administrator'
> ----
--
> --
> Using SQL Authentication
> Unable to connect to Server \\SQL-SERVER1:
> Server: Msg 18456, Level16, State1
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'sa'
> Thanks
> si
>
> "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
> news:zFzg4Zn6DHA.568@.cpmsftngxa07.phx.gbl...
> > What error message do you get when you attempt to login using the sa
> > account?
> >
> > Rand
> > This posting is provided "as is" with no warranties and confers no
rights.
> >
>
>|||I want to restore the server "as was" for a period of time. I thought I
could just follow the notes supplied by microsoft on moving databases to a
new server. They recommend that the system databases are first restored.
Thanks for the help
si
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:ugObu9y6DHA.4012@.tk2msftngp13.phx.gbl...
> Any reason you need the system databases? You can restore the the old
user
> database to a new system. You will need to use sp_change_users_login to
> remap the usernames to logins, but the data should be just fine. You can
> even restore to an SQL 2000 host system.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "John Smith" <so@.funtime.com> wrote in message
> news:1075904525.255819@.smtp-1.griffin.com...
> > > I need to resurrect an old info database from a ms sql 7 server backup
> > (generated from a sql
> > > maintenance plan). I have recovered the master, model and msdb
> databases
> > as well as the old info
> > > database. Unfortunately the sa password has been lost and once I
> recover
> > the master DB, I cannot
> > > log on to the query analyser to get the info I need. I have tried both
> sql
> > authentication and nt windows
> > > authentication.
> > >
> > > can anyone point me in the right direction to reset or recover?
> > >
> > > Thanks
> > >
> > > Si
> >
> > The error messages that I get when trying to log into querry analyser
are:
> >
> > Using Windows NT Authentication
> >
> > Unable to connect to Server \\SQL-SERVER1:
> >
> > Server: Msg 18456, Level16, State1
> > [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
> > 'SQL-SERVER1\administrator'
> >
> ----
> --
> > --
> > Using SQL Authentication
> >
> > Unable to connect to Server \\SQL-SERVER1:
> >
> > Server: Msg 18456, Level16, State1
> > [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
'sa'
> >
> > Thanks
> >
> > si
> >
> >
> > "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
> > news:zFzg4Zn6DHA.568@.cpmsftngxa07.phx.gbl...
> > > What error message do you get when you attempt to login using the sa
> > > account?
> > >
> > > Rand
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
> >
> >
>

Problem inserting data into remote server

Hello,

I'm working on an old database that has been converted to SQL server 2000 and I am having a problem executing the DML statement below. It looks like the problem is related to column file. File seems to be a reserved keyword for sql server. How do I get around this? I already tried brackets around the column name etc.

INSERT INTO [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE ([source], [session_id],session_index, [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time])
SELECT [source], [session_id],
[session_index], [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time] FROM T_LOG_STORAGE WHERE NOT EXISTS ( SELECT session_id FROM [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE derived WHERE T_LOG_STORAGE.source = derived.source AND T_LOG_STORAGE.session_id = derived.session_id AND T_LOG_STORAGE.session_index = derived.session_index) AND T_LOG_STORAGE.source = @.dSource

(1 row(s) affected)

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'file'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].Originally posted by hijinks
Hello,

I'm working on an old database that has been converted to SQL server 2000 and I am having a problem executing the DML statement below. It looks like the problem is related to column file. File seems to be a reserved keyword for sql server. How do I get around this? I already tried brackets around the column name etc.

INSERT INTO [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE ([source], [session_id],session_index, [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time])
SELECT [source], [session_id],
[session_index], [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time] FROM T_LOG_STORAGE WHERE NOT EXISTS ( SELECT session_id FROM [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE derived WHERE T_LOG_STORAGE.source = derived.source AND T_LOG_STORAGE.session_id = derived.session_id AND T_LOG_STORAGE.session_index = derived.session_index) AND T_LOG_STORAGE.source = @.dSource

(1 row(s) affected)

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'file'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].

What is it: (1 row(s) affected) - looks like insert was done? Do you have triggers on remote table?

Try to do simple insert and check is it work at all.|||Actually the (1 row(s) affected) is a by product of me printing the INSERT statement, so the insert does not happen.|||Actually the (1 row(s) affected) is a by product of me printing the INSERT statement, so the insert does not happen.