sql rowcount: backup

Showing posts with label backup. Show all posts

Thursday, March 22, 2012

\Backup directories - remove safely?

I've successfully applied SP4 to an MSDE 2000 installation on a
Windows 2000 Professional system. Now I've notived several \Backup
subdirectories in the "Microsoft SLQ Server" directory. Since I don't
want to go back to any previous version, these subdirectories appear
to be no longer required. May I safely remove them?
Heinz Wehner
(Karlsruhe, Germany)
Hi,
No problems. You can delete that directory.
Thanks
Hari
SQL Server MVP
"Heinz Wehner" <hwehner@.hotmail.com> wrote in message
news:c215e1ppe9k9vqim24ckphf0eukkgr6n5a@.4ax.com...
> I've successfully applied SP4 to an MSDE 2000 installation on a
> Windows 2000 Professional system. Now I've notived several \Backup
> subdirectories in the "Microsoft SLQ Server" directory. Since I don't
> want to go back to any previous version, these subdirectories appear
> to be no longer required. May I safely remove them?
> Heinz Wehner
> (Karlsruhe, Germany)
|||Thanks Hari.

> On Sun, 24 Jul 2005 19:32:03 +0530,
> Hari Prasad <hari_prasad_k@.hotmail.com> wrote:
> Hi,
> No problems. You can delete that directory.
> Thanks
> Hari
> SQL Server MVP
>
> Heinz Wehner <hwehner@.hotmail.com>
> wrote in message news:c215e1ppe9k9vqim24ckphf0eukkgr6n5a@.4ax.com...
> I've successfully applied SP4 to an MSDE 2000 installation on a
> Windows 2000 Professional system. Now I've notived several \Backup
> subdirectories in the "Microsoft SLQ Server" directory. Since I don't
> want to go back to any previous version, these subdirectories appear
> to be no longer required. May I safely remove them?
> Heinz Wehner
> (Karlsruhe, Germany)
sql

\\.\Tape0 is not a vaild Microsoft Tape format backup set

hi all
I am using Sql server 2000 with sp3 on Win 2003. Recently all of my nightly
sql server backups have started failing. Here is the error message I get with
it
The file on device '\\.\Tape0' is not a vaild Microsoft Tape format backup
set. Backup Database is terminating abnormally.
I am using HP dds-4 cartridges (40 GB)
How can I fix that?
Also how do I format my tapes?
regards,
AndreHi
Have you tried dropping the backup device and re-creating it? Will the NT
Backup recognise this tape device?
John
"Andre Gibson" wrote:
> hi all
> I am using Sql server 2000 with sp3 on Win 2003. Recently all of my nightly
> sql server backups have started failing. Here is the error message I get with
> it
> The file on device '\\.\Tape0' is not a vaild Microsoft Tape format backup
> set. Backup Database is terminating abnormally.
> I am using HP dds-4 cartridges (40 GB)
> How can I fix that?
> Also how do I format my tapes?
> regards,
> Andre|||No NT Backup doesn't recognize the tape. How do I drop and re-create the
device?
"John Bell" wrote:
> Hi
> Have you tried dropping the backup device and re-creating it? Will the NT
> Backup recognise this tape device?
> John
> "Andre Gibson" wrote:
> > hi all
> >
> > I am using Sql server 2000 with sp3 on Win 2003. Recently all of my nightly
> > sql server backups have started failing. Here is the error message I get with
> > it
> >
> > The file on device '\\.\Tape0' is not a vaild Microsoft Tape format backup
> > set. Backup Database is terminating abnormally.
> >
> > I am using HP dds-4 cartridges (40 GB)
> >
> > How can I fix that?
> > Also how do I format my tapes?
> >
> > regards,
> >
> > Andre|||Hi Andre
If the NT backup does not recognise the tape, then it may not be SQL Server
that is the problem. Have you tried different tapes or re-installing the tape
drive?
John
"Andre Gibson" wrote:
> No NT Backup doesn't recognize the tape. How do I drop and re-create the
> device?
> "John Bell" wrote:
> > Hi
> >
> > Have you tried dropping the backup device and re-creating it? Will the NT
> > Backup recognise this tape device?
> >
> > John
> >
> > "Andre Gibson" wrote:
> >
> > > hi all
> > >
> > > I am using Sql server 2000 with sp3 on Win 2003. Recently all of my nightly
> > > sql server backups have started failing. Here is the error message I get with
> > > it
> > >
> > > The file on device '\\.\Tape0' is not a vaild Microsoft Tape format backup
> > > set. Backup Database is terminating abnormally.
> > >
> > > I am using HP dds-4 cartridges (40 GB)
> > >
> > > How can I fix that?
> > > Also how do I format my tapes?
> > >
> > > regards,
> > >
> > > Andre|||John,
You were correct. It was my tapes.
Thanks so much for your assistance.
regards,
Andre
"John Bell" wrote:
> Hi Andre
> If the NT backup does not recognise the tape, then it may not be SQL Server
> that is the problem. Have you tried different tapes or re-installing the tape
> drive?
> John
> "Andre Gibson" wrote:
> > No NT Backup doesn't recognize the tape. How do I drop and re-create the
> > device?
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Have you tried dropping the backup device and re-creating it? Will the NT
> > > Backup recognise this tape device?
> > >
> > > John
> > >
> > > "Andre Gibson" wrote:
> > >
> > > > hi all
> > > >
> > > > I am using Sql server 2000 with sp3 on Win 2003. Recently all of my nightly
> > > > sql server backups have started failing. Here is the error message I get with
> > > > it
> > > >
> > > > The file on device '\\.\Tape0' is not a vaild Microsoft Tape format backup
> > > > set. Backup Database is terminating abnormally.
> > > >
> > > > I am using HP dds-4 cartridges (40 GB)
> > > >
> > > > How can I fix that?
> > > > Also how do I format my tapes?
> > > >
> > > > regards,
> > > >
> > > > Andre

Tuesday, March 20, 2012

\\.\Tape0 is not a vaild Microsoft Tape format backup set

[URGENT] Problem to Restore Database from backup

Mrs,

I'm having a serious problem in SQL 2000 SP3 Database which is used Business Applications.
Today, I tryied to take backup in service time by the Enterprize Manager, the following
error occured and could not take backup.
--
Error 926 : Database '[database name]' cannot opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
--
After that, we tried same operation 3 times. But result is same.

After sometime, end-user report us that they can not log-in to database.

So we tried to dettach this database and attached it.
We success to dettach, but we can not attached it.

Also we tried to restore this database from Yesterday's backup file by using Enterprize Manager,
but the following error occured, and could not complete.
This backup file was generated daily backup task in Maintenance Plan in Enterprize Manager.
--
Microsoft SQl-DMO(ODBC SQLState:42000)
The media family on device "E:\ccd\......xxx.bak" is incorrectly formed.
SQL Server cannot process this media family.
RESTORE DATABASE is terminating abnormally.

We need to recover the database immediately not to stop the end-user business process.
Please help us.

Yamagury.

refer , http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1977204&SiteID=1 try as suggested and get back.... also you need to apply SP4 or go to sql 2005 as sql 2000 sp3 has ended its life cycle in july 10th 2007 itself....

refer

http://support.microsoft.com/gp/lifesupsps

|||Thank you for your support.

Yes, We already tried to apply SP4.
But this problem was not solved.

Now I'm trying to do as your instruction.|||(you had to back up the transaction log before you perform any restore operations and hadn't detach it before.

You had to verify your backup with RESTORE VERIFYONLY statement .)

