Autonumber field: files

Showing posts with label files. Show all posts

Tuesday, March 27, 2012

Backing Up My RS Server Recommendation

On a RS Server what is the recommendation for files that I need to backup
besides the db?
Thanks,Make sure you backup the Private Keys used for encryption! Do this with the
RSKeyMgmt utility.
"Andy Jones" <ajones@.rheemac.com> wrote in message
news:uYxHSO9hFHA.1204@.TK2MSFTNGP12.phx.gbl...
> On a RS Server what is the recommendation for files that I need to backup
> besides the db?
> Thanks,
>

Sunday, March 25, 2012

Backing up log files

Hi,
I'd like to know what happens to a log file when you back it up. I received
an error that my log file had grown too much & that I should back it up,
which I did & I no longer received the error.
But what happens when you simply back up a log file? Does it also truncate
unused space or what? It obviously doesn't simply make a backup of it.
Many thanks for helping me understand what is happening here
AntBACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
reason don't want to do transaction log backup then set the recovery model for the database to
"simple".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
> Hi,
> I'd like to know what happens to a log file when you back it up. I received
> an error that my log file had grown too much & that I should back it up,
> which I did & I no longer received the error.
> But what happens when you simply back up a log file? Does it also truncate
> unused space or what? It obviously doesn't simply make a backup of it.
> Many thanks for helping me understand what is happening here
> Ant|||Tibor
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file).
I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
virtual logs file that LOG file is built by and delete only inactive
(written to disk) transaction
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file). If you for some reason don't want to do transaction log backup then
> set the recovery model for the database to "simple".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>|||Hi Uri,
It all depends on what abstraction level we want to be. :-)
At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate", whichever
in English gets the message through). I prefer to say "empty", since I believe it makes it clearer
that the file isn't made smaller.
There are of course much more details what happens when you BACKUP LOG. I stay away from those
details when I see a basic question and I suspect the OP has limited experience with transaction log
internals - like virtual log files. The reasoning is that more details will only cloud the message.
:-)
As for the details, this is how I believe it works. Anyone is of course free to add and correct,
below if off the top of my head:
A VLF can be in four states:
A. Never ever used
B. Used, and all log records are prior than the last checkpoint and have also been written to disk.
C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
D. The active log (where the current log record, "head of the log", is).
BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever the
head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C and D
have status 2.
Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
tend to stick with the higher abstraction level for basic questions. :-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
> LOG file is built by and delete only inactive (written to disk) transaction
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>|||Tibor
The ability to think on abstaction level given from God, I mean you canot
learn this. :-)
And you I think have it.
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file
Yep, that it was my understanding as well
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Tibor
The two middle states have nothing to do with Checkpoint. The have to do
with whether those transactions have been backed up, so the log space can be
reused.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Kalen,
Thanks. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi Tibor
> The two middle states have nothing to do with Checkpoint. The have to do with whether those
> transactions have been backed up, so the log space can be reused.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate",
>> whichever in English gets the message through). I prefer to say "empty", since I believe it makes
>> it clearer that the file isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG. I stay away from those
>> details when I see a basic question and I suspect the OP has limited experience with transaction
>> log internals - like virtual log files. The reasoning is that more details will only cloud the
>> message. :-)
>> As for the details, this is how I believe it works. Anyone is of course free to add and correct,
>> below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have also been written to
>> disk.
>> C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
>> virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever
>> the head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C
>> and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
>> tend to stick with the higher abstraction level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
>> LOG file is built by and delete only inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>|||You're welcome.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWx%23pC1YIHA.3880@.TK2MSFTNGP05.phx.gbl...
> Hi Kalen,
> Thanks. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi Tibor
>> The two middle states have nothing to do with Checkpoint. The have to do
>> with whether those transactions have been backed up, so the log space can
>> be reused.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the
>> log" (or "truncate", whichever in English gets the message through). I
>> prefer to say "empty", since I believe it makes it clearer that the file
>> isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG.
>> I stay away from those details when I see a basic question and I suspect
>> the OP has limited experience with transaction log internals - like
>> virtual log files. The reasoning is that more details will only cloud
>> the message. :-)
>> As for the details, this is how I believe it works. Anyone is of course
>> free to add and correct, below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have
>> also been written to disk.
>> C. Used, but some log records are more recent than last checkpoint
>> and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just
>> allow for re-use of the virtual log file. Basically turning C above into
>> B. A and B can be re-used (overwritten whenever the head of the log
>> moves to this virtual log file). These have status 0 in DBCC LOGINFO,
>> while C and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or
>> whiteboard). Which is why I tend to stick with the higher abstraction
>> level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to
>> re-use virtual logs file that LOG file is built by and delete only
>> inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file). If you for some reason don't want to do transaction log
>> backup then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of
>> it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>>
>

