Managing sql Log File: backup

Showing posts with label backup. Show all posts

Friday, March 30, 2012

Marked "IN LOAD" messages

I do restores on a daily basis. The restores are taken from DISK backups. I
am not using backup devices.
I am noticing all these errors in the log file:
Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
When I do the restore, I am using the gui (rt click on database, select
restore).
Sometimes I need to restore over an existing DB, other times I restore to a
new database.
Any ideas? I want to make sure I am doing the restores correctly and remove
these errorsIs it possible if I do not specify "Force over existing Database" that it can
cause this error?
"Kristen" wrote:
> I do restores on a daily basis. The restores are taken from DISK backups. I
> am not using backup devices.
> I am noticing all these errors in the log file:
> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
> When I do the restore, I am using the gui (rt click on database, select
> restore).
> Sometimes I need to restore over an existing DB, other times I restore to a
> new database.
> Any ideas? I want to make sure I am doing the restores correctly and remove
> these errors|||Try using the 'WITH RECOVERY' OPTION and issue the restore command via TSQL
and not the Gui.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
> Is it possible if I do not specify "Force over existing Database" that it
> can
> cause this error?
> "Kristen" wrote:
>> I do restores on a daily basis. The restores are taken from DISK
>> backups. I
>> am not using backup devices.
>> I am noticing all these errors in the log file:
>> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
>> When I do the restore, I am using the gui (rt click on database, select
>> restore).
>> Sometimes I need to restore over an existing DB, other times I restore to
>> a
>> new database.
>> Any ideas? I want to make sure I am doing the restores correctly and
>> remove
>> these errors|||I tried doing it in TSQL......didn't work
RESTORE DATABASE test2
FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
WITH RECOVERY,
MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
Bypassing recovery for database 'test2' because it is marked IN LOAD.
Starting up database 'test2'.
Recovery is checkpointing database 'test2' (58)
"Immy" wrote:
> Try using the 'WITH RECOVERY' OPTION and issue the restore command via TSQL
> and not the Gui.
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
> > Is it possible if I do not specify "Force over existing Database" that it
> > can
> > cause this error?
> >
> > "Kristen" wrote:
> >
> >> I do restores on a daily basis. The restores are taken from DISK
> >> backups. I
> >> am not using backup devices.
> >> I am noticing all these errors in the log file:
> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
> >> When I do the restore, I am using the gui (rt click on database, select
> >> restore).
> >> Sometimes I need to restore over an existing DB, other times I restore to
> >> a
> >> new database.
> >> Any ideas? I want to make sure I am doing the restores correctly and
> >> remove
> >> these errors
>
>|||So - to clarify, you did a full backup of your database, then copy the
database to a new location and then restore it?
What happens if you restore the backup on the same server that the backup
was taken from?
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
>I tried doing it in TSQL......didn't work
> RESTORE DATABASE test2
> FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> WITH RECOVERY,
> MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> Bypassing recovery for database 'test2' because it is marked IN LOAD.
> Starting up database 'test2'.
> Recovery is checkpointing database 'test2' (58)
>
> "Immy" wrote:
>> Try using the 'WITH RECOVERY' OPTION and issue the restore command via
>> TSQL
>> and not the Gui.
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
>> > Is it possible if I do not specify "Force over existing Database" that
>> > it
>> > can
>> > cause this error?
>> >
>> > "Kristen" wrote:
>> >
>> >> I do restores on a daily basis. The restores are taken from DISK
>> >> backups. I
>> >> am not using backup devices.
>> >> I am noticing all these errors in the log file:
>> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
>> >> When I do the restore, I am using the gui (rt click on database,
>> >> select
>> >> restore).
>> >> Sometimes I need to restore over an existing DB, other times I restore
>> >> to
>> >> a
>> >> new database.
>> >> Any ideas? I want to make sure I am doing the restores correctly and
>> >> remove
>> >> these errors
>>|||And obviously I mean in a different DB name and not over the existing one ;)
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
>I tried doing it in TSQL......didn't work
> RESTORE DATABASE test2
> FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> WITH RECOVERY,
> MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> Bypassing recovery for database 'test2' because it is marked IN LOAD.
> Starting up database 'test2'.
> Recovery is checkpointing database 'test2' (58)
>
> "Immy" wrote:
>> Try using the 'WITH RECOVERY' OPTION and issue the restore command via
>> TSQL
>> and not the Gui.
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
>> > Is it possible if I do not specify "Force over existing Database" that
>> > it
>> > can
>> > cause this error?
>> >
>> > "Kristen" wrote:
>> >
>> >> I do restores on a daily basis. The restores are taken from DISK
>> >> backups. I
>> >> am not using backup devices.
>> >> I am noticing all these errors in the log file:
>> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
>> >> When I do the restore, I am using the gui (rt click on database,
>> >> select
>> >> restore).
>> >> Sometimes I need to restore over an existing DB, other times I restore
>> >> to
>> >> a
>> >> new database.
>> >> Any ideas? I want to make sure I am doing the restores correctly and
>> >> remove
>> >> these errors
>>|||"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
>I tried doing it in TSQL......didn't work
> RESTORE DATABASE test2
> FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> WITH RECOVERY,
> MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> Bypassing recovery for database 'test2' because it is marked IN LOAD.
> Starting up database 'test2'.
> Recovery is checkpointing database 'test2' (58)
Why do you think these are "errors"? The sql server log file contains
messages, some of which may be errors but many of which are not. The
messages you see are standard messages that indicate a successful restore
(btw - I don't think you posted all the relevant messages since there should
be a "Database restored: Database: <dbname>, creation ..." message.|||I want to make sure the "IN LOAD" messages that I am getting will not create
any issues in the future. Do you know of any potential problems I could face
when getting this "IN LOAD" message
"Scott Morris" wrote:
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
> >I tried doing it in TSQL......didn't work
> >
> > RESTORE DATABASE test2
> > FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> > WITH RECOVERY,
> > MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> > MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> >
> > Bypassing recovery for database 'test2' because it is marked IN LOAD.
> > Starting up database 'test2'.
> > Recovery is checkpointing database 'test2' (58)
> Why do you think these are "errors"? The sql server log file contains
> messages, some of which may be errors but many of which are not. The
> messages you see are standard messages that indicate a successful restore
> (btw - I don't think you posted all the relevant messages since there should
> be a "Database restored: Database: <dbname>, creation ..." message.
>
>|||For this test, I took a backup, then restored it to a new database. When I
did that I got the error.
I am only working with 1 server so I did restore the backup on the same
server that the backup was taken from.
#2 - Most of the time, I need to restore a backup (from another DB) to an
existing database. I was using the GUI for this also. I think that may be
why I am having these messages in the log.
Can you give me an idea for the #2 scenario above what my RESTORE DATABASE
options should be?
Thanks for your help with this.
"Immy" wrote:
> So - to clarify, you did a full backup of your database, then copy the
> database to a new location and then restore it?
> What happens if you restore the backup on the same server that the backup
> was taken from?
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
> >I tried doing it in TSQL......didn't work
> >
> > RESTORE DATABASE test2
> > FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> > WITH RECOVERY,
> > MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> > MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> >
> > Bypassing recovery for database 'test2' because it is marked IN LOAD.
> > Starting up database 'test2'.
> > Recovery is checkpointing database 'test2' (58)
> >
> >
> > "Immy" wrote:
> >
> >> Try using the 'WITH RECOVERY' OPTION and issue the restore command via
> >> TSQL
> >> and not the Gui.
> >>
> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> >> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
> >> > Is it possible if I do not specify "Force over existing Database" that
> >> > it
> >> > can
> >> > cause this error?
> >> >
> >> > "Kristen" wrote:
> >> >
> >> >> I do restores on a daily basis. The restores are taken from DISK
> >> >> backups. I
> >> >> am not using backup devices.
> >> >> I am noticing all these errors in the log file:
> >> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
> >> >> When I do the restore, I am using the gui (rt click on database,
> >> >> select
> >> >> restore).
> >> >> Sometimes I need to restore over an existing DB, other times I restore
> >> >> to
> >> >> a
> >> >> new database.
> >> >> Any ideas? I want to make sure I am doing the restores correctly and
> >> >> remove
> >> >> these errors
> >>
> >>
> >>
>
>|||No I didn't post all the messages. The database did successfully restore. I
want to understand what I am doing wrong when restoring these databases so
the messages are not written to the log. Most of the time I am taking a
backup of 1 database and restoring it to another existing DB. Using the GUI
for the restore.
Any ideas?
"Scott Morris" wrote:
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
> >I tried doing it in TSQL......didn't work
> >
> > RESTORE DATABASE test2
> > FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> > WITH RECOVERY,
> > MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> > MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> >
> > Bypassing recovery for database 'test2' because it is marked IN LOAD.
> > Starting up database 'test2'.
> > Recovery is checkpointing database 'test2' (58)
> Why do you think these are "errors"? The sql server log file contains
> messages, some of which may be errors but many of which are not. The
> messages you see are standard messages that indicate a successful restore
> (btw - I don't think you posted all the relevant messages since there should
> be a "Database restored: Database: <dbname>, creation ..." message.
>
>|||If I do RESTORE DATABASE test WITH RECOVERY
it doesn't actually do a restore from a backup...it just sets the RECOVERY
option correct?
"Immy" wrote:
> Try using the 'WITH RECOVERY' OPTION and issue the restore command via TSQL
> and not the Gui.
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
> > Is it possible if I do not specify "Force over existing Database" that it
> > can
> > cause this error?
> >
> > "Kristen" wrote:
> >
> >> I do restores on a daily basis. The restores are taken from DISK
> >> backups. I
> >> am not using backup devices.
> >> I am noticing all these errors in the log file:
> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
> >> When I do the restore, I am using the gui (rt click on database, select
> >> restore).
> >> Sometimes I need to restore over an existing DB, other times I restore to
> >> a
> >> new database.
> >> Any ideas? I want to make sure I am doing the restores correctly and
> >> remove
> >> these errors
>
>|||"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:D9C48AE9-91FC-4752-94CB-0428A569F826@.microsoft.com...
> No I didn't post all the messages. The database did successfully restore.
> I
> want to understand what I am doing wrong when restoring these databases so
> the messages are not written to the log. Most of the time I am taking a
> backup of 1 database and restoring it to another existing DB. Using the
> GUI
> for the restore.
> Any ideas?
There isn't anything wrong and you can't directly control these messages.
These messages are not errors. Again, not everything written to the log is
an error.

Marked "IN LOAD" messages

Wednesday, March 28, 2012

Mapped Drive not availible for backup

To save space on our database server I have shared a folder on our web
server, and mapped it as a drive (S

on our database server. It shows up as
a drive in Windows Explorer, and in any file save dialog box, but is not
availible as a backup option from SQL Enterprise manager. If I type in
s:\DbBackups as the backup device and try to back up I get the following
error:
************************************************** ******
Cannot open backup device 'S:\DBBACKUPS\MyDB.bak'. Device error or device
off-line. See the SQL Server error log for more details. BACKUP DATABASE is
terminating abnormally.
************************************************** ******
The error log shows
************************************************** *******
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(6)
Event ID:17055
Date:9/15/2004
Time:4:17:47 PM
User:N/A
Computer:SYNCDB
Description:
3041 :
BACKUP failed to complete the command BACKUP DATABASE [DigitalDraw_Sys] TO
DISK = N'S:\DBBACKUPS\MyDB.bak' WITH NOINIT , NOUNLOAD , NAME = N'MyDB
backup', NOSKIP , STATS = 10, NOFORMAT
************************************************** *********
What is keeping me from backing up to this mapped drive?
Is it some sort of permissions issue? If so what acct do I need to grant
permissions for?
Any help is greatly appreciated.
-Dan
You can't use a mapped drive for backups but you can use the UNC name:
BACKUP DATABASE mydb TO DISK = '\\servername\share name\DBBACKUPS\MyDB.bak'
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:0DD86426-C9C6-48B6-8DE8-0F64C592F07E@.microsoft.com...
> To save space on our database server I have shared a folder on our web
> server, and mapped it as a drive (S

on our database server. It shows up
> as
> a drive in Windows Explorer, and in any file save dialog box, but is not
> availible as a backup option from SQL Enterprise manager. If I type in
> s:\DbBackups as the backup device and try to back up I get the following
> error:
> ************************************************** ******
> Cannot open backup device 'S:\DBBACKUPS\MyDB.bak'. Device error or device
> off-line. See the SQL Server error log for more details. BACKUP DATABASE
> is
> terminating abnormally.
> ************************************************** ******
> The error log shows
> ************************************************** *******
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 9/15/2004
> Time: 4:17:47 PM
> User: N/A
> Computer: SYNCDB
> Description:
> 3041 :
> BACKUP failed to complete the command BACKUP DATABASE [DigitalDraw_Sys] TO
> DISK = N'S:\DBBACKUPS\MyDB.bak' WITH NOINIT , NOUNLOAD , NAME = N'MyDB
> backup', NOSKIP , STATS = 10, NOFORMAT
> ************************************************** *********
> What is keeping me from backing up to this mapped drive?
> Is it some sort of permissions issue? If so what acct do I need to grant
> permissions for?
> Any help is greatly appreciated.
> -Dan
>
|||Hi,
Try doing this:-
1. Have a common OS user in both systems, Say name of the OS user is
backupuser.
2. Start the MSSQL Server and SQl Agent service using backupuser
How to do that:
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option. There you give a the backupuser
and password to start the service.
Now stop and start the MSSQL Serevr service.
3. Give the necessary privileges (write) in the remote machine directory
and share.
4. After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to
Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init will
overwrite the backup file every time)
I think this will definitely work out.
Thanks
Hari
MCDBA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:0DD86426-C9C6-48B6-8DE8-0F64C592F07E@.microsoft.com...
> To save space on our database server I have shared a folder on our web
> server, and mapped it as a drive (S

Mapped drive database backups

How would I schedule full database backups to backup to
another disk on a mapped shared network drive?
I have tried adding a disk dump device using
sp_addumpdevice. Then running the BACKUP DATABASE command
but I get the following error message:
Cannot open backup device 'pdmsdiskdump'. Device error or
device off-line.
Thanks,
Anjelina> How would I schedule full database backups to backup to
> another disk on a mapped shared network drive?
If you set up a mapped drive H: while logged in as user A, user A is the
only person who can see an H:!
Use \\UNCPath\, unless you have set SQL Server and agent to run as a domain
account with access to the share, and you set up the mapped drive while
logged in as that user.

mapped drive and backups

I mapped a drive to a different server, which has space, then tried to use
Ent. Manager to create a backup job to the mapped drive. Only, the mapped
isn't there only the local drives but the mapped appears locally to the OS.
I forgot something yes?
Thanks
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks
|||Hi
EM will not see mapped drives. Use T-SQL and backup to the UNC path
\\servername\sharename\file.bak.
Make sure that the SQL Server account is a domain account and has
permissions on the share.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks
|||This actually worked.
EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext'
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> EM will not see mapped drives. Use T-SQL and backup to the UNC path
> \\servername\sharename\file.bak.
> Make sure that the SQL Server account is a domain account and has
> permissions on the share.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
> message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>
>

mapped drive and backups

I mapped a drive to a different server, which has space, then tried to use
Ent. Manager to create a backup job to the mapped drive. Only, the mapped
isn't there only the local drives but the mapped appears locally to the OS.
I forgot something yes?
ThanksHowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks|||Hi
EM will not see mapped drives. Use T-SQL and backup to the UNC path
\\servername\sharename\file.bak.
Make sure that the SQL Server account is a domain account and has
permissions on the share.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks|||This actually worked.
EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext'
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> EM will not see mapped drives. Use T-SQL and backup to the UNC path
> \\servername\sharename\file.bak.
> Make sure that the SQL Server account is a domain account and has
> permissions on the share.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
> message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
> >I mapped a drive to a different server, which has space, then tried to use
> > Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> > isn't there only the local drives but the mapped appears locally to the
> > OS.
> > I forgot something yes?
> > Thanks
>
>sql

mapped drive and backups

I mapped a drive to a different server, which has space, then tried to use
Ent. Manager to create a backup job to the mapped drive. Only, the mapped
isn't there only the local drives but the mapped appears locally to the OS.
I forgot something yes?
ThanksHowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks|||Hi
EM will not see mapped drives. Use T-SQL and backup to the UNC path
\\servername\sharename\file.bak.
Make sure that the SQL Server account is a domain account and has
permissions on the share.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote in
message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>I mapped a drive to a different server, which has space, then tried to use
> Ent. Manager to create a backup job to the mapped drive. Only, the mapped
> isn't there only the local drives but the mapped appears locally to the
> OS.
> I forgot something yes?
> Thanks|||This actually worked.
EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext'
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> EM will not see mapped drives. Use T-SQL and backup to the UNC path
> \\servername\sharename\file.bak.
> Make sure that the SQL Server account is a domain account and has
> permissions on the share.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BrianMultiLanguage" <BrianMultiLanguage@.discussions.microsoft.com> wrote
in
> message news:C8DDEE27-FEC9-4FA7-BEFD-03048AA5A774@.microsoft.com...
>
>

Monday, March 19, 2012

manually truncate a log

I have a log.ldf file that has grown to around 150 GB ... I can't really do a
BACKUP of the database to truncate it.... it seems to struggle with that...
is there another way that I can MANUALLY truncate this log file?
help..How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> BACKUP of the database to truncate it.... it seems to struggle with that...
> is there another way that I can MANUALLY truncate this log file?
> help..|||Also: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1174062754.069322.283110@.y80g2000hsf.googlegroups.com...
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
>> BACKUP of the database to truncate it.... it seems to struggle with that...
>> is there another way that I can MANUALLY truncate this log file?
>> help..
>|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
> > I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> > BACKUP of the database to truncate it.... it seems to struggle with that...
> >
> > is there another way that I can MANUALLY truncate this log file?
> >
> > help..
>
>|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
>> How full is the log? Do you do any t-log backups at all? Its
>> possible you can just shrink it if the log is somewhat empty.
>> Otherwise, take a full backup, switch to simple recovery, shrink the
>> log, set back to Full and schedule your t-logs to be backed up on a
>> regular basis (we do our critical systems every 10 minutes, and our
>> important systems every two hours)
>> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
>> wrote:
>> > I have a log.ldf file that has grown to around 150 GB ... I can't
>> > really do a
>> > BACKUP of the database to truncate it.... it seems to struggle with
>> > that...
>> >
>> > is there another way that I can MANUALLY truncate this log file?
>> >
>> > help..
>>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

Manually roll-forward transactions

I have a full backup of my database, a transaction log backup (taken after
the full). The data files have been deleted, but I do have the transaction
log. After the last transaction log backup, there where a few important
transactions that happened that I need to roll-forward. Is there a way to
get those transactions back if I only have the transaction log, and not a
backup of it?
Thanks in advance.Use the following command:
BACKUP LOG databasename WITH NO_TRUNCATE
This will back up your current log even if the data files are not available.
Then you'll be able to restore your database using your last full backup and
subsequent log backups.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> I have a full backup of my database, a transaction log backup (taken after
> the full). The data files have been deleted, but I do have the
transaction
> log. After the last transaction log backup, there where a few important
> transactions that happened that I need to roll-forward. Is there a way to
> get those transactions back if I only have the transaction log, and not a
> backup of it?
> Thanks in advance.
>|||So if I start up SQL Server, after I have lost my data files (from a lost
disk that housed them), I will be able to do a backup of the log? Wouldn't
the currpted status of my database prevent me from doing the tran log
backup?
Paul
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> Use the following command:
> BACKUP LOG databasename WITH NO_TRUNCATE
> This will back up your current log even if the data files are not
available.
> Then you'll be able to restore your database using your last full backup
and
> subsequent log backups.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> > I have a full backup of my database, a transaction log backup (taken
after
> > the full). The data files have been deleted, but I do have the
> transaction
> > log. After the last transaction log backup, there where a few important
> > transactions that happened that I need to roll-forward. Is there a way
to
> > get those transactions back if I only have the transaction log, and not
a
> > backup of it?
> >
> > Thanks in advance.
> >
> >
>|||No, it doesn't prevent you from doing it. Actually, it's something very easy
to test. Just stop sql server, delete data files, restart sql server and
then issue the "magic" command.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:uKVQ0$p6DHA.2644@.TK2MSFTNGP11.phx.gbl...
> So if I start up SQL Server, after I have lost my data files (from a lost
> disk that housed them), I will be able to do a backup of the log?
Wouldn't
> the currpted status of my database prevent me from doing the tran log
> backup?
> Paul
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Use the following command:
> > BACKUP LOG databasename WITH NO_TRUNCATE
> > This will back up your current log even if the data files are not
> available.
> > Then you'll be able to restore your database using your last full backup
> and
> > subsequent log backups.
> >
> > --
> > Carlos E. Rojas
> > SQL Server MVP
> > Co-Author SQL Server 2000 Programming by Example
> >
> >
> > "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> > news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> > > I have a full backup of my database, a transaction log backup (taken
> after
> > > the full). The data files have been deleted, but I do have the
> > transaction
> > > log. After the last transaction log backup, there where a few
important
> > > transactions that happened that I need to roll-forward. Is there a
way
> to
> > > get those transactions back if I only have the transaction log, and
not
> a
> > > backup of it?
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>

Manually roll-forward transactions

quote:

> I have a full backup of my database, a transaction log backup (taken after
> the full). The data files have been deleted, but I do have the

transaction

quote:

> log. After the last transaction log backup, there where a few important
> transactions that happened that I need to roll-forward. Is there a way to
> get those transactions back if I only have the transaction log, and not a
> backup of it?
> Thanks in advance.
>

|||So if I start up SQL Server, after I have lost my data files (from a lost
disk that housed them), I will be able to do a backup of the log? Wouldn't
the currpted status of my database prevent me from doing the tran log
backup?
Paul
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...

quote:

> Use the following command:
> BACKUP LOG databasename WITH NO_TRUNCATE
> This will back up your current log even if the data files are not

available.

quote:

> Then you'll be able to restore your database using your last full backup

and

quote:

> subsequent log backups.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
after[QUOTE]
> transaction
to[QUOTE]
a[QUOTE]
>

|||No, it doesn't prevent you from doing it. Actually, it's something very easy
to test. Just stop sql server, delete data files, restart sql server and
then issue the "magic" command.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:uKVQ0$p6DHA.2644@.TK2MSFTNGP11.phx.gbl...

quote:

> So if I start up SQL Server, after I have lost my data files (from a lost
> disk that housed them), I will be able to do a backup of the log?

Wouldn't

quote:

> the currpted status of my database prevent me from doing the tran log
> backup?
> Paul
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> available.
> and
> after
important[QUOTE]
way[QUOTE]
> to
not[QUOTE]
> a
>

Manual log shipping

Hello:
I've implemented a stand-by server solution, where the
tran log backup from the primary server gets restored to
the secondary server at every 15-min interval.
I understand that there are some limitations with this
approach (could not implement MS SLS as our business unit
could not afford to purchase the Ent. Ed.), and was
wondering if anyone has encountered any other issues or
observations when implementing a similar manual log
shipping process, other than my own observations listed
below:
Log Shipping will fail if...
- ...there are any open connections to the database where
the transaction log files are restored to; though querying
tables using the fully qualified name is possible from
another database connection or via a linked server
connection.
I've also had one incident where my log shipping process
failed due to a LSN out of sync issue. This happened when
I ran a BCP IN operation. Other times, both BCP and BULK
INSERT operations ran successfully, funnelling changes to
the secondary server's database as expected.
Thank you for all your responses.
Regards,
- Rob.Your observations are correct. Log shipping will fail if there are =users connected to the database. I am wondering if perhaps someone =changed the dboptions when you had the log shipping fail after a BCP =import.
And no, I have not experienced any other issues. The custom log =shipping approach works very well.
You can find a script that will kill any connections to the specified =database here:
http://sqlguy.home.comcast.net/logship.htm
-- Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message =news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
> Hello:
> > I've implemented a stand-by server solution, where the > tran log backup from the primary server gets restored to > the secondary server at every 15-min interval.
> > I understand that there are some limitations with this > approach (could not implement MS SLS as our business unit > could not afford to purchase the Ent. Ed.), and was > wondering if anyone has encountered any other issues or > observations when implementing a similar manual log > shipping process, other than my own observations listed > below:
> > Log Shipping will fail if...
> > - ...there are any open connections to the database where > the transaction log files are restored to; though querying > tables using the fully qualified name is possible from > another database connection or via a linked server > connection.
> > I've also had one incident where my log shipping process > failed due to a LSN out of sync issue. This happened when > I ran a BCP IN operation. Other times, both BCP and BULK > INSERT operations ran successfully, funnelling changes to > the secondary server's database as expected.
> > Thank you for all your responses.
> > Regards,
> > - Rob.|||Rather than doing a KILL command on each SPID, a cleaner
way to do it is to put the database in single user mode
with rollback immediate for the duration of the log
restore and then put it back in multi user mode. This
works very well. Here's an example:
alter database database_name set SINGLE_USER with rollback
immediate
restore log database_name from disk
= 'c:\database_name_log.bak' with standby
= 'c:\standby\database_name.bak'
alter database database_name set MULTI_USER
>--Original Message--
>Your observations are correct. Log shipping will fail if
there are users connected to the database. I am wondering
if perhaps someone changed the dboptions when you had the
log shipping fail after a BCP import.
>And no, I have not experienced any other issues. The
custom log shipping approach works very well.
>You can find a script that will kill any connections to
the specified database here:
>http://sqlguy.home.comcast.net/logship.htm
>--
>Keith
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where the
>> tran log backup from the primary server gets restored
to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with this
>> approach (could not implement MS SLS as our business
unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
where
>> the transaction log files are restored to; though
querying
>> tables using the fully qualified name is possible from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
process
>> failed due to a LSN out of sync issue. This happened
when
>> I ran a BCP IN operation. Other times, both BCP and
BULK
>> INSERT operations ran successfully, funnelling changes
to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>.
>|||Agreed. I need to update the web page.
-- Keith
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message =news:13bbf01c3f7d4$804fb2a0$a001280a@.phx.gbl...
> Rather than doing a KILL command on each SPID, a cleaner > way to do it is to put the database in single user mode > with rollback immediate for the duration of the log > restore and then put it back in multi user mode. This > works very well. Here's an example:
> > alter database database_name set SINGLE_USER with rollback > immediate
> > restore log database_name from disk > =3D 'c:\database_name_log.bak' with standby > =3D 'c:\standby\database_name.bak'
> > alter database database_name set MULTI_USER
> > >--Original Message--
> >Your observations are correct. Log shipping will fail if > there are users connected to the database. I am wondering > if perhaps someone changed the dboptions when you had the > log shipping fail after a BCP import. > >
> >And no, I have not experienced any other issues. The > custom log shipping approach works very well.
> >
> >You can find a script that will kill any connections to > the specified database here:
> >http://sqlguy.home.comcast.net/logship.htm
> >
> >-- > >Keith
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in > message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
> >> Hello:
> >> > >> I've implemented a stand-by server solution, where the > >> tran log backup from the primary server gets restored > to > >> the secondary server at every 15-min interval.
> >> > >> I understand that there are some limitations with this > >> approach (could not implement MS SLS as our business > unit > >> could not afford to purchase the Ent. Ed.), and was > >> wondering if anyone has encountered any other issues or > >> observations when implementing a similar manual log > >> shipping process, other than my own observations listed > >> below:
> >> > >> Log Shipping will fail if...
> >> > >> - ...there are any open connections to the database > where > >> the transaction log files are restored to; though > querying > >> tables using the fully qualified name is possible from > >> another database connection or via a linked server > >> connection.
> >> > >> I've also had one incident where my log shipping > process > >> failed due to a LSN out of sync issue. This happened > when > >> I ran a BCP IN operation. Other times, both BCP and > BULK > >> INSERT operations ran successfully, funnelling changes > to > >> the secondary server's database as expected.
> >> > >> Thank you for all your responses.
> >> > >> Regards,
> >> > >> - Rob.
> >.
> >|||But even in single user mode, there could be multiple
connections to the database, which can cause manual log
shipping failures. In this case, I find killing all user
connections more effective, to ensure no connections
exists prior to restoring either the full backup and/or
the tran log.
Thanks.
>--Original Message--
>Rather than doing a KILL command on each SPID, a cleaner
>way to do it is to put the database in single user mode
>with rollback immediate for the duration of the log
>restore and then put it back in multi user mode. This
>works very well. Here's an example:
>alter database database_name set SINGLE_USER with
rollback
>immediate
>restore log database_name from disk
>= 'c:\database_name_log.bak' with standby
>= 'c:\standby\database_name.bak'
>alter database database_name set MULTI_USER
>>--Original Message--
>>Your observations are correct. Log shipping will fail
if
>there are users connected to the database. I am
wondering
>if perhaps someone changed the dboptions when you had the
>log shipping fail after a BCP import.
>>And no, I have not experienced any other issues. The
>custom log shipping approach works very well.
>>You can find a script that will kill any connections to
>the specified database here:
>>http://sqlguy.home.comcast.net/logship.htm
>>--
>>Keith
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where the
>> tran log backup from the primary server gets restored
>to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with this
>> approach (could not implement MS SLS as our business
>unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues
or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations
listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
>where
>> the transaction log files are restored to; though
>querying
>> tables using the fully qualified name is possible from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
>process
>> failed due to a LSN out of sync issue. This happened
>when
>> I ran a BCP IN operation. Other times, both BCP and
>BULK
>> INSERT operations ran successfully, funnelling changes
>to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>>.
>.
>|||Putting it in 'single user mode with rollback immediate'
will disconnect any currnet connections to the db and then
put it in single user mode for the process to restore the
log. Since it's in single user mode, only the process
that is restoring the log can connect. Once the restore
is done, just put it back into multi user mode.
>--Original Message--
>But even in single user mode, there could be multiple
>connections to the database, which can cause manual log
>shipping failures. In this case, I find killing all user
>connections more effective, to ensure no connections
>exists prior to restoring either the full backup and/or
>the tran log.
>Thanks.
>>--Original Message--
>>Rather than doing a KILL command on each SPID, a cleaner
>>way to do it is to put the database in single user mode
>>with rollback immediate for the duration of the log
>>restore and then put it back in multi user mode. This
>>works very well. Here's an example:
>>alter database database_name set SINGLE_USER with
>rollback
>>immediate
>>restore log database_name from disk
>>= 'c:\database_name_log.bak' with standby
>>= 'c:\standby\database_name.bak'
>>alter database database_name set MULTI_USER
>>--Original Message--
>>Your observations are correct. Log shipping will fail
>if
>>there are users connected to the database. I am
>wondering
>>if perhaps someone changed the dboptions when you had
the
>>log shipping fail after a BCP import.
>>And no, I have not experienced any other issues. The
>>custom log shipping approach works very well.
>>You can find a script that will kill any connections to
>>the specified database here:
>>http://sqlguy.home.comcast.net/logship.htm
>>--
>>Keith
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>>message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where
the
>> tran log backup from the primary server gets restored
>>to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with
this
>> approach (could not implement MS SLS as our business
>>unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues
>or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations
>listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
>>where
>> the transaction log files are restored to; though
>>querying
>> tables using the fully qualified name is possible
from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
>>process
>> failed due to a LSN out of sync issue. This happened
>>when
>> I ran a BCP IN operation. Other times, both BCP and
>>BULK
>> INSERT operations ran successfully, funnelling
changes
>>to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>>.
>>.
>.
>

Manual log shipping

Monday, March 12, 2012

Manual backup works, automatic scheduled doesn't

I am not able to perform automatic scheduled backups of one of our databases.
This database is called StarTeam_stardraw60_db and is located within
VSDEV\STARTEAM instance of MS SQL server.
I also tried to perform an automatic backup of another database within
VSDEV\STARTEAM instance and no luck.
However, if I try to perform an automatic scheduled backup of any database
that's located under a different instance of MS SQL Server, for example,
ProjectServer database under local instance, then everything works correctly.
I'm not clear on why one works correctly, while the other doesn't.
Thanks.
Is Agent started for that instance?
How do you define the backup? TSQL job step in an Agent job? Maintenance Plan?
What version of SQL Server?
Is there any error messages in the Agent/Maint plan output file/Report file?
Did you define an output file for the jobstep/Report file for the Maint plan?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>I am not able to perform automatic scheduled backups of one of our databases.
> This database is called StarTeam_stardraw60_db and is located within
> VSDEV\STARTEAM instance of MS SQL server.
> I also tried to perform an automatic backup of another database within
> VSDEV\STARTEAM instance and no luck.
> However, if I try to perform an automatic scheduled backup of any database
> that's located under a different instance of MS SQL Server, for example,
> ProjectServer database under local instance, then everything works correctly.
> I'm not clear on why one works correctly, while the other doesn't.
> Thanks.
>
|||Thank you - it was the Agent not started.
"Tibor Karaszi" wrote:

> Is Agent started for that instance?
> How do you define the backup? TSQL job step in an Agent job? Maintenance Plan?
> What version of SQL Server?
> Is there any error messages in the Agent/Maint plan output file/Report file?
> Did you define an output file for the jobstep/Report file for the Maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
> news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>

Manual backup works, automatic scheduled doesn't

I am not able to perform automatic scheduled backups of one of our databases.
This database is called StarTeam_stardraw60_db and is located within
VSDEV\STARTEAM instance of MS SQL server.
I also tried to perform an automatic backup of another database within
VSDEV\STARTEAM instance and no luck.
However, if I try to perform an automatic scheduled backup of any database
that's located under a different instance of MS SQL Server, for example,
ProjectServer database under local instance, then everything works correctly.
I'm not clear on why one works correctly, while the other doesn't.
Thanks.Is Agent started for that instance?
How do you define the backup? TSQL job step in an Agent job? Maintenance Plan?
What version of SQL Server?
Is there any error messages in the Agent/Maint plan output file/Report file?
Did you define an output file for the jobstep/Report file for the Maint plan?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>I am not able to perform automatic scheduled backups of one of our databases.
> This database is called StarTeam_stardraw60_db and is located within
> VSDEV\STARTEAM instance of MS SQL server.
> I also tried to perform an automatic backup of another database within
> VSDEV\STARTEAM instance and no luck.
> However, if I try to perform an automatic scheduled backup of any database
> that's located under a different instance of MS SQL Server, for example,
> ProjectServer database under local instance, then everything works correctly.
> I'm not clear on why one works correctly, while the other doesn't.
> Thanks.
>|||Thank you - it was the Agent not started.
"Tibor Karaszi" wrote:
> Is Agent started for that instance?
> How do you define the backup? TSQL job step in an Agent job? Maintenance Plan?
> What version of SQL Server?
> Is there any error messages in the Agent/Maint plan output file/Report file?
> Did you define an output file for the jobstep/Report file for the Maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
> news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
> >I am not able to perform automatic scheduled backups of one of our databases.
> >
> > This database is called StarTeam_stardraw60_db and is located within
> > VSDEV\STARTEAM instance of MS SQL server.
> > I also tried to perform an automatic backup of another database within
> > VSDEV\STARTEAM instance and no luck.
> >
> > However, if I try to perform an automatic scheduled backup of any database
> > that's located under a different instance of MS SQL Server, for example,
> > ProjectServer database under local instance, then everything works correctly.
> >
> > I'm not clear on why one works correctly, while the other doesn't.
> >
> > Thanks.
> >
>

Manual backup works, automatic scheduled doesn't

I am not able to perform automatic scheduled backups of one of our databases
.
This database is called StarTeam_stardraw60_db and is located within
VSDEV\STARTEAM instance of MS SQL server.
I also tried to perform an automatic backup of another database within
VSDEV\STARTEAM instance and no luck.
However, if I try to perform an automatic scheduled backup of any database
that's located under a different instance of MS SQL Server, for example,
ProjectServer database under local instance, then everything works correctly
.
I'm not clear on why one works correctly, while the other doesn't.
Thanks.Is Agent started for that instance?
How do you define the backup? TSQL job step in an Agent job? Maintenance Pla
n?
What version of SQL Server?
Is there any error messages in the Agent/Maint plan output file/Report file?
Did you define an output file for the jobstep/Report file for the Maint plan
?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>I am not able to perform automatic scheduled backups of one of our database
s.
> This database is called StarTeam_stardraw60_db and is located within
> VSDEV\STARTEAM instance of MS SQL server.
> I also tried to perform an automatic backup of another database within
> VSDEV\STARTEAM instance and no luck.
> However, if I try to perform an automatic scheduled backup of any database
> that's located under a different instance of MS SQL Server, for example,
> ProjectServer database under local instance, then everything works correct
ly.
> I'm not clear on why one works correctly, while the other doesn't.
> Thanks.
>|||Thank you - it was the Agent not started.
"Tibor Karaszi" wrote:

> Is Agent started for that instance?
> How do you define the backup? TSQL job step in an Agent job? Maintenance P
lan?
> What version of SQL Server?
> Is there any error messages in the Agent/Maint plan output file/Report fil
e?
> Did you define an output file for the jobstep/Report file for the Maint pl
an?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
> news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>

Manual backup not starting

I am trying to run a manual backup of a database in SQL Server 2000, as I've
done many times in the past. This database is over 4 Gb. The backup says
it is in progress, but I never see any "progress". It has been sitting
like this for over an hour, and the machine is only using 1% of it's
resources and has plenty of free memory.
Does anyone have any ideas why this is happening? Thanks.Hi,
Can you please try the below command from Query analyzer to backup the
database.
Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
Replace the dbname and drive letter based on your requirement.
Thanks
Hari
MCDBA
"Bill" <WBeatty@.Prodigy.Net> wrote in message
news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I am trying to run a manual backup of a database in SQL Server 2000, as
I've
> done many times in the past. This database is over 4 Gb. The backup says
> it is in progress, but I never see any "progress". It has been sitting
> like this for over an hour, and the machine is only using 1% of it's
> resources and has plenty of free memory.
> Does anyone have any ideas why this is happening? Thanks.
>|||Hari,
OK, I am currently running the query to backup. It has been running for 7
minutes so far. I'll let you know when I see any messages or it completes.
Thanks,
Bill
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I've
says
>|||Just like the Energizer Bunny....still going and going and going.... 1
hour 6 minutes, still not responding in Query Analyzer. I've never run
across this problem. I've backed this database up many times before with no
problem.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I've
says
>|||Well....I couldn't take it any more. I rebooted the server, and now
everything is working fine....who knows! Now the backup completed in 2
minutes. I couldn't find any other jobs that were interfering, everything
looked great, but the goofy thing wouldn't back up.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I've
says
>|||You may have had an old log or full backup that was blocking the first
backup. That happens sometimes. sp_who2 will show any blocking SPIDs.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Bill" <wbeatty@.prodigy.net> wrote in message
news:eA21dDw%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> Well....I couldn't take it any more. I rebooted the server, and now
> everything is working fine....who knows! Now the backup completed in 2
> minutes. I couldn't find any other jobs that were interfering, everything
> looked great, but the goofy thing wouldn't back up.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
stats=10
as
> says
sitting
>

Friday, March 30, 2012

Marked "IN LOAD" messages

Marked "IN LOAD" messages

Marked "IN LOAD" messages

Wednesday, March 28, 2012

Mapped Drive not availible for backup

Mapped drive database backups

mapped drive and backups

mapped drive and backups

mapped drive and backups

Monday, March 19, 2012

manually truncate a log

Manually roll-forward transactions

Manually roll-forward transactions

Manual log shipping

Manual log shipping

Monday, March 12, 2012

Manual backup works, automatic scheduled doesn't

Manual backup works, automatic scheduled doesn't

Manual backup works, automatic scheduled doesn't

Manual backup not starting

Managing sql Log File

Blog Archive

About Me