use RESTORE HEADERONLY and see if you have multiple backup files in the the same backup file. If yes then
try using FILE= option while restoring.

File is from :

RESTORE DATABASE { database_name | @.database_name_var }
< file_or_filegroup > [ ,...n ]
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @.file_number } ] .....

see Books Online

(Why do you have not SP4?

Read this.)

|||
Dear Deepak
I tried to create database and swap mdf/ldf file.
and go into single user mode, and then run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS,
but, DBCC command return error. Database could not be repaired.

How can I do after this?|||

Ok you can try as follows,

Alter database DBname set emergency

this will bring your db into emergency mode so that you can access your db and then subsequently export the datas alone from this db to another db so that you can minimize the dataloss.....if you have a full backup then drop this suspect database and restore it from full backup but there will be dataloss to minimize it set the db as emergency mode and export the tables,sps etc which you feel is required to another db in UAT server and script those and rerun the script in the restored db

Monday, March 19, 2012

[SQL2005] Backup Schedule

I understand most things concerning making a backup, but I cannot seem to
figure out what a sensible schedule is for making a backup. I would love to
figure this out before we go live in a few months.
The database will have a reasonable mutation rate, i.e. around 500 data
changes per day and work-loss must be kept to a minimum. We expect the size
to be around 2GB. The schedule I have made now is as follows:
Full Backup on Sunday at 02:00
Differential Backups on other weekdays at 02:00
Transaction Log Backups every 4 hours every day
Clean History 4 weeks old files every week on Sunday at 04:00
I do not know if this makes sense. I do not know if the log backup proces
will hinder the users or not. I do know there are many many variables that
play a role in making a good backup procedure, but I would greatly appreciate
any insight concerning correct scheduling. TIA!I would definitely change things a bit, considering "work-loss must be kept to a minimum".
How about database backup every day and log backup at least every hour. Or log backups every 10
minutes? The more frequent you do log backup, the less impact is will have each time it is executed
(even though the impact is low to begin with, especially if you have separated log/data/backup
disks). For a 2 GB database, I probably wouldn't do differential backups, unless you have compelling
advantages to do so.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
news:E2D4C305-A7A2-4651-A4DB-CE04E247F5FB@.microsoft.com...
>I understand most things concerning making a backup, but I cannot seem to
> figure out what a sensible schedule is for making a backup. I would love to
> figure this out before we go live in a few months.
> The database will have a reasonable mutation rate, i.e. around 500 data
> changes per day and work-loss must be kept to a minimum. We expect the size
> to be around 2GB. The schedule I have made now is as follows:
> Full Backup on Sunday at 02:00
> Differential Backups on other weekdays at 02:00
> Transaction Log Backups every 4 hours every day
> Clean History 4 weeks old files every week on Sunday at 04:00
> I do not know if this makes sense. I do not know if the log backup proces
> will hinder the users or not. I do know there are many many variables that
> play a role in making a good backup procedure, but I would greatly appreciate
> any insight concerning correct scheduling. TIA!|||I agree with Tibor. Although I'm sure useful in some scenarios, we
don't use differentials at all. Fulls once per day, and then logs
every hour to two hours, is what we do. It's easier to manage the
files, and we can keep track in our head which files exactly we need in
the case of restore. And we're in the same boat - "work-loss must be
kept to a minimum".
Tibor Karaszi wrote:
> I would definitely change things a bit, considering "work-loss must be kept to a minimum".
> How about database backup every day and log backup at least every hour. Or log backups every 10
> minutes? The more frequent you do log backup, the less impact is will have each time it is executed
> (even though the impact is low to begin with, especially if you have separated log/data/backup
> disks). For a 2 GB database, I probably wouldn't do differential backups, unless you have compelling
> advantages to do so.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
> news:E2D4C305-A7A2-4651-A4DB-CE04E247F5FB@.microsoft.com...
> >I understand most things concerning making a backup, but I cannot seem to
> > figure out what a sensible schedule is for making a backup. I would love to
> > figure this out before we go live in a few months.
> >
> > The database will have a reasonable mutation rate, i.e. around 500 data
> > changes per day and work-loss must be kept to a minimum. We expect the size
> > to be around 2GB. The schedule I have made now is as follows:
> >
> > Full Backup on Sunday at 02:00
> > Differential Backups on other weekdays at 02:00
> > Transaction Log Backups every 4 hours every day
> > Clean History 4 weeks old files every week on Sunday at 04:00
> >
> > I do not know if this makes sense. I do not know if the log backup proces
> > will hinder the users or not. I do know there are many many variables that
> > play a role in making a good backup procedure, but I would greatly appreciate
> > any insight concerning correct scheduling. TIA!

[SQL2005] Backup Schedule

I understand most things concerning making a backup, but I cannot seem to
figure out what a sensible schedule is for making a backup. I would love to
figure this out before we go live in a few months.
The database will have a reasonable mutation rate, i.e. around 500 data
changes per day and work-loss must be kept to a minimum. We expect the size
to be around 2GB. The schedule I have made now is as follows:
Full Backup on Sunday at 02:00
Differential Backups on other weekdays at 02:00
Transaction Log Backups every 4 hours every day
Clean History 4 weeks old files every week on Sunday at 04:00
I do not know if this makes sense. I do not know if the log backup proces
will hinder the users or not. I do know there are many many variables that
play a role in making a good backup procedure, but I would greatly appreciat
e
any insight concerning correct scheduling. TIA!I would definitely change things a bit, considering "work-loss must be kept
to a minimum".
How about database backup every day and log backup at least every hour. Or l
og backups every 10
minutes? The more frequent you do log backup, the less impact is will have e
ach time it is executed
(even though the impact is low to begin with, especially if you have separat
ed log/data/backup
disks). For a 2 GB database, I probably wouldn't do differential backups, un
less you have compelling
advantages to do so.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
news:E2D4C305-A7A2-4651-A4DB-CE04E247F5FB@.microsoft.com...
>I understand most things concerning making a backup, but I cannot seem to
> figure out what a sensible schedule is for making a backup. I would love t
o
> figure this out before we go live in a few months.
> The database will have a reasonable mutation rate, i.e. around 500 data
> changes per day and work-loss must be kept to a minimum. We expect the siz
e
> to be around 2GB. The schedule I have made now is as follows:
> Full Backup on Sunday at 02:00
> Differential Backups on other weekdays at 02:00
> Transaction Log Backups every 4 hours every day
> Clean History 4 weeks old files every week on Sunday at 04:00
> I do not know if this makes sense. I do not know if the log backup proces
> will hinder the users or not. I do know there are many many variables that
> play a role in making a good backup procedure, but I would greatly appreci
ate
> any insight concerning correct scheduling. TIA!|||I agree with Tibor. Although I'm sure useful in some scenarios, we
don't use differentials at all. Fulls once per day, and then logs
every hour to two hours, is what we do. It's easier to manage the
files, and we can keep track in our head which files exactly we need in
the case of restore. And we're in the same boat - "work-loss must be
kept to a minimum".
Tibor Karaszi wrote:[vbcol=seagreen]
> I would definitely change things a bit, considering "work-loss must be kep
t to a minimum".
> How about database backup every day and log backup at least every hour. Or
log backups every 10
> minutes? The more frequent you do log backup, the less impact is will have
each time it is executed
> (even though the impact is low to begin with, especially if you have separ
ated log/data/backup
> disks). For a 2 GB database, I probably wouldn't do differential backups,
unless you have compelling
> advantages to do so.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
> news:E2D4C305-A7A2-4651-A4DB-CE04E247F5FB@.microsoft.com...

[SQL2005] Backup Schedule