Thursday, March 22, 2012

Backing up a database that was been moved from another server

HI all
I have a database where we have moved the mdf/ldf files to another
server and reattached the database on the new server
I am now trying to backup this database up, but the wizard tell you
the backup has complete but it is too quick for it to be valid as the
db is 2gb
I also always get a message telling me 'The connection to the SQL
Server is broken' Do you want to try recoonecting to it
Any help?
Well ,try to restore your backup with a different name and you will get an
error message if the backup file is corrupted.
<satshergill@.yahoo.co.uk> wrote in message
news:1137407538.287495.195210@.g14g2000cwa.googlegr oups.com...
> HI all
> I have a database where we have moved the mdf/ldf files to another
> server and reattached the database on the new server
>
> I am now trying to backup this database up, but the wizard tell you
> the backup has complete but it is too quick for it to be valid as the
> db is 2gb
> I also always get a message telling me 'The connection to the SQL
> Server is broken' Do you want to try recoonecting to it
> Any help?
>

Backing up a database that was been moved from another server

HI all
I have a database where we have moved the mdf/ldf files to another
server and reattached the database on the new server
I am now trying to backup this database up, but the wizard tell you
the backup has complete but it is too quick for it to be valid as the
db is 2gb
I also always get a message telling me 'The connection to the SQL
Server is broken' Do you want to try recoonecting to it
Any help?Well ,try to restore your backup with a different name and you will get an
error message if the backup file is corrupted.
<satshergill@.yahoo.co.uk> wrote in message
news:1137407538.287495.195210@.g14g2000cwa.googlegroups.com...
> HI all
> I have a database where we have moved the mdf/ldf files to another
> server and reattached the database on the new server
>
> I am now trying to backup this database up, but the wizard tell you
> the backup has complete but it is too quick for it to be valid as the
> db is 2gb
> I also always get a message telling me 'The connection to the SQL
> Server is broken' Do you want to try recoonecting to it
> Any help?
>

Backing up a database that was been moved from another server

HI all
I have a database where we have moved the mdf/ldf files to another
server and reattached the database on the new server
I am now trying to backup this database up, but the wizard tell you
the backup has complete but it is too quick for it to be valid as the
db is 2gb
I also always get a message telling me 'The connection to the SQL
Server is broken' Do you want to try recoonecting to it
Any help?Well ,try to restore your backup with a different name and you will get an
error message if the backup file is corrupted.
<satshergill@.yahoo.co.uk> wrote in message
news:1137407538.287495.195210@.g14g2000cwa.googlegroups.com...
> HI all
> I have a database where we have moved the mdf/ldf files to another
> server and reattached the database on the new server
>
> I am now trying to backup this database up, but the wizard tell you
> the backup has complete but it is too quick for it to be valid as the
> db is 2gb
> I also always get a message telling me 'The connection to the SQL
> Server is broken' Do you want to try recoonecting to it
> Any help?
>sql

Tuesday, March 20, 2012

Backing up .mdf and .ldf files

