Hello,
I would to create a backup file dynamically (the filename will contain
the hostname of the DB).
any idea how can I use a variable for that?
(a variable is easy to use for a regular database backup, but not for
a master key).
I wrote on the comment the error I get.
thanks, Yaniv
use master;
declare @.tmpStr varchar(200)
set @.tmpStr = 'c:\temp\DB_service_2.key'
-- working
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
ENCRYPTION BY
PASSWORD = 't1t1t1t1t1'
-- error: Incorrect syntax near '@.tmpStr'
BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD =
't1t1t1t1t1'
goYou can use dynamic SQL. Built the BACKUP command in a TSQL varchar variable
, then execute it:
EXEC(@.sql)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yaniv.harpaz@.gmail.com> wrote in message
news:1170238336.716966.152710@.p10g2000cwp.googlegroups.com...
> Hello,
> I would to create a backup file dynamically (the filename will contain
> the hostname of the DB).
> any idea how can I use a variable for that?
> (a variable is easy to use for a regular database backup, but not for
> a master key).
> I wrote on the comment the error I get.
> thanks, Yaniv
> use master;
> declare @.tmpStr varchar(200)
> set @.tmpStr = 'c:\temp\DB_service_2.key'
> -- working
> BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
> ENCRYPTION BY
> PASSWORD = 't1t1t1t1t1'
> -- error: Incorrect syntax near '@.tmpStr'
> BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD =
> 't1t1t1t1t1'
> go
>
Showing posts with label service. Show all posts
Showing posts with label service. Show all posts
Thursday, March 22, 2012
BACKUP SERVICE MASTER KEY TO FILE
Hello,
I would to create a backup file dynamically (the filename will contain
the hostname of the DB).
any idea how can I use a variable for that?
(a variable is easy to use for a regular database backup, but not for
a master key).
I wrote on the comment the error I get.
thanks, Yaniv
use master;
declare @.tmpStr varchar(200)
set @.tmpStr = 'c:\temp\DB_service_2.key'
-- working
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
ENCRYPTION BY
PASSWORD = 't1t1t1t1t1'
-- error: Incorrect syntax near '@.tmpStr'
BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD = 't1t1t1t1t1'
goYou can use dynamic SQL. Built the BACKUP command in a TSQL varchar variable, then execute it:
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yaniv.harpaz@.gmail.com> wrote in message
news:1170238336.716966.152710@.p10g2000cwp.googlegroups.com...
> Hello,
> I would to create a backup file dynamically (the filename will contain
> the hostname of the DB).
> any idea how can I use a variable for that?
> (a variable is easy to use for a regular database backup, but not for
> a master key).
> I wrote on the comment the error I get.
> thanks, Yaniv
> use master;
> declare @.tmpStr varchar(200)
> set @.tmpStr = 'c:\temp\DB_service_2.key'
> -- working
> BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
> ENCRYPTION BY
> PASSWORD = 't1t1t1t1t1'
> -- error: Incorrect syntax near '@.tmpStr'
> BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD => 't1t1t1t1t1'
> go
>
I would to create a backup file dynamically (the filename will contain
the hostname of the DB).
any idea how can I use a variable for that?
(a variable is easy to use for a regular database backup, but not for
a master key).
I wrote on the comment the error I get.
thanks, Yaniv
use master;
declare @.tmpStr varchar(200)
set @.tmpStr = 'c:\temp\DB_service_2.key'
-- working
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
ENCRYPTION BY
PASSWORD = 't1t1t1t1t1'
-- error: Incorrect syntax near '@.tmpStr'
BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD = 't1t1t1t1t1'
goYou can use dynamic SQL. Built the BACKUP command in a TSQL varchar variable, then execute it:
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yaniv.harpaz@.gmail.com> wrote in message
news:1170238336.716966.152710@.p10g2000cwp.googlegroups.com...
> Hello,
> I would to create a backup file dynamically (the filename will contain
> the hostname of the DB).
> any idea how can I use a variable for that?
> (a variable is easy to use for a regular database backup, but not for
> a master key).
> I wrote on the comment the error I get.
> thanks, Yaniv
> use master;
> declare @.tmpStr varchar(200)
> set @.tmpStr = 'c:\temp\DB_service_2.key'
> -- working
> BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
> ENCRYPTION BY
> PASSWORD = 't1t1t1t1t1'
> -- error: Incorrect syntax near '@.tmpStr'
> BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD => 't1t1t1t1t1'
> go
>
Tuesday, March 20, 2012
Backup reporting false success
I am experiencing the same behavior as below (see snip)on
my SQL Server 7.0
running on NT (lastest service packs and patched on
both.) I too have witnessed the BAK file created and
then
deleted. I do not have the maintance plan configured to
deleting old .BAK files. I do receive this error message
when I create a backup from the "backup" option (not the
database maintenance plan.) This is the message I get:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
percent backed up. [SQLSTATE 01000] (Message 3211)
ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 30 percent
backed up. [SQLSTATE 01000] (Message 3211). The step
failed.
NOTE: I am backing up to the server harddrive(NO NETWORK
INVOLVED!!!)with 20 gigs free. This database is only
199mb in size. I suspect the "general network error" is
bogus.
Please help!
snip from other person with same issue
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases >>
(the
largest being around 500 megs). 3 of the databases
backup
>>fine but 1 database will not take a full backup or a
transaction log backup from the maintenance plan. The
job
runs and succeeds but the file is not where it should be
(nor anywhere else).
The development server with the same configuration does
not have this same problem, and it takes about 7 seconds
to backup the database in question.
I have monitored the directory that the file should be
created in while the job is running, and the file does
create and stays there for the 5 to 7 seconds that the
job
is running and then it dissappears.
I have also used File Monitor from sysinternals and
monitored the system while a backup is being run and
sqlservr.exe and sqlmaint.exe are the only things that
touch the .BAK file. Sqlservr.exe reads and writes to
the .BAK file numerous times and the last thing that
happens is sqlservr.exe opens the .BAK file, then Deletes
the .BAK file, and then closes the .BAK file. No other
applications besides sqlservr.exe and sqlmaint.exe touch
the .BAK file. The index numbers in File Monitor do not
skip any numbers.
Here are the lines from sysinternals File Monitor where
the backup is deleted:
1646 6:32:56 PM sqlservr.exe:3688 OPEN
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS Options: Open Access: All
1647 6:32:56 PM sqlservr.exe:3688 DELETE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
1648 6:32:56 PM sqlservr.exe:3688 CLOSE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
The error message in the errorlog is:
BACKUP failed to complete the command BACKUP LOG [t_prod]
TO DISK = N'E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
Disk space is not an issue as there is over 60 gigs free
and all the databases together barely total a 1 gig.
Backups from a script that run through a job (without the
maint plan wizard) succeed about 75% of the time but
about
25% of the time the job fails with the same errors as the
maint plan fails with. I'm still trying to get File
Monitor running at the time a backup from a script fails,
but haven't been luckly enough to catch it at the right
time yet.
Any information or suggestions to resolve this problem
are
greatly appreaciated as I've been trying to figure this
out for too long. If there is any other information that
would be useful to post, please let me know.
Thanks,
Trent.
.Just a thought... Do you have disk compression turned on?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kathy" <rnurse@.educ.state.ak.us> wrote in message news:799c01c3b458$0ebdd730$a601280a@.phx.gbl...
> I am experiencing the same behavior as below (see snip)on
> my SQL Server 7.0
> running on NT (lastest service packs and patched on
> both.) I too have witnessed the BAK file created and
> then
> deleted. I do not have the maintance plan configured to
> deleting old .BAK files. I do receive this error message
> when I create a backup from the "backup" option (not the
> database maintenance plan.) This is the message I get:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
> percent backed up. [SQLSTATE 01000] (Message 3211)
> ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
> 258) General network error. Check your network
> documentation. [SQLSTATE 08S01] (Error 11) 30 percent
> backed up. [SQLSTATE 01000] (Message 3211). The step
> failed.
> NOTE: I am backing up to the server harddrive(NO NETWORK
> INVOLVED!!!)with 20 gigs free. This database is only
> 199mb in size. I suspect the "general network error" is
> bogus.
> Please help!
> snip from other person with same issue
> >>I have a server with Windows 2000 SP4+hotfixes, SQL
> >>Server
> >>2000 SE SP3A+hotfix. The server has 4 user databases >>
> (the
> largest being around 500 megs). 3 of the databases
> backup
> >>fine but 1 database will not take a full backup or a
> transaction log backup from the maintenance plan. The
> job
> runs and succeeds but the file is not where it should be
> (nor anywhere else).
> The development server with the same configuration does
> not have this same problem, and it takes about 7 seconds
> to backup the database in question.
> I have monitored the directory that the file should be
> created in while the job is running, and the file does
> create and stays there for the 5 to 7 seconds that the
> job
> is running and then it dissappears.
> I have also used File Monitor from sysinternals and
> monitored the system while a backup is being run and
> sqlservr.exe and sqlmaint.exe are the only things that
> touch the .BAK file. Sqlservr.exe reads and writes to
> the .BAK file numerous times and the last thing that
> happens is sqlservr.exe opens the .BAK file, then Deletes
> the .BAK file, and then closes the .BAK file. No other
> applications besides sqlservr.exe and sqlmaint.exe touch
> the .BAK file. The index numbers in File Monitor do not
> skip any numbers.
> Here are the lines from sysinternals File Monitor where
> the backup is deleted:
> 1646 6:32:56 PM sqlservr.exe:3688 OPEN
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS Options: Open Access: All
> 1647 6:32:56 PM sqlservr.exe:3688 DELETE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
> 1648 6:32:56 PM sqlservr.exe:3688 CLOSE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>
> The error message in the errorlog is:
> BACKUP failed to complete the command BACKUP LOG [t_prod]
> TO DISK = N'E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
> Disk space is not an issue as there is over 60 gigs free
> and all the databases together barely total a 1 gig.
> Backups from a script that run through a job (without the
> maint plan wizard) succeed about 75% of the time but
> about
> 25% of the time the job fails with the same errors as the
> maint plan fails with. I'm still trying to get File
> Monitor running at the time a backup from a script fails,
> but haven't been luckly enough to catch it at the right
> time yet.
> Any information or suggestions to resolve this problem
> are
> greatly appreaciated as I've been trying to figure this
> out for too long. If there is any other information that
> would be useful to post, please let me know.
> Thanks,
> Trent.
> .
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I should give some explanation to the phenomenon you described in the snip.
The backup process in the SQL Server is an automatic process, which means
when backup failed to complete, all the backup process will rollback. This
is why the customer you snipped from used File Monitor and found that "does
create and stays there for the 5 to 7 seconds that the job is running and
then it disappears." As he mentioned later "Backups from a script that run
through a job (without the maintenance plan wizard) succeed about 75% of
the time but about 25% of the time the job fails with the same errors as
the maintenance plan fails with".
For the error message you mentioned, I need more information for analysis.
First, is this error message encountered for all the databases you have, or
just one database when using "Backup" option?
Second, besides using the "backup" option, do you meet the same error
message when using T-SQL or job for back up? You can try the T-SQL code
like this:
use master
exec sp_addumpdevice 'disk','northwindback', 'c:\northwind_back.bak'
backup database northwind to northwindback
go
restore database northwind from northwindback
go
If you do not encounter this error, then please provide information for
every step when using "backup" option to check if anywhere in this process
might involve the network device
If you meet the error message again, please collect information from
SQLDIAG, NT application event logs, system event logs and dbcc checkdb out
put and post here for our further analysis.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for responding!!! :-)
The first point you made really made sense to me. I knew
transactions rolled back but never thought about a backup
job rolling back. I did as you suggested. I created a
backup device and backup a database (and restored it)
using T-SQL code. Of course this worked, because it is
not a job scheduled using the backup wizard interface. I
can always manually backup a database by right clicking,
choicing "All Task" and "Backup Database." But as soon
as I ran it as a scheduled job it will failed, or SQL
would reported it as suceeding with no BAK file.
So, I decided to create a backup job manually. It
worked!!! Then I scheduled it through the job interfaced
and it ran as scheduled.
To clarify this to you:
Backup Job FAILS when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Right click on the new backup device (otg_backupdevice)
and select "Backup a Database"
The SQL Server backup wizard window appears, select the
database (otg) to backup, add the backup device
(otg_backupdevice) and schedule the backup to occur once
a day at 12:00 A.M.
Then ran the job from the jobs window
The job fails.
Backup Job Succeeds when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Go to the job window
Right-click and select new job
Name the job - OTG backup to device
Click on the "Steps" tab and click on "New Step"
Select OTG as the database
In the command window I enter "backup database otg to
otg_backupdevice
Click on the "Schedule" tab and schedule it to backup at
10:00 am
The job suceeds
I suspect there is a problem with the backup wizard'
Thank you for your help. I still wonder why the backups
will not work through maintenance plans or through the
backup wizard.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||I tested restoring a database and the system reports the
database is restore sucessfully but it does not. The
sysdatabase does not show an entry for the restored
database. When I close Enterprise Manager and reopen the
database is gone. The mdf file is still in the data
directory. What is wrong with my sql server.
I can restore the database from the same file on my SQL
2000 server but it fail on every one of the SQL 7.0
servers. The production 7.0 SQL server and the 7.0 SQL
server on my laptop computer.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Thank you for your detailed information and it is helpful for us to
analysis.
As you said, when execute the TSQL and JOB schedule, the backup process
will complete fine while when using backup option, it failed, right? If I
misunderstood the information you provided, please feel free to let me know.
As using the "backup" option, because it add many options when carried out
the backup, so please carry out it step by step as I mentioned below:
1) Right clicking, choose "All Task" and "Backup Database."
2) This is very important step! Make sure in the destination, there should
be only one device (When you add the device, there could be some other
devices in it), remove other devices from the destination. If not, you will
encounter the errors.
3) Click "OK" after you assigned the schedule time (You can also run it
immediately by click "ok" to test if this will work first)
Try the above steps and pay attention to the second one, only one
destination could be appeared in the "destination".
I hope this will solve the problem and if there is any problem, feel free
to post message here and I am ready to help! By the way, happy
thanks-giving day to you and your family!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
my SQL Server 7.0
running on NT (lastest service packs and patched on
both.) I too have witnessed the BAK file created and
then
deleted. I do not have the maintance plan configured to
deleting old .BAK files. I do receive this error message
when I create a backup from the "backup" option (not the
database maintenance plan.) This is the message I get:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
percent backed up. [SQLSTATE 01000] (Message 3211)
ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 30 percent
backed up. [SQLSTATE 01000] (Message 3211). The step
failed.
NOTE: I am backing up to the server harddrive(NO NETWORK
INVOLVED!!!)with 20 gigs free. This database is only
199mb in size. I suspect the "general network error" is
bogus.
Please help!
snip from other person with same issue
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases >>
(the
largest being around 500 megs). 3 of the databases
backup
>>fine but 1 database will not take a full backup or a
transaction log backup from the maintenance plan. The
job
runs and succeeds but the file is not where it should be
(nor anywhere else).
The development server with the same configuration does
not have this same problem, and it takes about 7 seconds
to backup the database in question.
I have monitored the directory that the file should be
created in while the job is running, and the file does
create and stays there for the 5 to 7 seconds that the
job
is running and then it dissappears.
I have also used File Monitor from sysinternals and
monitored the system while a backup is being run and
sqlservr.exe and sqlmaint.exe are the only things that
touch the .BAK file. Sqlservr.exe reads and writes to
the .BAK file numerous times and the last thing that
happens is sqlservr.exe opens the .BAK file, then Deletes
the .BAK file, and then closes the .BAK file. No other
applications besides sqlservr.exe and sqlmaint.exe touch
the .BAK file. The index numbers in File Monitor do not
skip any numbers.
Here are the lines from sysinternals File Monitor where
the backup is deleted:
1646 6:32:56 PM sqlservr.exe:3688 OPEN
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS Options: Open Access: All
1647 6:32:56 PM sqlservr.exe:3688 DELETE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
1648 6:32:56 PM sqlservr.exe:3688 CLOSE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
The error message in the errorlog is:
BACKUP failed to complete the command BACKUP LOG [t_prod]
TO DISK = N'E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
Disk space is not an issue as there is over 60 gigs free
and all the databases together barely total a 1 gig.
Backups from a script that run through a job (without the
maint plan wizard) succeed about 75% of the time but
about
25% of the time the job fails with the same errors as the
maint plan fails with. I'm still trying to get File
Monitor running at the time a backup from a script fails,
but haven't been luckly enough to catch it at the right
time yet.
Any information or suggestions to resolve this problem
are
greatly appreaciated as I've been trying to figure this
out for too long. If there is any other information that
would be useful to post, please let me know.
Thanks,
Trent.
.Just a thought... Do you have disk compression turned on?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kathy" <rnurse@.educ.state.ak.us> wrote in message news:799c01c3b458$0ebdd730$a601280a@.phx.gbl...
> I am experiencing the same behavior as below (see snip)on
> my SQL Server 7.0
> running on NT (lastest service packs and patched on
> both.) I too have witnessed the BAK file created and
> then
> deleted. I do not have the maintance plan configured to
> deleting old .BAK files. I do receive this error message
> when I create a backup from the "backup" option (not the
> database maintenance plan.) This is the message I get:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
> percent backed up. [SQLSTATE 01000] (Message 3211)
> ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
> 258) General network error. Check your network
> documentation. [SQLSTATE 08S01] (Error 11) 30 percent
> backed up. [SQLSTATE 01000] (Message 3211). The step
> failed.
> NOTE: I am backing up to the server harddrive(NO NETWORK
> INVOLVED!!!)with 20 gigs free. This database is only
> 199mb in size. I suspect the "general network error" is
> bogus.
> Please help!
> snip from other person with same issue
> >>I have a server with Windows 2000 SP4+hotfixes, SQL
> >>Server
> >>2000 SE SP3A+hotfix. The server has 4 user databases >>
> (the
> largest being around 500 megs). 3 of the databases
> backup
> >>fine but 1 database will not take a full backup or a
> transaction log backup from the maintenance plan. The
> job
> runs and succeeds but the file is not where it should be
> (nor anywhere else).
> The development server with the same configuration does
> not have this same problem, and it takes about 7 seconds
> to backup the database in question.
> I have monitored the directory that the file should be
> created in while the job is running, and the file does
> create and stays there for the 5 to 7 seconds that the
> job
> is running and then it dissappears.
> I have also used File Monitor from sysinternals and
> monitored the system while a backup is being run and
> sqlservr.exe and sqlmaint.exe are the only things that
> touch the .BAK file. Sqlservr.exe reads and writes to
> the .BAK file numerous times and the last thing that
> happens is sqlservr.exe opens the .BAK file, then Deletes
> the .BAK file, and then closes the .BAK file. No other
> applications besides sqlservr.exe and sqlmaint.exe touch
> the .BAK file. The index numbers in File Monitor do not
> skip any numbers.
> Here are the lines from sysinternals File Monitor where
> the backup is deleted:
> 1646 6:32:56 PM sqlservr.exe:3688 OPEN
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS Options: Open Access: All
> 1647 6:32:56 PM sqlservr.exe:3688 DELETE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
> 1648 6:32:56 PM sqlservr.exe:3688 CLOSE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>
> The error message in the errorlog is:
> BACKUP failed to complete the command BACKUP LOG [t_prod]
> TO DISK = N'E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
> Disk space is not an issue as there is over 60 gigs free
> and all the databases together barely total a 1 gig.
> Backups from a script that run through a job (without the
> maint plan wizard) succeed about 75% of the time but
> about
> 25% of the time the job fails with the same errors as the
> maint plan fails with. I'm still trying to get File
> Monitor running at the time a backup from a script fails,
> but haven't been luckly enough to catch it at the right
> time yet.
> Any information or suggestions to resolve this problem
> are
> greatly appreaciated as I've been trying to figure this
> out for too long. If there is any other information that
> would be useful to post, please let me know.
> Thanks,
> Trent.
> .
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I should give some explanation to the phenomenon you described in the snip.
The backup process in the SQL Server is an automatic process, which means
when backup failed to complete, all the backup process will rollback. This
is why the customer you snipped from used File Monitor and found that "does
create and stays there for the 5 to 7 seconds that the job is running and
then it disappears." As he mentioned later "Backups from a script that run
through a job (without the maintenance plan wizard) succeed about 75% of
the time but about 25% of the time the job fails with the same errors as
the maintenance plan fails with".
For the error message you mentioned, I need more information for analysis.
First, is this error message encountered for all the databases you have, or
just one database when using "Backup" option?
Second, besides using the "backup" option, do you meet the same error
message when using T-SQL or job for back up? You can try the T-SQL code
like this:
use master
exec sp_addumpdevice 'disk','northwindback', 'c:\northwind_back.bak'
backup database northwind to northwindback
go
restore database northwind from northwindback
go
If you do not encounter this error, then please provide information for
every step when using "backup" option to check if anywhere in this process
might involve the network device
If you meet the error message again, please collect information from
SQLDIAG, NT application event logs, system event logs and dbcc checkdb out
put and post here for our further analysis.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for responding!!! :-)
The first point you made really made sense to me. I knew
transactions rolled back but never thought about a backup
job rolling back. I did as you suggested. I created a
backup device and backup a database (and restored it)
using T-SQL code. Of course this worked, because it is
not a job scheduled using the backup wizard interface. I
can always manually backup a database by right clicking,
choicing "All Task" and "Backup Database." But as soon
as I ran it as a scheduled job it will failed, or SQL
would reported it as suceeding with no BAK file.
So, I decided to create a backup job manually. It
worked!!! Then I scheduled it through the job interfaced
and it ran as scheduled.
To clarify this to you:
Backup Job FAILS when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Right click on the new backup device (otg_backupdevice)
and select "Backup a Database"
The SQL Server backup wizard window appears, select the
database (otg) to backup, add the backup device
(otg_backupdevice) and schedule the backup to occur once
a day at 12:00 A.M.
Then ran the job from the jobs window
The job fails.
Backup Job Succeeds when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Go to the job window
Right-click and select new job
Name the job - OTG backup to device
Click on the "Steps" tab and click on "New Step"
Select OTG as the database
In the command window I enter "backup database otg to
otg_backupdevice
Click on the "Schedule" tab and schedule it to backup at
10:00 am
The job suceeds
I suspect there is a problem with the backup wizard'
Thank you for your help. I still wonder why the backups
will not work through maintenance plans or through the
backup wizard.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||I tested restoring a database and the system reports the
database is restore sucessfully but it does not. The
sysdatabase does not show an entry for the restored
database. When I close Enterprise Manager and reopen the
database is gone. The mdf file is still in the data
directory. What is wrong with my sql server.
I can restore the database from the same file on my SQL
2000 server but it fail on every one of the SQL 7.0
servers. The production 7.0 SQL server and the 7.0 SQL
server on my laptop computer.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Thank you for your detailed information and it is helpful for us to
analysis.
As you said, when execute the TSQL and JOB schedule, the backup process
will complete fine while when using backup option, it failed, right? If I
misunderstood the information you provided, please feel free to let me know.
As using the "backup" option, because it add many options when carried out
the backup, so please carry out it step by step as I mentioned below:
1) Right clicking, choose "All Task" and "Backup Database."
2) This is very important step! Make sure in the destination, there should
be only one device (When you add the device, there could be some other
devices in it), remove other devices from the destination. If not, you will
encounter the errors.
3) Click "OK" after you assigned the schedule time (You can also run it
immediately by click "ok" to test if this will work first)
Try the above steps and pay attention to the second one, only one
destination could be appeared in the "destination".
I hope this will solve the problem and if there is any problem, feel free
to post message here and I am ready to help! By the way, happy
thanks-giving day to you and your family!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Backup Question For Newbie
We use SQL for a few minor applications, and currently, we just down the
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
Monday, March 19, 2012
Backup procedure
Hello there!
I have SQLServer 2000 running on Windows 2000 advanced server.
I want to schedule daily full backup.
Do I have to stop SQLServer service before it starts backup?
Or can users have full access to the database while it's doing the backup?
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance
I have SQLServer 2000 running on Windows 2000 advanced server.
I want to schedule daily full backup.
Do I have to stop SQLServer service before it starts backup?
Or can users have full access to the database while it's doing the backup?
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance
Backup procedure
Hello there
I have SQLServer 2000 running on Windows 2000 advanced server
I want to schedule daily full backup
Do I have to stop SQLServer service before it starts backup
Or can users have full access to the database while it's doing the backup
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance
I have SQLServer 2000 running on Windows 2000 advanced server
I want to schedule daily full backup
Do I have to stop SQLServer service before it starts backup
Or can users have full access to the database while it's doing the backup
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance
Subscribe to:
Posts (Atom)