I understand most things concerning making a backup, but I cannot seem to
figure out what a sensible schedule is for making a backup. I would love to
figure this out before we go live in a few months.
The database will have a reasonable mutation rate, i.e. around 500 data
changes per day and work-loss must be kept to a minimum. We expect the size
to be around 2GB. The schedule I have made now is as follows:
Full Backup on Sunday at 02:00
Differential Backups on other weekdays at 02:00
Transaction Log Backups every 4 hours every day
Clean History 4 weeks old files every week on Sunday at 04:00
I do not know if this makes sense. I do not know if the log backup proces
will hinder the users or not. I do know there are many many variables that
play a role in making a good backup procedure, but I would greatly appreciate
any insight concerning correct scheduling. TIA!
I would definitely change things a bit, considering "work-loss must be kept to a minimum".
How about database backup every day and log backup at least every hour. Or log backups every 10
minutes? The more frequent you do log backup, the less impact is will have each time it is executed
(even though the impact is low to begin with, especially if you have separated log/data/backup
disks). For a 2 GB database, I probably wouldn't do differential backups, unless you have compelling
advantages to do so.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
news:E2D4C305-A7A2-4651-A4DB-CE04E247F5FB@.microsoft.com...
>I understand most things concerning making a backup, but I cannot seem to
> figure out what a sensible schedule is for making a backup. I would love to
> figure this out before we go live in a few months.
> The database will have a reasonable mutation rate, i.e. around 500 data
> changes per day and work-loss must be kept to a minimum. We expect the size
> to be around 2GB. The schedule I have made now is as follows:
> Full Backup on Sunday at 02:00
> Differential Backups on other weekdays at 02:00
> Transaction Log Backups every 4 hours every day
> Clean History 4 weeks old files every week on Sunday at 04:00
> I do not know if this makes sense. I do not know if the log backup proces
> will hinder the users or not. I do know there are many many variables that
> play a role in making a good backup procedure, but I would greatly appreciate
> any insight concerning correct scheduling. TIA!
|||I agree with Tibor. Although I'm sure useful in some scenarios, we
don't use differentials at all. Fulls once per day, and then logs
every hour to two hours, is what we do. It's easier to manage the
files, and we can keep track in our head which files exactly we need in
the case of restore. And we're in the same boat - "work-loss must be
kept to a minimum".
Tibor Karaszi wrote:[vbcol=seagreen]
> I would definitely change things a bit, considering "work-loss must be kept to a minimum".
> How about database backup every day and log backup at least every hour. Or log backups every 10
> minutes? The more frequent you do log backup, the less impact is will have each time it is executed
> (even though the impact is low to begin with, especially if you have separated log/data/backup
> disks). For a 2 GB database, I probably wouldn't do differential backups, unless you have compelling
> advantages to do so.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
> news:E2D4C305-A7A2-4651-A4DB-CE04E247F5FB@.microsoft.com...

Sunday, March 11, 2012

[SQL 2005] Restore question

I trying to test my restore in SQL 2005. I created a DB called test_restore
and try to restore from backup but getting this error. I have tried using
"overwrite option" but the same. I also tried T-SQL statement with MOVE and
also tried using read-only DB but still the same .
Any idea?
*************************************************
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing
*****************************************************Are you trying to overwite a existing database or a new one?
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:
> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> *****************************************************|||Hi i didnt read your question correctly...
I got the same error while trying to resotre to a created DB. So i tried
creating on a non existing DB it worked, try that way. Iam still checking on
how to overwrite the existing DB...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:
> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> *****************************************************|||Yes, got the answer :)
Try this...
RESTORE DATABASE Test
FROM DISK = 'D:\Adv_Test.Bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
STATS = 1, REPLACE
GO
REPLACE is the key word while trying to overwrite existing DB.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:
> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> *****************************************************|||hi Sreejit,
Thank you
Yes, i wanted to test the restore into another temporary db to test it.
Are you suggesting not to create the tempdb first and run the command below
straightaway? How does the db got created then?
By the way, i am restoring files and filegroups.Will this method work? Or,
do i need to specify all the filegroups names? There are long list..Any
better way? :-s
"Sreejith G" wrote:
> Yes, got the answer :)
> Try this...
>
> RESTORE DATABASE Test
> FROM DISK = 'D:\Adv_Test.Bak'
> WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
> STATS = 1, REPLACE
> GO
> REPLACE is the key word while trying to overwrite existing DB.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "rupart" wrote:
> > I trying to test my restore in SQL 2005. I created a DB called test_restore
> > and try to restore from backup but getting this error. I have tried using
> > "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> > also tried using read-only DB but still the same .
> > Any idea?
> >
> > *************************************************
> > Msg 3154, Level 16, State 4, Line 1
> > The backup set holds a backup of a database other than the existing
> > *****************************************************|||rupart wrote:
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
A RESTORE will create the database if it's not already there, so there
are no need to create it before a restore. The REPLACE option, is to
tell SQL server to overwrite an existing database with the same name as
the one that has been backed up. This is to prevent you from overwriting
a database by accident. You can read up on this in Books On Line.
Regards
Steen|||hi guys,
thx a lot for your replies. I find out a few things:
1) Using GUI, to restore, click on "Database" after Instance Name, right
click, restore database or restore files and filegroup. Then, enter your db
name(or you choose your test db that you have created from drop down).
Choosing the test db directly and attempting to restore will give you error.
2) Using T-SQL.(files and filegrp to test db)
************************************************
USE master
Go
RESTORE DATABASE TestRestore
FROM DISK = 'X:\Test.bak'
WITH RECOVERY,
MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
GO
************************************************
"rupart" wrote:
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
> "Sreejith G" wrote:
> > Yes, got the answer :)
> >
> > Try this...
> >
> >
> >
> > RESTORE DATABASE Test
> > FROM DISK = 'D:\Adv_Test.Bak'
> > WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL.1\MSSQL\DATA\Test.mdf',
> > MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
> > STATS = 1, REPLACE
> > GO
> >
> > REPLACE is the key word while trying to overwrite existing DB.
> >
> > --
> > Thanks,
> > Sree
> > [Please specify the version of Sql Server as we can save one thread and time
> > asking back if its 2000 or 2005]
> >
> >
> >
> > "rupart" wrote:
> >
> > > I trying to test my restore in SQL 2005. I created a DB called test_restore
> > > and try to restore from backup but getting this error. I have tried using
> > > "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> > > also tried using read-only DB but still the same .
> > > Any idea?
> > >
> > > *************************************************
> > > Msg 3154, Level 16, State 4, Line 1
> > > The backup set holds a backup of a database other than the existing
> > > *****************************************************|||rupart wrote:
> hi guys,
> thx a lot for your replies. I find out a few things:
> 1) Using GUI, to restore, click on "Database" after Instance Name, right
> click, restore database or restore files and filegroup. Then, enter your db
> name(or you choose your test db that you have created from drop down).
> Choosing the test db directly and attempting to restore will give you error.
> 2) Using T-SQL.(files and filegrp to test db)
> ************************************************
> USE master
> Go
> RESTORE DATABASE TestRestore
> FROM DISK = 'X:\Test.bak'
> WITH RECOVERY,
> MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
> MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
> MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
> MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
> GO
> ************************************************
>
Does this means that you've got it working or do you still have
problems? Your T-SQL will fail if the TestRestore database already
exists since you haven't supplied the REPLACE option.
Regards
Steen

[SQL 2005] Restore question

Try this...
RESTORE DATABASE Test
FROM DISK = 'D:\Adv_Test.Bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
STATS = 1, REPLACE
GO
REPLACE is the key word while trying to overwrite existing DB.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"rupart" wrote:

> I trying to test my restore in SQL 2005. I created a DB called test_restor
e
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE an
d
> also tried using read-only DB but still the same .
> Any idea?
> ****************************************
*********
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> ****************************************
*************|||hi Sreejit,
Thank you
Yes, i wanted to test the restore into another temporary db to test it.
Are you suggesting not to create the tempdb first and run the command below
straightaway? How does the db got created then?
By the way, i am restoring files and filegroups.Will this method work? Or,
do i need to specify all the filegroups names? There are long list..Any
better way? :-s
"Sreejith G" wrote:
[vbcol=seagreen]
> Yes, got the answer

> Try this...
>
> RESTORE DATABASE Test
> FROM DISK = 'D:\Adv_Test.Bak'
> WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
> STATS = 1, REPLACE
> GO
> REPLACE is the key word while trying to overwrite existing DB.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "rupart" wrote:
>|||rupart wrote:
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command belo
w
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
A RESTORE will create the database if it's not already there, so there
are no need to create it before a restore. The REPLACE option, is to
tell SQL server to overwrite an existing database with the same name as
the one that has been backed up. This is to prevent you from overwriting
a database by accident. You can read up on this in Books On Line.
Regards
Steen|||hi guys,
thx a lot for your replies. I find out a few things:
1) Using GUI, to restore, click on "Database" after Instance Name, right
click, restore database or restore files and filegroup. Then, enter your db
name(or you choose your test db that you have created from drop down).
Choosing the test db directly and attempting to restore will give you error.
2) Using T-SQL.(files and filegrp to test db)
****************************************
********
USE master
Go
RESTORE DATABASE TestRestore
FROM DISK = 'X:\Test.bak'
WITH RECOVERY,
MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
GO
****************************************
********
"rupart" wrote:
[vbcol=seagreen]
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command belo
w
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
> "Sreejith G" wrote:
>|||rupart wrote:
> hi guys,
> thx a lot for your replies. I find out a few things:
> 1) Using GUI, to restore, click on "Database" after Instance Name, right
> click, restore database or restore files and filegroup. Then, enter your d
b
> name(or you choose your test db that you have created from drop down).
> Choosing the test db directly and attempting to restore will give you erro
r.
> 2) Using T-SQL.(files and filegrp to test db)
> ****************************************
********
> USE master
> Go
> RESTORE DATABASE TestRestore
> FROM DISK = 'X:\Test.bak'
> WITH RECOVERY,
> MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
> MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
> MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
> MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
> GO
> ****************************************
********
>
Does this means that you've got it working or do you still have
problems? Your T-SQL will fail if the TestRestore database already
exists since you haven't supplied the REPLACE option.
Regards
Steen

[SQL 2005] Restore question

Try this...
RESTORE DATABASE Test
FROM DISK = 'D:\Adv_Test.Bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
STATS = 1, REPLACE
GO
REPLACE is the key word while trying to overwrite existing DB.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"rupart" wrote:

> I trying to test my restore in SQL 2005. I created a DB called test_restore
> and try to restore from backup but getting this error. I have tried using
> "overwrite option" but the same. I also tried T-SQL statement with MOVE and
> also tried using read-only DB but still the same .
> Any idea?
> *************************************************
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing
> ************************************************** ***
|||hi Sreejit,
Thank you
Yes, i wanted to test the restore into another temporary db to test it.
Are you suggesting not to create the tempdb first and run the command below
straightaway? How does the db got created then?
By the way, i am restoring files and filegroups.Will this method work? Or,
do i need to specify all the filegroups names? There are long list..Any
better way? :-s
"Sreejith G" wrote:
[vbcol=seagreen]
> Yes, got the answer

> Try this...
>
> RESTORE DATABASE Test
> FROM DISK = 'D:\Adv_Test.Bak'
> WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\Test_log.ldf',
> STATS = 1, REPLACE
> GO
> REPLACE is the key word while trying to overwrite existing DB.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "rupart" wrote:
|||rupart wrote:
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
A RESTORE will create the database if it's not already there, so there
are no need to create it before a restore. The REPLACE option, is to
tell SQL server to overwrite an existing database with the same name as
the one that has been backed up. This is to prevent you from overwriting
a database by accident. You can read up on this in Books On Line.
Regards
Steen
|||hi guys,
thx a lot for your replies. I find out a few things:
1) Using GUI, to restore, click on "Database" after Instance Name, right
click, restore database or restore files and filegroup. Then, enter your db
name(or you choose your test db that you have created from drop down).
Choosing the test db directly and attempting to restore will give you error.
2) Using T-SQL.(files and filegrp to test db)
************************************************
USE master
Go
RESTORE DATABASE TestRestore
FROM DISK = 'X:\Test.bak'
WITH RECOVERY,
MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
GO
************************************************
"rupart" wrote:
[vbcol=seagreen]
> hi Sreejit,
> Thank you
> Yes, i wanted to test the restore into another temporary db to test it.
> Are you suggesting not to create the tempdb first and run the command below
> straightaway? How does the db got created then?
> By the way, i am restoring files and filegroups.Will this method work? Or,
> do i need to specify all the filegroups names? There are long list..Any
> better way? :-s
> "Sreejith G" wrote:
|||rupart wrote:
> hi guys,
> thx a lot for your replies. I find out a few things:
> 1) Using GUI, to restore, click on "Database" after Instance Name, right
> click, restore database or restore files and filegroup. Then, enter your db
> name(or you choose your test db that you have created from drop down).
> Choosing the test db directly and attempting to restore will give you error.
> 2) Using T-SQL.(files and filegrp to test db)
> ************************************************
> USE master
> Go
> RESTORE DATABASE TestRestore
> FROM DISK = 'X:\Test.bak'
> WITH RECOVERY,
> MOVE 'ABC_PROD' TO 'X:\ABC_PROD.mdf',
> MOVE 'ABC_1' TO 'X:\ABC_1.ndf',
> MOVE 'ABC_2' TO 'X:\ABC_2.ndf',
> MOVE 'ABC_log' TO 'X:\ABC_log.ldf'
> GO
> ************************************************
>
Does this means that you've got it working or do you still have
problems? Your T-SQL will fail if the TestRestore database already
exists since you haven't supplied the REPLACE option.
Regards
Steen

[SQL 2005] backup Diff on MSDB doesnt work

Hello all,

I need help concerning a differential backup on a MSDB database.

I received this message when I tried to backup it

Log from Windows

Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/1/2007
Time: 2:03:37 PM
User: N/A
Computer: XXX

Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=9028. Thread=5928. Client. Instance=. VD=Global\Data Protector_(DEFAULT)_msdb_14_00_21.

Log from DataProtector

Normal] From: XXX "(DEFAULT)" Time: XXX
SQL statement:
BACKUP DATABASE [msdb] TO
VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14"
WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536;

[Warning] From: XXX "(DEFAULT)" Time: XXX
Error has occurred while executing a SQL statement.
Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb>
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'

Ive tried to do a full followed straight after by a diff but doent help.

Thank you for your help

yes you can take differential but for master you can take only full backup ..........whats in the error log ? ? ? you need to take a full backup and then subsequently differential backup

|||

Yes we can, I do it for many servers

|||

The error is pretty explicit about what the problem is.

A differential backup is defined as the set of changes SINCE THE LAST FULL BACKUP.

If you've never done a full backup, there is nothing to base the differential on and it won't work.

Do a full backup of MSDB, and then your differentials should work.

|||

Hi thank you to replay.