I want to copy and paste .mdf and .ldf from our SQL server.
Am I correct in saying that the mdf and ldf files are locked into th SQL
databases and require the files to be detached via Enterprise Manager (for
SQL2000)?
In which case, how do I copy the .mdf and .ldf files to another HDD?
Is there a Utility I can use or another way?
Please help.
skc
Lookup sp_detach_db ,sp_attach_db in the BOL.
You can stop your services and copy/paste the files. I prefer
BACKUP/RESTORE operations
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:D6D5728B-1FEC-44AD-80DA-D93293D8CA4B@.microsoft.com...
>I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc
|||Skc wrote:
> I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc
Hi
Why do you want to copy the files rather than using Backup/Restore? If
you want to copy the database files, you'll have to detach them first
using sp_detach_db. You can now copy them as regular files and you can
then use sp_attach_Db to create the database when you've copied the
files to the new server.
Please keep in mind that your database will be unavailable when you run
sp_detach_Db and then until you attach it again with sp_attach_db.
You should also keep in mind, that after you've run the sp_detach_db you
haven't got a working database - not even the original. By this I mean
that if something goes wrong during the detach and the database file get
corrupted, you have lost your database. I know that it's not very likely
that it fails and if it eventually does, you might be able to recover it
in some way, but it's a potential risk.
If you go the backup route, you'll still have your original database
even though your backup doesn't work, so you can always create a new backup.
HTH
Regards
Steen

Backing up .mdf and .ldf files

I want to copy and paste .mdf and .ldf from our SQL server.
Am I correct in saying that the mdf and ldf files are locked into th SQL
databases and require the files to be detached via Enterprise Manager (for
SQL2000)?
In which case, how do I copy the .mdf and .ldf files to another HDD?
Is there a Utility I can use or another way?
Please help.
skcLookup sp_detach_db ,sp_attach_db in the BOL.
You can stop your services and copy/paste the files. I prefer
BACKUP/RESTORE operations
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:D6D5728B-1FEC-44AD-80DA-D93293D8CA4B@.microsoft.com...
>I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc|||Skc wrote:
> I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc
Hi
Why do you want to copy the files rather than using Backup/Restore? If
you want to copy the database files, you'll have to detach them first
using sp_detach_db. You can now copy them as regular files and you can
then use sp_attach_Db to create the database when you've copied the
files to the new server.
Please keep in mind that your database will be unavailable when you run
sp_detach_Db and then until you attach it again with sp_attach_db.
You should also keep in mind, that after you've run the sp_detach_db you
haven't got a working database - not even the original. By this I mean
that if something goes wrong during the detach and the database file get
corrupted, you have lost your database. I know that it's not very likely
that it fails and if it eventually does, you might be able to recover it
in some way, but it's a potential risk.
If you go the backup route, you'll still have your original database
even though your backup doesn't work, so you can always create a new backup.
HTH
Regards
Steensql

Backing up .mdf and .ldf files

I want to copy and paste .mdf and .ldf from our SQL server.
Am I correct in saying that the mdf and ldf files are locked into th SQL
databases and require the files to be detached via Enterprise Manager (for
SQL2000)?
In which case, how do I copy the .mdf and .ldf files to another HDD?
Is there a Utility I can use or another way?
Please help.
skcLookup sp_detach_db ,sp_attach_db in the BOL.
You can stop your services and copy/paste the files. I prefer
BACKUP/RESTORE operations
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:D6D5728B-1FEC-44AD-80DA-D93293D8CA4B@.microsoft.com...
>I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc|||Skc wrote:
> I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc
Hi
Why do you want to copy the files rather than using Backup/Restore? If
you want to copy the database files, you'll have to detach them first
using sp_detach_db. You can now copy them as regular files and you can
then use sp_attach_Db to create the database when you've copied the
files to the new server.
Please keep in mind that your database will be unavailable when you run
sp_detach_Db and then until you attach it again with sp_attach_db.
You should also keep in mind, that after you've run the sp_detach_db you
haven't got a working database - not even the original. By this I mean
that if something goes wrong during the detach and the database file get
corrupted, you have lost your database. I know that it's not very likely
that it fails and if it eventually does, you might be able to recover it
in some way, but it's a potential risk.
If you go the backup route, you'll still have your original database
even though your backup doesn't work, so you can always create a new backup.
HTH
Regards
Steen

Monday, March 19, 2012

Back Up Strategy

Would like to take your valuable views on ,What’s the best strategy for
Database back up and files back up:-
I have few options:-
? Using Database Maintenance plan
? Using State Store
? On SQL Server, using Differential on daily & Complete back on w

end..
? Other options :-
Thanks...Hi,
Thanks for your post!
My understanding of your issue is:
You wanted the best strategy for SQL Server database backup.
If I have misunderstood, please let me know.
Generally, different situations require different backup strategies.
It's difficult to say which one is the best.
You might back up your database frequently if your system is in an online
transaction processing (OLTP) environment.
You might back up your database less frequently if your system has little
activity or is used primarily for decision support.
You should schedule backups when SQL Server is not in the process of
being heavily updated.
After you determine your backup strategy, you can automate the process by
using the Database Maintenance Plan Wizard.
Also, you may refer to:
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_bkprst_63eh.asp
http://www.microsoft.com/technet/pr...n/sqlbackuprest
.mspx
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_bkprst_7drn.asp
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w

to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Back up on remote computer.

We have 5 computer (peer-to-peer).
SQL Serve is intalled on one machine. I want to do the
backup (put the backup files) on remote machine but it
doesn't show the network drives. It just shows the local
drives.
How can I do the backup on remote machine?HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
> We have 5 computer (peer-to-peer).
> SQL Serve is intalled on one machine. I want to do the
> backup (put the backup files) on remote machine but it
> doesn't show the network drives. It just shows the local
> drives.
> How can I do the backup on remote machine?|||Backup to the fully-qualified UNC name rather than a mapped drive letter.
For example: \\machine_name\share\backups. The mapped drives visible to you
on your machine won't necessarily be available to SQL Server.
--
David Portas
SQL Server MVP
--|||Good one, Geoff,. Short and straight to the point.
(The MS disclaimer is probably more text than the actual KB. :-) )
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eZ9$YLBoEHA.2096@.TK2MSFTNGP15.phx.gbl...
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> <anonymous@.discussions.microsoft.com> wrote in message
> news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
>> We have 5 computer (peer-to-peer).
>> SQL Serve is intalled on one machine. I want to do the
>> backup (put the backup files) on remote machine but it
>> doesn't show the network drives. It just shows the local
>> drives.
>> How can I do the backup on remote machine?
>|||The MSDN Article you mentioned/wrote says:
"1) The SQL Server service must be running under a domain-
level account."
My questions are:
(1) How do I know whether my SQL Server service is running
under a domain-level account or not?
(2) All our computers are in a Workgroup. What steps
should I take to start the SQL Server service to run under
domain-level account.
Our SQL Server is Standard Edition and version is 2000
SP3a.
>--Original Message--
>HowTo: Backup to UNC name using Database Maintenance
Wizard
>http://support.microsoft.com/?kbid=555128
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
><anonymous@.discussions.microsoft.com> wrote in message
>news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
>> We have 5 computer (peer-to-peer).
>> SQL Serve is intalled on one machine. I want to do the
>> backup (put the backup files) on remote machine but it
>> doesn't show the network drives. It just shows the local
>> drives.
>> How can I do the backup on remote machine?
>
>.
>|||Comments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:346801c4a020$e318d6b0$a501280a@.phx.gbl...
> The MSDN Article you mentioned/wrote says:
> "1) The SQL Server service must be running under a domain-
> level account."
> My questions are:
> (1) How do I know whether my SQL Server service is running
> under a domain-level account or not?
>
Look under the Services applet in Control Panel for the SQL Server service
(MSSQLSERVER) or Enterprise manager Properties page Security tab. A
domain-level account is one that authenticates as Domain_Name\LoginName
> (2) All our computers are in a Workgroup. What steps
> should I take to start the SQL Server service to run under
> domain-level account.
I haven't tried this in a workgroup. It might work if you have a login and
password that is the same on both servers that you run the SQL service
under. The other option is to create a domain.
> Our SQL Server is Standard Edition and version is 2000
> SP3a.
>
> >--Original Message--
> >HowTo: Backup to UNC name using Database Maintenance
> Wizard
> >http://support.microsoft.com/?kbid=555128
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
> >> We have 5 computer (peer-to-peer).
> >>
> >> SQL Serve is intalled on one machine. I want to do the
> >> backup (put the backup files) on remote machine but it
> >> doesn't show the network drives. It just shows the local
> >> drives.
> >>
> >> How can I do the backup on remote machine?
> >
> >
> >.
> >|||Thanks. There seemed to be a need for something to explain how to do this.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uYgKQsBoEHA.3460@.tk2msftngp13.phx.gbl...
> Good one, Geoff,. Short and straight to the point.
> (The MS disclaimer is probably more text than the actual KB. :-) )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eZ9$YLBoEHA.2096@.TK2MSFTNGP15.phx.gbl...
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> > http://support.microsoft.com/?kbid=555128
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > <anonymous@.discussions.microsoft.com> wrote in message
> > news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
> >> We have 5 computer (peer-to-peer).
> >>
> >> SQL Serve is intalled on one machine. I want to do the
> >> backup (put the backup files) on remote machine but it
> >> doesn't show the network drives. It just shows the local
> >> drives.
> >>
> >> How can I do the backup on remote machine?
> >
> >
>|||We have 5 machines. First machine Windows 2000 Server (SQL
Server 2000 Standard is installed here). Rest of the
machines have Windows 2000 Pro.
Keep the above scenario in mind, could you explain me what
you wrote earlier:
>It might work if you have a login and password that is
> the same on both servers that you run the SQL service
>under. The other option is to create a domain.|||OK. Create a login on the SQL host computer. Put it in the local
administrators group on that machine. Change the SQL Service to run under
that login. Create a login on the backup target machine with the exact same
username and password. Grant that login full control over the file share
and the NTFS folder you want to back up to. No guarantees, but this may
work in a workgroup.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:426701c4a023$dc891f50$a301280a@.phx.gbl...
> We have 5 machines. First machine Windows 2000 Server (SQL
> Server 2000 Standard is installed here). Rest of the
> machines have Windows 2000 Pro.
> Keep the above scenario in mind, could you explain me what
> you wrote earlier:
> >It might work if you have a login and password that is
> > the same on both servers that you run the SQL service
> >under. The other option is to create a domain.
>|||On Windows 2000 Server Machine the SQLServer Services are
running under Administrator. Also on the other computer
(windows 2000 professional) login is administrator (and
obviously has all rights on the shared folder).
This doesn't work when I try to backup on that shared
folder. The error says that location of the (backup) file
cannot be verified..do u want to continue...and when I
say YES then after a couple of seconds the backup process
stops."
SO does this CONCLUDE that this is not possible in
Workgroup scenario?
Thank you.
>--Original Message--
>OK. Create a login on the SQL host computer. Put it in
the local
>administrators group on that machine. Change the SQL
Service to run under
>that login. Create a login on the backup target machine
with the exact same
>username and password. Grant that login full control
over the file share
>and the NTFS folder you want to back up to. No
guarantees, but this may
>work in a workgroup.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
><anonymous@.discussions.microsoft.com> wrote in message
>news:426701c4a023$dc891f50$a301280a@.phx.gbl...
>> We have 5 machines. First machine Windows 2000 Server
(SQL
>> Server 2000 Standard is installed here). Rest of the
>> machines have Windows 2000 Pro.
>> Keep the above scenario in mind, could you explain me
what
>> you wrote earlier:
>> >It might work if you have a login and password that is
>> > the same on both servers that you run the SQL service
>> >under. The other option is to create a domain.
>
>.
>|||Do the Administrator account have the same password on the two machines?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
> On Windows 2000 Server Machine the SQLServer Services are
> running under Administrator. Also on the other computer
> (windows 2000 professional) login is administrator (and
> obviously has all rights on the shared folder).
> This doesn't work when I try to backup on that shared
> folder. The error says that location of the (backup) file
> cannot be verified..do u want to continue...and when I
> say YES then after a couple of seconds the backup process
> stops."
> SO does this CONCLUDE that this is not possible in
> Workgroup scenario?
> Thank you.
> >--Original Message--
> >OK. Create a login on the SQL host computer. Put it in
> the local
> >administrators group on that machine. Change the SQL
> Service to run under
> >that login. Create a login on the backup target machine
> with the exact same
> >username and password. Grant that login full control
> over the file share
> >and the NTFS folder you want to back up to. No
> guarantees, but this may
> >work in a workgroup.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
> >> We have 5 machines. First machine Windows 2000 Server
> (SQL
> >> Server 2000 Standard is installed here). Rest of the
> >> machines have Windows 2000 Pro.
> >>
> >> Keep the above scenario in mind, could you explain me
> what
> >> you wrote earlier:
> >>
> >> >It might work if you have a login and password that is
> >> > the same on both servers that you run the SQL service
> >> >under. The other option is to create a domain.
> >>
> >
> >
> >.
> >|||Yes.
>--Original Message--
>Do the Administrator account have the same password on
the two machines?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
>> On Windows 2000 Server Machine the SQLServer Services
are
>> running under Administrator. Also on the other computer
>> (windows 2000 professional) login is administrator (and
>> obviously has all rights on the shared folder).
>> This doesn't work when I try to backup on that shared
>> folder. The error says that location of the (backup)
file
>> cannot be verified..do u want to continue...and when I
>> say YES then after a couple of seconds the backup
process
>> stops."
>> SO does this CONCLUDE that this is not possible in
>> Workgroup scenario?
>> Thank you.
>> >--Original Message--
>> >OK. Create a login on the SQL host computer. Put it
in
>> the local
>> >administrators group on that machine. Change the SQL
>> Service to run under
>> >that login. Create a login on the backup target
machine
>> with the exact same
>> >username and password. Grant that login full control
>> over the file share
>> >and the NTFS folder you want to back up to. No
>> guarantees, but this may
>> >work in a workgroup.
>> >
>> >--
>> >Geoff N. Hiten
>> >Microsoft SQL Server MVP
>> >Senior Database Administrator
>> >Careerbuilder.com
>> >
>> >I support the Professional Association for SQL Server
>> >www.sqlpass.org
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
>> >> We have 5 machines. First machine Windows 2000
Server
>> (SQL
>> >> Server 2000 Standard is installed here). Rest of the
>> >> machines have Windows 2000 Pro.
>> >>
>> >> Keep the above scenario in mind, could you explain
me
>> what
>> >> you wrote earlier:
>> >>
>> >> >It might work if you have a login and password
that is
>> >> > the same on both servers that you run the SQL
service
>> >> >under. The other option is to create a domain.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Are you using an UNC path or a mapped drive?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:12fb01c4a074$c5073190$a601280a@.phx.gbl...
> Yes.
> >--Original Message--
> >Do the Administrator account have the same password on
> the two machines?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
> >> On Windows 2000 Server Machine the SQLServer Services
> are
> >> running under Administrator. Also on the other computer
> >> (windows 2000 professional) login is administrator (and
> >> obviously has all rights on the shared folder).
> >>
> >> This doesn't work when I try to backup on that shared
> >> folder. The error says that location of the (backup)
> file
> >> cannot be verified..do u want to continue...and when I
> >> say YES then after a couple of seconds the backup
> process
> >> stops."
> >>
> >> SO does this CONCLUDE that this is not possible in
> >> Workgroup scenario?
> >>
> >> Thank you.
> >>
> >> >--Original Message--
> >> >OK. Create a login on the SQL host computer. Put it
> in
> >> the local
> >> >administrators group on that machine. Change the SQL
> >> Service to run under
> >> >that login. Create a login on the backup target
> machine
> >> with the exact same
> >> >username and password. Grant that login full control
> >> over the file share
> >> >and the NTFS folder you want to back up to. No
> >> guarantees, but this may
> >> >work in a workgroup.
> >> >
> >> >--
> >> >Geoff N. Hiten
> >> >Microsoft SQL Server MVP
> >> >Senior Database Administrator
> >> >Careerbuilder.com
> >> >
> >> >I support the Professional Association for SQL Server
> >> >www.sqlpass.org
> >> >
> >> ><anonymous@.discussions.microsoft.com> wrote in message
> >> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
> >> >> We have 5 machines. First machine Windows 2000
> Server
> >> (SQL
> >> >> Server 2000 Standard is installed here). Rest of the
> >> >> machines have Windows 2000 Pro.
> >> >>
> >> >> Keep the above scenario in mind, could you explain
> me
> >> what
> >> >> you wrote earlier:
> >> >>
> >> >> >It might work if you have a login and password
> that is
> >> >> > the same on both servers that you run the SQL
> service
> >> >> >under. The other option is to create a domain.
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||I am using UNC path.
>--Original Message--
>Are you using an UNC path or a mapped drive?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:12fb01c4a074$c5073190$a601280a@.phx.gbl...
>> Yes.
>> >--Original Message--
>> >Do the Administrator account have the same password on
>> the two machines?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
>> >> On Windows 2000 Server Machine the SQLServer
Services
>> are
>> >> running under Administrator. Also on the other
computer
>> >> (windows 2000 professional) login is administrator
(and
>> >> obviously has all rights on the shared folder).
>> >>
>> >> This doesn't work when I try to backup on that
shared
>> >> folder. The error says that location of the (backup)
>> file
>> >> cannot be verified..do u want to continue...and
when I
>> >> say YES then after a couple of seconds the backup
>> process
>> >> stops."
>> >>
>> >> SO does this CONCLUDE that this is not possible in
>> >> Workgroup scenario?
>> >>
>> >> Thank you.
>> >>
>> >> >--Original Message--
>> >> >OK. Create a login on the SQL host computer. Put
it
>> in
>> >> the local
>> >> >administrators group on that machine. Change the
SQL
>> >> Service to run under
>> >> >that login. Create a login on the backup target
>> machine
>> >> with the exact same
>> >> >username and password. Grant that login full
control
>> >> over the file share
>> >> >and the NTFS folder you want to back up to. No
>> >> guarantees, but this may
>> >> >work in a workgroup.
>> >> >
>> >> >--
>> >> >Geoff N. Hiten
>> >> >Microsoft SQL Server MVP
>> >> >Senior Database Administrator
>> >> >Careerbuilder.com
>> >> >
>> >> >I support the Professional Association for SQL
Server
>> >> >www.sqlpass.org
>> >> >
>> >> ><anonymous@.discussions.microsoft.com> wrote in
message
>> >> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
>> >> >> We have 5 machines. First machine Windows 2000
>> Server
>> >> (SQL
>> >> >> Server 2000 Standard is installed here). Rest of
the
>> >> >> machines have Windows 2000 Pro.
>> >> >>
>> >> >> Keep the above scenario in mind, could you
explain
>> me
>> >> what
>> >> >> you wrote earlier:
>> >> >>
>> >> >> >It might work if you have a login and password
>> that is
>> >> >> > the same on both servers that you run the SQL
>> service
>> >> >> >under. The other option is to create a domain.
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||That is strange. I just did exactly that. I have a workgroup, and SQL Server is running under a
service account that also exists on the other machine, having the same password on both machines. I
executed below command, and it worked perfectly:
BACKUP DATABASE pubs TO DISK = '\\TibMedia\C\pubs.bak'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:3b8001c4a0ad$30389f20$a501280a@.phx.gbl...
>I am using UNC path.
>>--Original Message--
>>Are you using an UNC path or a mapped drive?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:12fb01c4a074$c5073190$a601280a@.phx.gbl...
>> Yes.
>> >--Original Message--
>> >Do the Administrator account have the same password on
>> the two machines?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
>> >> On Windows 2000 Server Machine the SQLServer
> Services
>> are
>> >> running under Administrator. Also on the other
> computer
>> >> (windows 2000 professional) login is administrator
> (and
>> >> obviously has all rights on the shared folder).
>> >>
>> >> This doesn't work when I try to backup on that
> shared
>> >> folder. The error says that location of the (backup)
>> file
>> >> cannot be verified..do u want to continue...and
> when I
>> >> say YES then after a couple of seconds the backup
>> process
>> >> stops."
>> >>
>> >> SO does this CONCLUDE that this is not possible in
>> >> Workgroup scenario?
>> >>
>> >> Thank you.
>> >>
>> >> >--Original Message--
>> >> >OK. Create a login on the SQL host computer. Put
> it
>> in
>> >> the local
>> >> >administrators group on that machine. Change the
> SQL
>> >> Service to run under
>> >> >that login. Create a login on the backup target
>> machine
>> >> with the exact same
>> >> >username and password. Grant that login full
> control
>> >> over the file share
>> >> >and the NTFS folder you want to back up to. No
>> >> guarantees, but this may
>> >> >work in a workgroup.
>> >> >
>> >> >--
>> >> >Geoff N. Hiten
>> >> >Microsoft SQL Server MVP
>> >> >Senior Database Administrator
>> >> >Careerbuilder.com
>> >> >
>> >> >I support the Professional Association for SQL
> Server
>> >> >www.sqlpass.org
>> >> >
>> >> ><anonymous@.discussions.microsoft.com> wrote in
> message
>> >> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
>> >> >> We have 5 machines. First machine Windows 2000
>> Server
>> >> (SQL
>> >> >> Server 2000 Standard is installed here). Rest of
> the
>> >> >> machines have Windows 2000 Pro.
>> >> >>
>> >> >> Keep the above scenario in mind, could you
> explain
>> me
>> >> what
>> >> >> you wrote earlier:
>> >> >>
>> >> >> >It might work if you have a login and password
>> that is
>> >> >> > the same on both servers that you run the SQL
>> service
>> >> >> >under. The other option is to create a domain.
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>>
>>.

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK = ''c:\backups\Tuesday_databasebackup.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 = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK = ''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
goApparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
--
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegroups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK => ''c:\backups\Tuesday_databasebackup.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 = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK => ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK =
''c:\backups\Tuesday_databasebackup.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 = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK =
''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
go
Apparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.
|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegro ups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK =
> ''c:\backups\Tuesday_databasebackup.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 = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK =
> ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK =
''c:\backups\Tuesday_databasebackup.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 = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK =
''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
goApparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegroups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK =
> ''c:\backups\Tuesday_databasebackup.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 = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK =
> ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