That what I do, a full and after a diff, that why I don't understand why I doesnt find any base

Thank you

|||

Is the database recovery set to FULL? Simple won't do it (it actually reset the status field needed).

|||

FULL recovery model applies to log backups, not to differential backups.

In fact, there is no way to clear the differential base once it is set.

What you're attempting to do should work just fine.

Please try it from Management Studio query window and paste the commands and response.

Thanks,

Kevin

|||

Kevin Farlee wrote:

FULL recovery model applies to log backups, not to differential backups.

In fact, there is no way to clear the differential base once it is set.

Thanks for the correction. Somehow, I was thinking the OP wants to backup log for msdb (you can't 'cuz it's simple). Duh!

[SQL 2005] backup Diff on MSDB doesnt work

Hello all,

I need help concerning a differential backup on a MSDB database.

I received this message when I tried to backup it

Log from Windows

Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/1/2007
Time: 2:03:37 PM
User: N/A
Computer: XXX

Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=9028. Thread=5928. Client. Instance=. VD=Global\Data Protector_(DEFAULT)_msdb_14_00_21.

Log from DataProtector

Ive tried to do a full followed straight after by a diff but doent help.

Thank you for your help

yes you can take differential but for master you can take only full backup ..........whats in the error log ? ? ? you need to take a full backup and then subsequently differential backup

|||

Yes we can, I do it for many servers

|||

The error is pretty explicit about what the problem is.

A differential backup is defined as the set of changes SINCE THE LAST FULL BACKUP.

If you've never done a full backup, there is nothing to base the differential on and it won't work.

Do a full backup of MSDB, and then your differentials should work.

|||

Hi thank you to replay.

That what I do, a full and after a diff, that why I don't understand why I doesnt find any base

Thank you

|||

Is the database recovery set to FULL? Simple won't do it (it actually reset the status field needed).

|||

FULL recovery model applies to log backups, not to differential backups.

In fact, there is no way to clear the differential base once it is set.

What you're attempting to do should work just fine.

Please try it from Management Studio query window and paste the commands and response.

Thanks,

Kevin

|||

Kevin Farlee wrote:

FULL recovery model applies to log backups, not to differential backups.

In fact, there is no way to clear the differential base once it is set.

Thanks for the correction. Somehow, I was thinking the OP wants to backup log for msdb (you can't 'cuz it's simple). Duh!

Tuesday, March 6, 2012

[MSSQL7] Backup on network drive

Hi there,
Does anyone know if it's possible to perform a MSSQL7 database backup on a network drive instead of the local drives ?
...and if yes, how to proceed ?
It seems to be possible under MSSQL2000.
Many thanks for your help in advance,
MattEach MS-SQL device must be know as a local disk to control the IO writes, and a dump device follow this rule|||many thanks for this answer ;o)|||Originally posted by fadace
Each MS-SQL device must be know as a local disk to control the IO writes, and a dump device follow this rule

It is not a true:

backup DATABASE database
to disk ='\\networkserver\BACKUP\database.bak'|||hu interresting, gonna have a try with this !! thanks !! :)

is there no read/write error risks ? I mean, if we exclude the network transmission problems, of course.|||Originally posted by mattoo
hu interresting, gonna have a try with this !! thanks !! :)

is there no read/write error risks ? I mean, if we exclude the network transmission problems, of course.

If someone cuts a network cable it should be a problem but no more.|||Fine. Many thanks again for your help ;)

Thursday, February 16, 2012

[AdventureWorks] database backup failed with error: 3013

SQL Server 2005
A couple nights ago I got several failures on my 3 AM database backups, my
program generated the message:
[AdventureWorks] database backup failed with error: 3013
on several databases, including: AdventureWorks, AdventureWorksDW, master,
model & msdb.
The error number was captured using @.@.ERROR.
When I saw the error, I did the following:
- Check drive space. Fine.
- Check Windows Event Log - no pertinant messages (particulary drive
space)
- Check SQL Server logs
BACKUP failed to complete the command BACKUP LOG msdb. Check the
backup application log for details
Error 3041, Severity 16, State 1
(repeats for several databases)
- Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
fine.
- Re-ran a full backup without any issues.
I know I will see error 3013 if I run out of space on a drive, but that
wasn't the case here.
The only thing "wrong" with this install is that the data and backups are
all on the E: drive and while the drive itself is fragmened, but with only
some minor exceptions, none of the .*df files are in the list.
Oh, and not that I think it matters, but AutoShrink is off on all DB's.
I would appreciate any assistance anyone can give helping me to understand
what may have gone wrong.
Thanks,
Jay
PS. I decided to do some resizing of datafiles and run a defrag.
Apparently something changed in the nightly backups so that it is
trying to backup the logs on those databases.
The databases you list all default to SIMPLE recovery mode. The
example you showed for msdb was a failure to backup the log. You can
not backup the log on a database in SIMPLE recovery mode.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Nov 2007 09:40:37 -0800, "Jay" <nospam@.nospam.org> wrote:

>SQL Server 2005
>A couple nights ago I got several failures on my 3 AM database backups, my
>program generated the message:
>[AdventureWorks] database backup failed with error: 3013
>on several databases, including: AdventureWorks, AdventureWorksDW, master,
>model & msdb.
>The error number was captured using @.@.ERROR.
>When I saw the error, I did the following:
> - Check drive space. Fine.
> - Check Windows Event Log - no pertinant messages (particulary drive
>space)
> - Check SQL Server logs
> BACKUP failed to complete the command BACKUP LOG msdb. Check the
>backup application log for details
> Error 3041, Severity 16, State 1
> (repeats for several databases)
> - Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
>fine.
> - Re-ran a full backup without any issues.
>I know I will see error 3013 if I run out of space on a drive, but that
>wasn't the case here.
>The only thing "wrong" with this install is that the data and backups are
>all on the E: drive and while the drive itself is fragmened, but with only
>some minor exceptions, none of the .*df files are in the list.
>Oh, and not that I think it matters, but AutoShrink is off on all DB's.
>I would appreciate any assistance anyone can give helping me to understand
>what may have gone wrong.
>Thanks,
>Jay
>PS. I decided to do some resizing of datafiles and run a defrag.
>
|||You are correct sir.
I have been dealing with the difference between 2000 & 2005 on the issue of
backing up the transaction log for simple mode databases and had just
included extra error traps. I also have a version that won't try the simple
mode log backup on version 9.
I supose I just managed to confuse myself.
Thanks,
Jay
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:h4fmj392cv2vmstka6dkk07qfiovlvgsnu@.4ax.com... [vbcol=seagreen]
> Apparently something changed in the nightly backups so that it is
> trying to backup the logs on those databases.
> The databases you list all default to SIMPLE recovery mode. The
> example you showed for msdb was a failure to backup the log. You can
> not backup the log on a database in SIMPLE recovery mode.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 14 Nov 2007 09:40:37 -0800, "Jay" <nospam@.nospam.org> wrote:
|||To add to Roy's comments, I would recommend not backing up the
AdventureWorks sample databases at all. You can always reinstall or download
these later if you need to.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Jay" <nospam@.nospam.org> wrote in message
news:%238yk4VuJIHA.6108@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2005
> A couple nights ago I got several failures on my 3 AM database backups, my
> program generated the message:
> [AdventureWorks] database backup failed with error: 3013
> on several databases, including: AdventureWorks, AdventureWorksDW, master,
> model & msdb.
> The error number was captured using @.@.ERROR.
> When I saw the error, I did the following:
> - Check drive space. Fine.
> - Check Windows Event Log - no pertinant messages (particulary drive
> space)
> - Check SQL Server logs
> BACKUP failed to complete the command BACKUP LOG msdb. Check
> the backup application log for details
> Error 3041, Severity 16, State 1
> (repeats for several databases)
> - Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
> fine.
> - Re-ran a full backup without any issues.
> I know I will see error 3013 if I run out of space on a drive, but that
> wasn't the case here.
> The only thing "wrong" with this install is that the data and backups are
> all on the E: drive and while the drive itself is fragmened, but with only
> some minor exceptions, none of the .*df files are in the list.
> Oh, and not that I think it matters, but AutoShrink is off on all DB's.
> I would appreciate any assistance anyone can give helping me to understand
> what may have gone wrong.
> Thanks,
> Jay
> PS. I decided to do some resizing of datafiles and run a defrag.
>
|||Thanks, but no thanks. All databases get backed up.
Besides, I activly monitor these systems, so I'll know if space is an issue
before it becomes an issue.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%23m8HfIvJIHA.3820@.TK2MSFTNGP03.phx.gbl...
> To add to Roy's comments, I would recommend not backing up the
> AdventureWorks sample databases at all. You can always reinstall or
> download these later if you need to.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Jay" <nospam@.nospam.org> wrote in message
> news:%238yk4VuJIHA.6108@.TK2MSFTNGP03.phx.gbl...
>

[AdventureWorks] database backup failed with error: 3013

SQL Server 2005
A couple nights ago I got several failures on my 3 AM database backups, my
program generated the message:
[AdventureWorks] database backup failed with error: 3013
on several databases, including: AdventureWorks, AdventureWorksDW, master,
model & msdb.
The error number was captured using @.@.ERROR.
When I saw the error, I did the following:
- Check drive space. Fine.
- Check Windows Event Log - no pertinant messages (particulary drive
space)
- Check SQL Server logs
BACKUP failed to complete the command BACKUP LOG msdb. Check the
backup application log for details
Error 3041, Severity 16, State 1
(repeats for several databases)
- Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
fine.
- Re-ran a full backup without any issues.
I know I will see error 3013 if I run out of space on a drive, but that
wasn't the case here.
The only thing "wrong" with this install is that the data and backups are
all on the E: drive and while the drive itself is fragmened, but with only
some minor exceptions, none of the .*df files are in the list.
Oh, and not that I think it matters, but AutoShrink is off on all DB's.
I would appreciate any assistance anyone can give helping me to understand
what may have gone wrong.
Thanks,
Jay
PS. I decided to do some resizing of datafiles and run a defrag.Apparently something changed in the nightly backups so that it is
trying to backup the logs on those databases.
The databases you list all default to SIMPLE recovery mode. The
example you showed for msdb was a failure to backup the log. You can
not backup the log on a database in SIMPLE recovery mode.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Nov 2007 09:40:37 -0800, "Jay" <nospam@.nospam.org> wrote:
>SQL Server 2005
>A couple nights ago I got several failures on my 3 AM database backups, my
>program generated the message:
>[AdventureWorks] database backup failed with error: 3013
>on several databases, including: AdventureWorks, AdventureWorksDW, master,
>model & msdb.
>The error number was captured using @.@.ERROR.
>When I saw the error, I did the following:
> - Check drive space. Fine.
> - Check Windows Event Log - no pertinant messages (particulary drive
>space)
> - Check SQL Server logs
> BACKUP failed to complete the command BACKUP LOG msdb. Check the
>backup application log for details
> Error 3041, Severity 16, State 1
> (repeats for several databases)
> - Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
>fine.
> - Re-ran a full backup without any issues.
>I know I will see error 3013 if I run out of space on a drive, but that
>wasn't the case here.
>The only thing "wrong" with this install is that the data and backups are
>all on the E: drive and while the drive itself is fragmened, but with only
>some minor exceptions, none of the .*df files are in the list.
>Oh, and not that I think it matters, but AutoShrink is off on all DB's.
>I would appreciate any assistance anyone can give helping me to understand
>what may have gone wrong.
>Thanks,
>Jay
>PS. I decided to do some resizing of datafiles and run a defrag.
>|||You are correct sir.
I have been dealing with the difference between 2000 & 2005 on the issue of
backing up the transaction log for simple mode databases and had just
included extra error traps. I also have a version that won't try the simple
mode log backup on version 9.
I supose I just managed to confuse myself.
Thanks,
Jay
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:h4fmj392cv2vmstka6dkk07qfiovlvgsnu@.4ax.com...
> Apparently something changed in the nightly backups so that it is
> trying to backup the logs on those databases.
> The databases you list all default to SIMPLE recovery mode. The
> example you showed for msdb was a failure to backup the log. You can
> not backup the log on a database in SIMPLE recovery mode.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 14 Nov 2007 09:40:37 -0800, "Jay" <nospam@.nospam.org> wrote:
>>SQL Server 2005
>>A couple nights ago I got several failures on my 3 AM database backups, my
>>program generated the message:
>>[AdventureWorks] database backup failed with error: 3013
>>on several databases, including: AdventureWorks, AdventureWorksDW, master,
>>model & msdb.
>>The error number was captured using @.@.ERROR.
>>When I saw the error, I did the following:
>> - Check drive space. Fine.
>> - Check Windows Event Log - no pertinant messages (particulary drive
>>space)
>> - Check SQL Server logs
>> BACKUP failed to complete the command BACKUP LOG msdb. Check
>> the
>>backup application log for details
>> Error 3041, Severity 16, State 1
>> (repeats for several databases)
>> - Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
>>fine.
>> - Re-ran a full backup without any issues.
>>I know I will see error 3013 if I run out of space on a drive, but that
>>wasn't the case here.
>>The only thing "wrong" with this install is that the data and backups are
>>all on the E: drive and while the drive itself is fragmened, but with only
>>some minor exceptions, none of the .*df files are in the list.
>>Oh, and not that I think it matters, but AutoShrink is off on all DB's.
>>I would appreciate any assistance anyone can give helping me to understand
>>what may have gone wrong.
>>Thanks,
>>Jay
>>PS. I decided to do some resizing of datafiles and run a defrag.|||To add to Roy's comments, I would recommend not backing up the
AdventureWorks sample databases at all. You can always reinstall or download
these later if you need to.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Jay" <nospam@.nospam.org> wrote in message
news:%238yk4VuJIHA.6108@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2005
> A couple nights ago I got several failures on my 3 AM database backups, my
> program generated the message:
> [AdventureWorks] database backup failed with error: 3013
> on several databases, including: AdventureWorks, AdventureWorksDW, master,
> model & msdb.
> The error number was captured using @.@.ERROR.
> When I saw the error, I did the following:
> - Check drive space. Fine.
> - Check Windows Event Log - no pertinant messages (particulary drive
> space)
> - Check SQL Server logs
> BACKUP failed to complete the command BACKUP LOG msdb. Check
> the backup application log for details
> Error 3041, Severity 16, State 1
> (repeats for several databases)
> - Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
> fine.
> - Re-ran a full backup without any issues.
> I know I will see error 3013 if I run out of space on a drive, but that
> wasn't the case here.
> The only thing "wrong" with this install is that the data and backups are
> all on the E: drive and while the drive itself is fragmened, but with only
> some minor exceptions, none of the .*df files are in the list.
> Oh, and not that I think it matters, but AutoShrink is off on all DB's.
> I would appreciate any assistance anyone can give helping me to understand
> what may have gone wrong.
> Thanks,
> Jay
> PS. I decided to do some resizing of datafiles and run a defrag.
>|||Thanks, but no thanks. All databases get backed up.
Besides, I activly monitor these systems, so I'll know if space is an issue
before it becomes an issue.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%23m8HfIvJIHA.3820@.TK2MSFTNGP03.phx.gbl...
> To add to Roy's comments, I would recommend not backing up the
> AdventureWorks sample databases at all. You can always reinstall or
> download these later if you need to.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Jay" <nospam@.nospam.org> wrote in message
> news:%238yk4VuJIHA.6108@.TK2MSFTNGP03.phx.gbl...
>> SQL Server 2005
>> A couple nights ago I got several failures on my 3 AM database backups,
>> my program generated the message:
>> [AdventureWorks] database backup failed with error: 3013
>> on several databases, including: AdventureWorks, AdventureWorksDW,
>> master, model & msdb.
>> The error number was captured using @.@.ERROR.
>> When I saw the error, I did the following:
>> - Check drive space. Fine.
>> - Check Windows Event Log - no pertinant messages (particulary drive
>> space)
>> - Check SQL Server logs
>> BACKUP failed to complete the command BACKUP LOG msdb. Check
>> the backup application log for details
>> Error 3041, Severity 16, State 1
>> (repeats for several databases)
>> - Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
>> fine.
>> - Re-ran a full backup without any issues.
>> I know I will see error 3013 if I run out of space on a drive, but that
>> wasn't the case here.
>> The only thing "wrong" with this install is that the data and backups are
>> all on the E: drive and while the drive itself is fragmened, but with
>> only some minor exceptions, none of the .*df files are in the list.
>> Oh, and not that I think it matters, but AutoShrink is off on all DB's.
>> I would appreciate any assistance anyone can give helping me to
>> understand what may have gone wrong.
>> Thanks,
>> Jay
>> PS. I decided to do some resizing of datafiles and run a defrag.
>