Saturday, February 25, 2012

Avoiding Setting xp_cmdshell in SQL 2005

Would anyone know, how to work around a potential problem of not using xp_cmdshell.

For our users, We are using xp_cmdshell to look up the xml files in a particular folder and process them into Database. xp_cmdshell is used to read the contents of the file and build up the string for the stored procedure parameter input.

In SQL 2000, xp_cmdshell was set by default. in SQL 2005 not set by default and some of my users doesn't want to set it suggesting security concerns.

Thanks.

You could use OPENROWSET with BULK option for upload xml file as sigle blob into one database field. For example:

Code Snippet

INSERT INTO myTable(Document)
SELECT * FROM OPENROWSET(BULK N'C:\data.xml', SINGLE_BLOB) AS Document

Then you could use xml data type functions for split data. See xml.value, xml.nodes in BOL

Friday, February 10, 2012

Autonumber question

Does anyone know how to reset an autonumber field in SQLExpress 2005?

In MSAccess it was a simple "compress" of the database. My .mdf files are getting very large and I would like to shrink them down for easier mobility. A blank database takes up aprox. 30 mbs. Most all of the autonumber fields are currently in the 200,000 - 800,000 range. When I compressed my Access database prior to transfering to SQLExpress it significantly decreased the size of the empty database file.

Thank you for any help you can provide.

hi,

please have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=769553&SiteID=1

regards

Tuesday, March 27, 2012

Backing Up My RS Server Recommendation

Sunday, March 25, 2012

Backing up log files

Thursday, March 22, 2012

Backing up a database that was been moved from another server

Backing up a database that was been moved from another server

Backing up a database that was been moved from another server

Tuesday, March 20, 2012

Backing up .mdf and .ldf files

Backing up .mdf and .ldf files

Backing up .mdf and .ldf files

Monday, March 19, 2012

Back Up Strategy

Back up on remote computer.

Back up on remote computer.

back up job not backing up properly?

back up job not backing up properly?

back up job not backing up properly?

Saturday, February 25, 2012

Avoiding Setting xp_cmdshell in SQL 2005

Friday, February 10, 2012

Autonumber question

Autonumber field

Blog Archive

About Me