[AdventureWorks] database backup failed with error: 3013

SQL Server 2005
A couple nights ago I got several failures on my 3 AM database backups, my
program generated the message:
[AdventureWorks] database backup failed with error: 3013
on several databases, including: AdventureWorks, AdventureWorksDW, master,
model & msdb.
The error number was captured using @.@.ERROR.
When I saw the error, I did the following:
- Check drive space. Fine.
- Check Windows Event Log - no pertinant messages (particulary drive
space)
- Check SQL Server logs
BACKUP failed to complete the command BACKUP LOG msdb. Check the
backup application log for details
Error 3041, Severity 16, State 1
(repeats for several databases)
- Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
fine.
- Re-ran a full backup without any issues.
I know I will see error 3013 if I run out of space on a drive, but that
wasn't the case here.
The only thing "wrong" with this install is that the data and backups are
all on the E: drive and while the drive itself is fragmened, but with only
some minor exceptions, none of the .*df files are in the list.
Oh, and not that I think it matters, but AutoShrink is off on all DB's.
I would appreciate any assistance anyone can give helping me to understand
what may have gone wrong.
Thanks,
Jay
PS. I decided to do some resizing of datafiles and run a defrag.Apparently something changed in the nightly backups so that it is
trying to backup the logs on those databases.
The databases you list all default to SIMPLE recovery mode. The
example you showed for msdb was a failure to backup the log. You can
not backup the log on a database in SIMPLE recovery mode.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Nov 2007 09:40:37 -0800, "Jay" <nospam@.nospam.org> wrote:

>SQL Server 2005
>A couple nights ago I got several failures on my 3 AM database backups, my
>program generated the message:
>[AdventureWorks] database backup failed with error: 3013
>on several databases, including: AdventureWorks, AdventureWorksDW, master,
>model & msdb.
>The error number was captured using @.@.ERROR.
>When I saw the error, I did the following:
> - Check drive space. Fine.
> - Check Windows Event Log - no pertinant messages (particulary drive
>space)
> - Check SQL Server logs
> BACKUP failed to complete the command BACKUP LOG msdb. Check th
e
>backup application log for details
> Error 3041, Severity 16, State 1
> (repeats for several databases)
> - Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
>fine.
> - Re-ran a full backup without any issues.
>I know I will see error 3013 if I run out of space on a drive, but that
>wasn't the case here.
>The only thing "wrong" with this install is that the data and backups are
>all on the E: drive and while the drive itself is fragmened, but with only
>some minor exceptions, none of the .*df files are in the list.
>Oh, and not that I think it matters, but AutoShrink is off on all DB's.
>I would appreciate any assistance anyone can give helping me to understand
>what may have gone wrong.
>Thanks,
>Jay
>PS. I decided to do some resizing of datafiles and run a defrag.
>|||You are correct sir.
I have been dealing with the difference between 2000 & 2005 on the issue of
backing up the transaction log for simple mode databases and had just
included extra error traps. I also have a version that won't try the simple
mode log backup on version 9.
I supose I just managed to confuse myself.
Thanks,
Jay
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:h4fmj392cv2vmstka6dkk07qfiovlvgsnu@.
4ax.com...[vbcol=seagreen]
> Apparently something changed in the nightly backups so that it is
> trying to backup the logs on those databases.
> The databases you list all default to SIMPLE recovery mode. The
> example you showed for msdb was a failure to backup the log. You can
> not backup the log on a database in SIMPLE recovery mode.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 14 Nov 2007 09:40:37 -0800, "Jay" <nospam@.nospam.org> wrote:
>|||To add to Roy's comments, I would recommend not backing up the
AdventureWorks sample databases at all. You can always reinstall or download
these later if you need to.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Jay" <nospam@.nospam.org> wrote in message
news:%238yk4VuJIHA.6108@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2005
> A couple nights ago I got several failures on my 3 AM database backups, my
> program generated the message:
> [AdventureWorks] database backup failed with error: 3013
> on several databases, including: AdventureWorks, AdventureWorksDW, master,
> model & msdb.
> The error number was captured using @.@.ERROR.
> When I saw the error, I did the following:
> - Check drive space. Fine.
> - Check Windows Event Log - no pertinant messages (particulary drive
> space)
> - Check SQL Server logs
> BACKUP failed to complete the command BACKUP LOG msdb. Check
> the backup application log for details
> Error 3041, Severity 16, State 1
> (repeats for several databases)
> - Ran DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB and all were
> fine.
> - Re-ran a full backup without any issues.
> I know I will see error 3013 if I run out of space on a drive, but that
> wasn't the case here.
> The only thing "wrong" with this install is that the data and backups are
> all on the E: drive and while the drive itself is fragmened, but with only
> some minor exceptions, none of the .*df files are in the list.
> Oh, and not that I think it matters, but AutoShrink is off on all DB's.
> I would appreciate any assistance anyone can give helping me to understand
> what may have gone wrong.
> Thanks,
> Jay
> PS. I decided to do some resizing of datafiles and run a defrag.
>|||Thanks, but no thanks. All databases get backed up.
Besides, I activly monitor these systems, so I'll know if space is an issue
before it becomes an issue.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%23m8HfIvJIHA.3820@.TK2MSFTNGP03.phx.gbl...
> To add to Roy's comments, I would recommend not backing up the
> AdventureWorks sample databases at all. You can always reinstall or
> download these later if you need to.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/...r/bb428874.aspx
> "Jay" <nospam@.nospam.org> wrote in message
> news:%238yk4VuJIHA.6108@.TK2MSFTNGP03.phx.gbl...
>

Monday, February 13, 2012

[2005 Express]Add SPs to a Schema?

I have SQL Server 2005 Express set up on a PC. I imported a backup of an MSDE database. All of the database objects are there. However, I need to assign permissions to my Stored Procedures. So, using Management Studio Express, I saw absolutely no way of adding a stored procedure to a schema or to a user. So I downloaded SQL Server 2005 Developer from my MSDN account. I installed the client tools to get the full version of Management Studio. Same thing. No way to add permissions to a SP. Some web articles say to right-click the SP and select Properties. However, there is no "Properties" in the right-click menu!!! There is only Rename, Delete, Modify, etc. How can something so easy with an old product be so difficult with a new one? Does anyone know how to accomplish adding permissions to SPs?

I can right-click a table or a View and get the popup window with the list view and contents to set permissions to tables and views.On my build of SSMS, I do have a properties context menu off of any sproc in OE, and when I click it I can add perms to the sproc. I have this build: 9.00.2047.00. which do you have?

I am thinking perhaps this is due to a bad reaction between SSMS and SSMSE perhaps. EDIT: Is that redundant? :)

If nothig else works, I would try unistalling them both and the reistalling only SSMS.|||I have build 9.00.1399.00, so it is an older version. This build was from MSDN Subscription Downloads, on Disk 2 for SQL Server 2005 Developer. Do you know where I could download an update/the latest version?

UPDATE: I uninstalled and then reinstalled all of the SQL Server Workstation stuff. Then, I installed an instance of SQL Server 2005 Developer on my test PC. Now, with SSMS, I can right-click and get "Properties" for stored Procedures in the SQL Server 2005 instance, but I still can't get the right-click "Properties" in the Express instance. Is this a limitation of Express?|||Yes, I think this is a limitation of ssmse.

The sql express GUI is very limited in what it does. it's meant to be a teaser to make it more attractive for people to start using sql server instead of mysql, but then upgrade to a pay version once they are addicted. :)

EDIT: I am running sp1 - I think that's the difference between our two version numbers. You can download sp1 from here: http://www.microsoft.com/sql/sp1.mspx|||Oh, I see. Even the Management Studio updates are included in the SQL Server service pack, huh? I'll have to apply that. Thanks.|||oh yea. UI devs put bugs in their code too. :)|||Well, overall, I can get the Express instance to run the SPs from my VB.NET code. I used ALTER to put them into a new schema, and gave the user account that connects from my intranet app permissions on that schema. This allows the app to run the SPs. It's odd, what use is setting permissions on the specific SP (in the Developer instance, or course) when just using the schema allows execution? Is it to be more granular with permissions and, say, deny permissions on a specific SP within a schema to a schema user?|||that's one of the points of using schemas.

note that an explicit deny on a sproc should override any indirect grant (thru a schema).

[?]Restore and Backup problem

I've a program with VB6 working with SQLServer 2000.
I have a Central Station with the program runnin with full prvileges and
that can perform all the actions on the DB.
There are also some clients that cannot be connected via LAN or internet
that need to use the same DB.
The way I solved this with Aces was to copy the Access file from the Central
Station ot the disconnected clients.
Then to update the central Staton I used to save the recordset of new data
entered from there onto files and then copy them into the Central Station.
Now I'm upgrading the program to run with SQLServer.
I can do everything but the Backup and restore of the DB.
Actually I perform the DB Backup to file with the command line

BACKUP DATABASE myDB TO DISK = 'TempFileName.bak'

This way I can save the file on a USB key or send it via email

Then I restore it on the disconnected clients using the command line

RESTORE DATABASE myDB FROM DISK ='TempFileName.bak' WITH REPLACE

Everything looks fine and seems to work, but on my testing machine I'm
having this strange behavior:

I create new datas into the DataBase, then I execute the backup and quit the
application.
I open EM and delete the just entered datas
I open the application simulating the disconnected clinet (just a string
option, the program is the same)
I execute the RESTORE
I expect hte cancelled datas to be back online, but...... the cancelled
datas is not restored.
I then open EM and execute a backup from the file I created with Backup and
..... the data goes in.
Why?
Is the problem raised only becouse the SQLServer is the same in both cases?

What I need is just to copy one DB and inserting it into a different Server.
Any idea why this is appening?
Any idea on how to do this operation?

I tryed already the RECOVERY, NORECOVERY and quite everything, but ......

Thanks in advance for your helpPerhaps you are inadvertently appending to the backup file instead of
overwriting. Try

BACKUP DATABASE myDB TO DISK = 'TempFileName.bak' WITH INIT

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Spieg" <spiegator@.tiscali.it> wrote in message
news:WgQUc.120173$OR2.6141961@.news3.tin.it...
> I've a program with VB6 working with SQLServer 2000.
> I have a Central Station with the program runnin with full prvileges and
> that can perform all the actions on the DB.
> There are also some clients that cannot be connected via LAN or internet
> that need to use the same DB.
> The way I solved this with Aces was to copy the Access file from the
Central
> Station ot the disconnected clients.
> Then to update the central Staton I used to save the recordset of new data
> entered from there onto files and then copy them into the Central Station.
> Now I'm upgrading the program to run with SQLServer.
> I can do everything but the Backup and restore of the DB.
> Actually I perform the DB Backup to file with the command line
> BACKUP DATABASE myDB TO DISK = 'TempFileName.bak'
> This way I can save the file on a USB key or send it via email
> Then I restore it on the disconnected clients using the command line
> RESTORE DATABASE myDB FROM DISK ='TempFileName.bak' WITH REPLACE
> Everything looks fine and seems to work, but on my testing machine I'm
> having this strange behavior:
> I create new datas into the DataBase, then I execute the backup and quit
the
> application.
> I open EM and delete the just entered datas
> I open the application simulating the disconnected clinet (just a string
> option, the program is the same)
> I execute the RESTORE
> I expect hte cancelled datas to be back online, but...... the cancelled
> datas is not restored.
> I then open EM and execute a backup from the file I created with Backup
and
> .... the data goes in.
> Why?
> Is the problem raised only becouse the SQLServer is the same in both
cases?
> What I need is just to copy one DB and inserting it into a different
Server.
> Any idea why this is appening?
> Any idea on how to do this operation?
> I tryed already the RECOVERY, NORECOVERY and quite everything, but ......
> Thanks in advance for your help|||Right, the WITH INIT Worked, damn I read it, but ...
Thanks

"Dan Guzman" <danguzman@.nospam-earthlink.net> ha scritto nel messaggio
news:c3UUc.6485$3O3.6087@.newsread2.news.pas.earthl ink.net...
> Perhaps you are inadvertently appending to the backup file instead of
> overwriting. Try
> BACKUP DATABASE myDB TO DISK = 'TempFileName.bak' WITH INIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP

Thursday, March 22, 2012

\Backup directories - remove safely?

\\.\Tape0 is not a vaild Microsoft Tape format backup set

Tuesday, March 20, 2012

\\.\Tape0 is not a vaild Microsoft Tape format backup set

[URGENT] Problem to Restore Database from backup

Monday, March 19, 2012

[SQL2005] Backup Schedule

[SQL2005] Backup Schedule

[SQL2005] Backup Schedule

Sunday, March 11, 2012

[SQL 2005] Restore question

[SQL 2005] Restore question

[SQL 2005] Restore question

[SQL 2005] backup Diff on MSDB doesnt work

[SQL 2005] backup Diff on MSDB doesnt work

Tuesday, March 6, 2012

[MSSQL7] Backup on network drive

Thursday, February 16, 2012

[AdventureWorks] database backup failed with error: 3013

[AdventureWorks] database backup failed with error: 3013

[AdventureWorks] database backup failed with error: 3013

Monday, February 13, 2012

[2005 Express]Add SPs to a Schema?

[?]Restore and Backup problem

sql rowcount

Blog Archive

About Me