Data Flow Task Problem: backup

Showing posts with label backup. Show all posts

Thursday, March 29, 2012

Data files & Transaction log recovery

How do I backup a data file & transaction log from one instance of SQL to a different sql server. I have done a complete backup and now I want to restore the database but on a different SQL server, but the *.mdf and *.ldf is missing, so how do I restore them also?When you did the full backup you created a backup file. When you restore
this the mdf and ldf will be created.
On the instance you want to restore to restore from the device. On the
second tab will be the location of the mdf and ldf - this will be the
file names from the other instance so you will have to overtype with
another filename or path.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||If you restore to another SQL server you also have to think about databaseusers. The users from the database you restore stil are in the DB when it's on the new server but you not see them. You can either run the drop user or use sp_change_users_login 'Auto_Fix', 'username', NULL. This is call ophran users
/Joel|||Thanks a million. I completed the backup and did the restore on the other instance of SQL server and it worked like a charm. You're a life saver!

Tuesday, March 27, 2012

Data File Offline

I have done a partial restore of a backup which leaves one of my data files
offline. I do not need this data file and would like to remove it from the
database. When I attempt to run ALTER DATABASE dbname REMOVE FILE filename I
recieve the following error:
"Cannot add, remove, or modify file 'filename' because it is offline."
Is there anyway to remove this file?
Thanks
You need to perform a couple of steps first.
1. Bring the datafile back online. I'm not sure what you mean by the file
is marked as offline.
2. Run DBCC SHRINKFILE with the EMPTYFILE option. This will remove data
from the file and keep SQL Server from placing data on the file.
3. Run the ALTER DATABASE command with the REMOVE FILE option.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"CraigA" <CraigA@.discussions.microsoft.com> wrote in message
news:AD111224-AD9F-463E-912C-1B168D223022@.microsoft.com...
> I have done a partial restore of a backup which leaves one of my data
files
> offline. I do not need this data file and would like to remove it from
the
> database. When I attempt to run ALTER DATABASE dbname REMOVE FILE
filename I
> recieve the following error:
> "Cannot add, remove, or modify file 'filename' because it is offline."
> Is there anyway to remove this file?
> Thanks

Data File Offline

I have done a partial restore of a backup which leaves one of my data files
offline. I do not need this data file and would like to remove it from the
database. When I attempt to run ALTER DATABASE dbname REMOVE FILE filename
I
recieve the following error:
"Cannot add, remove, or modify file 'filename' because it is offline."
Is there anyway to remove this file?
ThanksYou need to perform a couple of steps first.
1. Bring the datafile back online. I'm not sure what you mean by the file
is marked as offline.
2. Run DBCC SHRINKFILE with the EMPTYFILE option. This will remove data
from the file and keep SQL Server from placing data on the file.
3. Run the ALTER DATABASE command with the REMOVE FILE option.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"CraigA" <CraigA@.discussions.microsoft.com> wrote in message
news:AD111224-AD9F-463E-912C-1B168D223022@.microsoft.com...
> I have done a partial restore of a backup which leaves one of my data
files
> offline. I do not need this data file and would like to remove it from
the
> database. When I attempt to run ALTER DATABASE dbname REMOVE FILE
filename I
> recieve the following error:
> "Cannot add, remove, or modify file 'filename' because it is offline."
> Is there anyway to remove this file?
> Thankssql

Data file backups and ArcServe

We are attempting to backup our SQL Server data files (MDF
and LDF) onto tape using Brightstor ARCserve. (I have my
normal daily backups - this is for offsite redundancy) The
Arcserve backups of the data files are failing
consistantly. I suspect that arcserve is not able to get
an image of the data files because they are open and
active - transactions run on this server 24/7. Does anyone
have experience with this? Is there a workaround besides
stopping the SQL service? I'm satisfied with taping the
daily backups, but my se wants the data itself.
RLR
You would need to purchase the Open File Agent in order to
backup the files while the SQL Server is running.
-Sue
On Thu, 22 Apr 2004 07:18:17 -0700, "rlruss"
<rlrsysdev@.mindspring.com> wrote:

>We are attempting to backup our SQL Server data files (MDF
>and LDF) onto tape using Brightstor ARCserve. (I have my
>normal daily backups - this is for offsite redundancy) The
>Arcserve backups of the data files are failing
>consistantly. I suspect that arcserve is not able to get
>an image of the data files because they are open and
>active - transactions run on this server 24/7. Does anyone
>have experience with this? Is there a workaround besides
>stopping the SQL service? I'm satisfied with taping the
>daily backups, but my se wants the data itself.
>RLR
|||According to my se, we have Open agent installed - it works for everything except the db files. I created a non-sql file with the mdf extension, and it copied it fine. We're going with taping the dailies until this gets figured out - se isn't thrilled, bu
t I can live with it.
so it goes..
sql

Thursday, March 8, 2012

Data change during backup.

I have found this article:
Dynamic Backups
SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
Let's look at how these two processes differ. When SQL Server begins a
backup, it notes the Log Sequence Number (LSN) of the oldest active
transaction and performs a checkpoint, which synchronizes the pages on disk
with the pages in the cache memory. Then, SQL Server starts the backup,
reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
needs to update a record, SQL Server allows the update if the backup process
has already backed up the record. Otherwise, SQL Server holds up the request
for a moment-long enough for the backup process to jump ahead and back up th
e
extent containing that record. SQL Server 6.5 then lets the update request
proceed and resumes the backup process at the point it was when it was
interrupted. When SQL Server 6.5 reaches this extent again, the backup
process skips it, because the process has already backed up this extent.
SQL Server 7.0, in contrast, doesn't worry about whether users are reading
or changing pages. SQL Server 7.0 just backs up the extents sequentially,
which is faster than jumping around as SQL Server 6.5 does. Because SQL
Server 7.0 doesn't jump ahead to back up extents before users change data,
you could end up with inconsistent data. However, SQL Server 7.0 also
introduced the ability to capture data changes that users make while the
backup is in progress. When SQL Server 7.0 reaches the end of the data, the
backup process backs up the transaction log, capturing the changes users mad
e
during the backup process. Although dynamic backup comes with a performance
penalty, Microsoft promises no more than about a 6 percent to 7 percent
performance reduction, which most users would never notice. Scheduling
backups during low database activity is still a good idea, but if you have t
o
back up the transaction log several times a day, you won't be able to avoid
having some users connected.
Because a backup can take considerable time, SQL Server 7.0's process is a
welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
when SQL Server finishes the backup.
If you're backing up just the transaction log, at the end of the backup
process, SQL Server 7.0 truncates the log, removing all transactions before
the LSN it recorded for the oldest ongoing transaction. Truncating the log
frees up space in the log and keeps it from filling up. (The log could still
fill up, however, if you have a long-running transaction that isn't
completing.) Remember that SQL Server doesn't truncate any log entry that ha
s
an LSN greater than that of the oldest active transaction.
The question is:
What is the behavior of Sql Server 2000, when a long-running transaction
work during backup?
SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
the backup ?Same as 7.0. Data pages are backuped as they are, and the transaction log re
cords generated during
the backup process are also included. If the transaction isn't finished at e
nd-time of the backup,
the COMMIT log records isn't included in the backup and when you restore the
backup, the transaction
will be rolled back.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:89E94015-4FD0-4EE6-97E0-FAE4E591919B@.microsoft.com...
>I have found this article:
> Dynamic Backups
> SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
> Let's look at how these two processes differ. When SQL Server begins a
> backup, it notes the Log Sequence Number (LSN) of the oldest active
> transaction and performs a checkpoint, which synchronizes the pages on dis
k
> with the pages in the cache memory. Then, SQL Server starts the backup,
> reading from the hard disk-not from the cache. In SQL Server 6.5, if a use
r
> needs to update a record, SQL Server allows the update if the backup proce
ss
> has already backed up the record. Otherwise, SQL Server holds up the reque
st
> for a moment-long enough for the backup process to jump ahead and back up
the
> extent containing that record. SQL Server 6.5 then lets the update request
> proceed and resumes the backup process at the point it was when it was
> interrupted. When SQL Server 6.5 reaches this extent again, the backup
> process skips it, because the process has already backed up this extent.
> SQL Server 7.0, in contrast, doesn't worry about whether users are reading
> or changing pages. SQL Server 7.0 just backs up the extents sequentially,
> which is faster than jumping around as SQL Server 6.5 does. Because SQL
> Server 7.0 doesn't jump ahead to back up extents before users change data,
> you could end up with inconsistent data. However, SQL Server 7.0 also
> introduced the ability to capture data changes that users make while the
> backup is in progress. When SQL Server 7.0 reaches the end of the data, th
e
> backup process backs up the transaction log, capturing the changes users m
ade
> during the backup process. Although dynamic backup comes with a performanc
e
> penalty, Microsoft promises no more than about a 6 percent to 7 percent
> performance reduction, which most users would never notice. Scheduling
> backups during low database activity is still a good idea, but if you have
to
> back up the transaction log several times a day, you won't be able to avoi
d
> having some users connected.
> Because a backup can take considerable time, SQL Server 7.0's process is a
> welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
> when SQL Server begins the backup; SQL Server 7.0 backs up the data as it
is
> when SQL Server finishes the backup.
> If you're backing up just the transaction log, at the end of the backup
> process, SQL Server 7.0 truncates the log, removing all transactions befor
e
> the LSN it recorded for the oldest ongoing transaction. Truncating the log
> frees up space in the log and keeps it from filling up. (The log could sti
ll
> fill up, however, if you have a long-running transaction that isn't
> completing.) Remember that SQL Server doesn't truncate any log entry that
has
> an LSN greater than that of the oldest active transaction.
> The question is:
> What is the behavior of Sql Server 2000, when a long-running transaction
> work during backup?
> SQL Server 2000 backs up the data as it is when SQL Server begin or finish
es
> the backup ?

Data change during backup.

I have found this article:
Dynamic Backups
SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
Let's look at how these two processes differ. When SQL Server begins a
backup, it notes the Log Sequence Number (LSN) of the oldest active
transaction and performs a checkpoint, which synchronizes the pages on disk
with the pages in the cache memory. Then, SQL Server starts the backup,
reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
needs to update a record, SQL Server allows the update if the backup process
has already backed up the record. Otherwise, SQL Server holds up the request
for a moment-long enough for the backup process to jump ahead and back up the
extent containing that record. SQL Server 6.5 then lets the update request
proceed and resumes the backup process at the point it was when it was
interrupted. When SQL Server 6.5 reaches this extent again, the backup
process skips it, because the process has already backed up this extent.
SQL Server 7.0, in contrast, doesn't worry about whether users are reading
or changing pages. SQL Server 7.0 just backs up the extents sequentially,
which is faster than jumping around as SQL Server 6.5 does. Because SQL
Server 7.0 doesn't jump ahead to back up extents before users change data,
you could end up with inconsistent data. However, SQL Server 7.0 also
introduced the ability to capture data changes that users make while the
backup is in progress. When SQL Server 7.0 reaches the end of the data, the
backup process backs up the transaction log, capturing the changes users made
during the backup process. Although dynamic backup comes with a performance
penalty, Microsoft promises no more than about a 6 percent to 7 percent
performance reduction, which most users would never notice. Scheduling
backups during low database activity is still a good idea, but if you have to
back up the transaction log several times a day, you won't be able to avoid
having some users connected.
Because a backup can take considerable time, SQL Server 7.0's process is a
welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
when SQL Server finishes the backup.
If you're backing up just the transaction log, at the end of the backup
process, SQL Server 7.0 truncates the log, removing all transactions before
the LSN it recorded for the oldest ongoing transaction. Truncating the log
frees up space in the log and keeps it from filling up. (The log could still
fill up, however, if you have a long-running transaction that isn't
completing.) Remember that SQL Server doesn't truncate any log entry that has
an LSN greater than that of the oldest active transaction.
The question is:
What is the behavior of Sql Server 2000, when a long-running transaction
work during backup?
SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
the backup ?
Same as 7.0. Data pages are backuped as they are, and the transaction log records generated during
the backup process are also included. If the transaction isn't finished at end-time of the backup,
the COMMIT log records isn't included in the backup and when you restore the backup, the transaction
will be rolled back.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:89E94015-4FD0-4EE6-97E0-FAE4E591919B@.microsoft.com...
>I have found this article:
> Dynamic Backups
> SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
> Let's look at how these two processes differ. When SQL Server begins a
> backup, it notes the Log Sequence Number (LSN) of the oldest active
> transaction and performs a checkpoint, which synchronizes the pages on disk
> with the pages in the cache memory. Then, SQL Server starts the backup,
> reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
> needs to update a record, SQL Server allows the update if the backup process
> has already backed up the record. Otherwise, SQL Server holds up the request
> for a moment-long enough for the backup process to jump ahead and back up the
> extent containing that record. SQL Server 6.5 then lets the update request
> proceed and resumes the backup process at the point it was when it was
> interrupted. When SQL Server 6.5 reaches this extent again, the backup
> process skips it, because the process has already backed up this extent.
> SQL Server 7.0, in contrast, doesn't worry about whether users are reading
> or changing pages. SQL Server 7.0 just backs up the extents sequentially,
> which is faster than jumping around as SQL Server 6.5 does. Because SQL
> Server 7.0 doesn't jump ahead to back up extents before users change data,
> you could end up with inconsistent data. However, SQL Server 7.0 also
> introduced the ability to capture data changes that users make while the
> backup is in progress. When SQL Server 7.0 reaches the end of the data, the
> backup process backs up the transaction log, capturing the changes users made
> during the backup process. Although dynamic backup comes with a performance
> penalty, Microsoft promises no more than about a 6 percent to 7 percent
> performance reduction, which most users would never notice. Scheduling
> backups during low database activity is still a good idea, but if you have to
> back up the transaction log several times a day, you won't be able to avoid
> having some users connected.
> Because a backup can take considerable time, SQL Server 7.0's process is a
> welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
> when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
> when SQL Server finishes the backup.
> If you're backing up just the transaction log, at the end of the backup
> process, SQL Server 7.0 truncates the log, removing all transactions before
> the LSN it recorded for the oldest ongoing transaction. Truncating the log
> frees up space in the log and keeps it from filling up. (The log could still
> fill up, however, if you have a long-running transaction that isn't
> completing.) Remember that SQL Server doesn't truncate any log entry that has
> an LSN greater than that of the oldest active transaction.
> The question is:
> What is the behavior of Sql Server 2000, when a long-running transaction
> work during backup?
> SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
> the backup ?

Data change during backup.

I have found this article:
Dynamic Backups
SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
Let's look at how these two processes differ. When SQL Server begins a
backup, it notes the Log Sequence Number (LSN) of the oldest active
transaction and performs a checkpoint, which synchronizes the pages on disk
with the pages in the cache memory. Then, SQL Server starts the backup,
reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
needs to update a record, SQL Server allows the update if the backup process
has already backed up the record. Otherwise, SQL Server holds up the request
for a moment-long enough for the backup process to jump ahead and back up the
extent containing that record. SQL Server 6.5 then lets the update request
proceed and resumes the backup process at the point it was when it was
interrupted. When SQL Server 6.5 reaches this extent again, the backup
process skips it, because the process has already backed up this extent.
SQL Server 7.0, in contrast, doesn't worry about whether users are reading
or changing pages. SQL Server 7.0 just backs up the extents sequentially,
which is faster than jumping around as SQL Server 6.5 does. Because SQL
Server 7.0 doesn't jump ahead to back up extents before users change data,
you could end up with inconsistent data. However, SQL Server 7.0 also
introduced the ability to capture data changes that users make while the
backup is in progress. When SQL Server 7.0 reaches the end of the data, the
backup process backs up the transaction log, capturing the changes users made
during the backup process. Although dynamic backup comes with a performance
penalty, Microsoft promises no more than about a 6 percent to 7 percent
performance reduction, which most users would never notice. Scheduling
backups during low database activity is still a good idea, but if you have to
back up the transaction log several times a day, you won't be able to avoid
having some users connected.
Because a backup can take considerable time, SQL Server 7.0's process is a
welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
when SQL Server finishes the backup.
If you're backing up just the transaction log, at the end of the backup
process, SQL Server 7.0 truncates the log, removing all transactions before
the LSN it recorded for the oldest ongoing transaction. Truncating the log
frees up space in the log and keeps it from filling up. (The log could still
fill up, however, if you have a long-running transaction that isn't
completing.) Remember that SQL Server doesn't truncate any log entry that has
an LSN greater than that of the oldest active transaction.
The question is:
What is the behavior of Sql Server 2000, when a long-running transaction
work during backup?
SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
the backup ?Same as 7.0. Data pages are backuped as they are, and the transaction log records generated during
the backup process are also included. If the transaction isn't finished at end-time of the backup,
the COMMIT log records isn't included in the backup and when you restore the backup, the transaction
will be rolled back.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:89E94015-4FD0-4EE6-97E0-FAE4E591919B@.microsoft.com...
>I have found this article:
> Dynamic Backups
> SQL Server 7.0's backup process is faster than SQL Server 6.5's process.
> Let's look at how these two processes differ. When SQL Server begins a
> backup, it notes the Log Sequence Number (LSN) of the oldest active
> transaction and performs a checkpoint, which synchronizes the pages on disk
> with the pages in the cache memory. Then, SQL Server starts the backup,
> reading from the hard disk-not from the cache. In SQL Server 6.5, if a user
> needs to update a record, SQL Server allows the update if the backup process
> has already backed up the record. Otherwise, SQL Server holds up the request
> for a moment-long enough for the backup process to jump ahead and back up the
> extent containing that record. SQL Server 6.5 then lets the update request
> proceed and resumes the backup process at the point it was when it was
> interrupted. When SQL Server 6.5 reaches this extent again, the backup
> process skips it, because the process has already backed up this extent.
> SQL Server 7.0, in contrast, doesn't worry about whether users are reading
> or changing pages. SQL Server 7.0 just backs up the extents sequentially,
> which is faster than jumping around as SQL Server 6.5 does. Because SQL
> Server 7.0 doesn't jump ahead to back up extents before users change data,
> you could end up with inconsistent data. However, SQL Server 7.0 also
> introduced the ability to capture data changes that users make while the
> backup is in progress. When SQL Server 7.0 reaches the end of the data, the
> backup process backs up the transaction log, capturing the changes users made
> during the backup process. Although dynamic backup comes with a performance
> penalty, Microsoft promises no more than about a 6 percent to 7 percent
> performance reduction, which most users would never notice. Scheduling
> backups during low database activity is still a good idea, but if you have to
> back up the transaction log several times a day, you won't be able to avoid
> having some users connected.
> Because a backup can take considerable time, SQL Server 7.0's process is a
> welcome enhancement. Pre-SQL Server 7.0 releases back up the data as it is
> when SQL Server begins the backup; SQL Server 7.0 backs up the data as it is
> when SQL Server finishes the backup.
> If you're backing up just the transaction log, at the end of the backup
> process, SQL Server 7.0 truncates the log, removing all transactions before
> the LSN it recorded for the oldest ongoing transaction. Truncating the log
> frees up space in the log and keeps it from filling up. (The log could still
> fill up, however, if you have a long-running transaction that isn't
> completing.) Remember that SQL Server doesn't truncate any log entry that has
> an LSN greater than that of the oldest active transaction.
> The question is:
> What is the behavior of Sql Server 2000, when a long-running transaction
> work during backup?
> SQL Server 2000 backs up the data as it is when SQL Server begin or finishes
> the backup ?

Wednesday, March 7, 2012

Data Base Backup & Restore

We are in the process of migrating an application from an
Oracle data base to a SQL Server data base.
My DBA is telling me that with SQL Server he will not be
able to do a backup & restore at the 'alias' level like
he can now do with Oracle. He says he will have to do
an entire data base backup.
Is this true?In our current environment with our databases on Oracle,
we have one physical db for 1099 tax info. When it comes
to keeping the tax years separate, we set up different
aliases for each year...1099_01, 1099_02, etc. By doing
this, we are able to backup and restore at the alias
level. In case there is a problem with a
certain 'alias', we are able to restore just that
specific alias and not the entire db.
We would like to be able to do the same thing using SQL
Server, but it sounds like we may not be able to.
Any suggestions'
>--Original Message--
>Yes,
>Assuming you mean a database backup. He can also copy
out individual tables using something like DTS or BCP if
required.
>He can also do file or filegroup backups to subset
things if the db is very large, but frequently full db
backups are more than sufficient.
>What are you trying to achieve?
>Mike John
>"Don" <Don@.nomail.com> wrote in message news:08ed01c38211
$b2eb5d10$a101280a@.phx.gbl...
>> We are in the process of migrating an application from
an
>> Oracle data base to a SQL Server data base.
>> My DBA is telling me that with SQL Server he will not
be
>> able to do a backup & restore at the 'alias' level
like
>> he can now do with Oracle. He says he will have to
do
>> an entire data base backup.
>> Is this true?
>.
>

Data Backup to CDROM

Is there any software that can make backup of data from MSSQL Server, LIVE - during work - to another machine on CDROM?
Anybody has the experience - good or bad with the CD solution?You mean that you want to buy something to do what SQL Server itself does?

-PatP

Data BackUp and Restore

hi,

pls tell me any step by step tutorial (with graphic presentation) to restore and backup the database on sql server 2005.

thank you.

The example below uses both theBACKUPandRESTORE statementsto make a copyof the Northwinddatabase.
The MOVE statement causes the dataandlogfile to be restoredto the specified locations.
TheRESTORE FILELISTONLY statementis usedto determine the numberand namesof the filesin thedatabase being restored.
The new copyof thedatabase is named TestDB.For more information, seeRESTORE FILELISTONLY.BACKUP DATABASE NorthwindTO DISK ='c:\Northwind.bak'RESTORE FILELISTONLYFROM DISK ='c:\Northwind.bak'RESTORE DATABASE TestDBFROM DISK ='c:\Northwind.bak'WITH MOVE'Northwind'TO'c:\test\testdb.mdf', MOVE'Northwind_log'TO'c:\test\testdb.ldf'GO

data and log files won't copy

I copied a database using backup and restore from Sql server 2000 to Sql server 2005. I noticed the data(mdf) and log(ldf) files did not copy. when I try to do a copy and past it gives me an error: cannot copy lof: it is being used by another person or program. Close all programs and try again. Does this mean I have to stop both servers to copy between them? Please help.

Hi,

you have to detach your database form your sql server 2000, and then attach them to the sql server 2005.

...

Detaching a database requires exclusive access to the database

USE master;
ALTER DATABASE dbname
SET SINGLE_USER;
GO

exec sp_detach_db @.dbname='dbname'

...

plz read BOL for more information: Detaching and Attaching Databases

|||

If you are restoring from an actual backup file (which usually has a .bak extension), you would not need to copy the data and log files. The restore process creates new ones from the backup file.

There are two easy ways to go from 2000 on one DB Server to 2005 on another DB Server. One is to backup the SQL Server 2000 database to a .bak file, then copy the .bak file to the new server and use SQL Server 2005 to do the restore (on the new server). This converts the database to 2005 format.

The second way is to detach the database in SQL Server 2000 and then copy the data and log files to the new server and then use SQL Server 2005 to attach them. This also converts the database to 2005 format. Either way, once you have restored or attached a SQL Server 2000 database in SQL Server 2005, the database cannot go back to SQL Server 2000 format!

Finally, whichever method you use, you should change the compatibility level to 90 and run sp_UpdateStats on the upgraded database.

|||Thank you for all your help. This worked out fine.|||

Hi,

First step :Take database offline or stop the sql server 2000 service.

After that you copy the .mdf and .ldf files.

withRegards

S.kuraliniyan

SystemEngineer

Friday, February 24, 2012

damaged data file

Hi, I have been given a damaged SQL 7 .mdf file of which rhere is no backup.
I have tried attaching but get a device activation error with a reference to
the original log file (which no longer exists).
Is there anything I can try to recover the file for these people?
God bless,
Stuart HawkinsI've never used it, but it is the only one I know of. Short of that, I think
your only option is to open a case with MS Support.
http://www.officerecovery.com/mssql/
(I presume you've already tried sp_attach_single_file_db.)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stuart Hawkins" <stuart@.sanctified.ltd.uk> wrote in message
news:%239LX7I7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Hi, I have been given a damaged SQL 7 .mdf file of which rhere is no
backup.
> I have tried attaching but get a device activation error with a reference
to
> the original log file (which no longer exists).
> Is there anything I can try to recover the file for these people?
> God bless,
> Stuart Hawkins
>|||Worked great thank you. (I had tried sp_attach_single_file_db!)
God bless,
Stuart Hawkins
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eMjUXk7pDHA.2772@.TK2MSFTNGP12.phx.gbl...
> I've never used it, but it is the only one I know of. Short of that, I
think
> your only option is to open a case with MS Support.
> http://www.officerecovery.com/mssql/
> (I presume you've already tried sp_attach_single_file_db.)
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Stuart Hawkins" <stuart@.sanctified.ltd.uk> wrote in message
> news:%239LX7I7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > Hi, I have been given a damaged SQL 7 .mdf file of which rhere is no
> backup.
> > I have tried attaching but get a device activation error with a
reference
> to
> > the original log file (which no longer exists).
> >
> > Is there anything I can try to recover the file for these people?
> >
> > God bless,
> > Stuart Hawkins
> >
> >
>

Sunday, February 19, 2012

Damaged backup file?

Hello
When I try to restore database from a specific backup file
using Enterprise Manager I get this error message:
+---+
| Microsoft SQL-DMO (ODBC SQLState: HY000) |
+---+
| Location: recbase.cpp:1378 |
| Expression: m_offBeginVar < m_SizeRec |
| SPID: 51 |
| Process ID: 1544 |
| |
| [ OK ] |
| |
+---+
The backup file contains 2 files: data file and a log file.
Restoring only data file is successful, but leaves the DB
in "Loading" state. Restoring only a log file to this partialy
restored DB is unsuccessful - generates the same error as in
full restore, and leaves DB in "Loading/Suspect" state.
Backup verification (used in SQL Query Analyzer):
RESTORE VERIFYONLY
FROM DISK = '...'
returns "The backup set is valid."
Restoring with SQL Suery Analyzer using statement:
RESTORE DATABASE [db_name]
FROM DISK = '...'
gives:
Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
Processed 1 pages for database 'mf_test', file 'xxx_Log' on file 1.
Server: Msg 3624, Level 20, State 1, Line 1
Location: R:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 52
Process ID: 1544
Connection Broken
Is there any way to recover any data from this apparently damaged backup
file? Maybe there's a way to ignore the log file and have the DB in
usable state...
Hilarion
> Backup verification (used in SQL Query Analyzer):
> RESTORE VERIFYONLY
> FROM DISK = '...'
> returns "The backup set is valid."
VERIFYONLY doesn't verify internal consistency of the backup. Essentially only that the file is
readable and that it is a SQL Server backup.

> Is there any way to recover any data from this apparently damaged backup
> file? Maybe there's a way to ignore the log file and have the DB in
> usable state...
Yes, do only one restore, from the database backup and specify WITH RECOVERY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hilarion" <hilarion@.noemail.nospam> wrote in message news:OkWDob9nEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hello
> When I try to restore database from a specific backup file
> using Enterprise Manager I get this error message:
> +---+
> | Microsoft SQL-DMO (ODBC SQLState: HY000) |
> +---+
> | Location: recbase.cpp:1378 |
> | Expression: m_offBeginVar < m_SizeRec |
> | SPID: 51 |
> | Process ID: 1544 |
> | |
> | [ OK ] |
> | |
> +---+
> The backup file contains 2 files: data file and a log file.
> Restoring only data file is successful, but leaves the DB
> in "Loading" state. Restoring only a log file to this partialy
> restored DB is unsuccessful - generates the same error as in
> full restore, and leaves DB in "Loading/Suspect" state.
> Backup verification (used in SQL Query Analyzer):
> RESTORE VERIFYONLY
> FROM DISK = '...'
> returns "The backup set is valid."
> Restoring with SQL Suery Analyzer using statement:
> RESTORE DATABASE [db_name]
> FROM DISK = '...'
> gives:
> Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
> Processed 1 pages for database 'mf_test', file 'xxx_Log' on file 1.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: R:\sql\ntdbms\storeng\drs\include\record.inl:1447
> Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
> SPID: 52
> Process ID: 1544
> Connection Broken
>
> Is there any way to recover any data from this apparently damaged backup
> file? Maybe there's a way to ignore the log file and have the DB in
> usable state...
> Hilarion
>
|||> > Is there any way to recover any data from this apparently damaged backup[vbcol=seagreen]

> Yes, do only one restore, from the database backup and specify WITH RECOVERY.
Could you supply correct RESTORE statement? Cause:
RESTORE DATABASE [db_name]
FROM DISK = '...'
WITH RECOVERY
gives:
Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
Processed 1 pages for database 'db_name', file 'xxx_Log' on file 1.
Server: Msg 3624, Level 20, State 1, Line 1
Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 52
Process ID: 3652
Connection Broken
RESTORE DATABASE [db_name]
FILEGROUP = 'PRIMARY'
FROM DISK = '...'
WITH RECOVERY
gives:
Server: Msg 3032, Level 16, State 2, Line 1
One or more of the options (recovery) are not supported for this statement.
Review the documentation for supported options.
RESTORE DATABASE [db_name]
FILEGROUP = 'PRIMARY'
FROM DISK = '...'
WITH PARTIAL, RECOVERY
gives:
Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
Processed 1 pages for database 'db_name', file 'xxx_Log' on file 1.
Server: Msg 3624, Level 20, State 1, Line 1
Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 52
Process ID: 3652
Connection Broken
Tried also some other statements. Seems like "RECOVERY" forces restore
of all files (also log file, which is damaged).
I do not know how to specify WITH RECOVERY in Enterprise Manager, and
how to specify single file ('xxx_Data' data file) to be restored in
RESTORE WITH RECOVERY statement. "SQL Server Books Online" aren't very
helpful in this case.
Hilarion
|||Seems you indeed have some corruption in your database backup (the log records therein). And SQL
server need those log records to give you a consistent state of the database. I suggest you open a
case with MS Support for this, they *might* have ways to get the database back...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hilarion" <hilarion@.noemail.nospam> wrote in message
news:%23PcYxrKoEHA.3424@.TK2MSFTNGP12.phx.gbl...
>
> Could you supply correct RESTORE statement? Cause:
> RESTORE DATABASE [db_name]
> FROM DISK = '...'
> WITH RECOVERY
> gives:
> Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
> Processed 1 pages for database 'db_name', file 'xxx_Log' on file 1.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: recbase.cpp:1378
> Expression: m_offBeginVar < m_SizeRec
> SPID: 52
> Process ID: 3652
> Connection Broken
> RESTORE DATABASE [db_name]
> FILEGROUP = 'PRIMARY'
> FROM DISK = '...'
> WITH RECOVERY
> gives:
> Server: Msg 3032, Level 16, State 2, Line 1
> One or more of the options (recovery) are not supported for this statement.
> Review the documentation for supported options.
>
> RESTORE DATABASE [db_name]
> FILEGROUP = 'PRIMARY'
> FROM DISK = '...'
> WITH PARTIAL, RECOVERY
> gives:
> Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
> Processed 1 pages for database 'db_name', file 'xxx_Log' on file 1.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: recbase.cpp:1378
> Expression: m_offBeginVar < m_SizeRec
> SPID: 52
> Process ID: 3652
> Connection Broken
>
> Tried also some other statements. Seems like "RECOVERY" forces restore
> of all files (also log file, which is damaged).
>
> I do not know how to specify WITH RECOVERY in Enterprise Manager, and
> how to specify single file ('xxx_Data' data file) to be restored in
> RESTORE WITH RECOVERY statement. "SQL Server Books Online" aren't very
> helpful in this case.
> Hilarion
>
|||> Seems you indeed have some corruption in your database
> backup (the log records therein). And SQL server need
> those log records to give you a consistent state of the
> database. I suggest you open a case with MS Support for
> this, they *might* have ways to get the database back...
I hoped some Microsoft specialist will answer my posts
here, but thanx anyway.
Hilarion
|||> I hoped some Microsoft specialist will answer my posts
> here
What happened to:
MSDN managed newsgroups provide:
- Unlimited on-line technical support - keep your PSS incidents
- A commitment to respond to your post within >>two business days<<
- Over 200 newsgroups to choose from
- Spam protection for your e-mail address when posting items
|||Well, this is an MSDN managed newsgroup so if you are an MSDN subscriber and
had posted from a registered anti-spam posting alias, you would have gotten
a response from someone on the MSDN managed newsgroups team. However,
hilarion@.noemail.nospam doesn't appear to be registered. If you are an MSDN
subscriber, please start at
http://support.microsoft.com/default...am.asp&SD=msdn
and follow the directions to register your anti-spam alias. It may take a
little while to get into the system, but once it is you'll start getting
those responses.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hilarion" <hilarion@.noemail.nospam> wrote in message
news:uO21hyVoEHA.2068@.TK2MSFTNGP09.phx.gbl...
> What happened to:
>
> MSDN managed newsgroups provide:
> - Unlimited on-line technical support - keep your PSS incidents
> - A commitment to respond to your post within >>two business days<<
> - Over 200 newsgroups to choose from
> - Spam protection for your e-mail address when posting items
>
|||Oh, and I'm not a file recovery specialist so I didn't address your original
question in my response, but that type of recovery isn't something that they
handle via the newsgroups anyway. If they can do anything, it really does
require that you open a case.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:e%231S%23cZoEHA.3792@.TK2MSFTNGP11.phx.gbl...
> Well, this is an MSDN managed newsgroup so if you are an MSDN subscriber
> and had posted from a registered anti-spam posting alias, you would have
> gotten a response from someone on the MSDN managed newsgroups team.
> However, hilarion@.noemail.nospam doesn't appear to be registered. If you
> are an MSDN subscriber, please start at
> http://support.microsoft.com/default...am.asp&SD=msdn
> and follow the directions to register your anti-spam alias. It may take a
> little while to get into the system, but once it is you'll start getting
> those responses.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Hilarion" <hilarion@.noemail.nospam> wrote in message
> news:uO21hyVoEHA.2068@.TK2MSFTNGP09.phx.gbl...
>
|||Hello
I did that (a day or something about) before posting the original post (and
re-registered few minutes ago getting "Please chose a more unique Nickname and
submit again" which means that this alias _is_ registered), so I do not know what
else should I do.
The link you gave is not entirely valid. Under this address:
http://support.microsoft.com/default...am.asp&SD=msdn
one can only find this information:
<info>
For security purposes, we have moved the registration page to the MSDN Subscriber
Downloads site located at:
http://msdn.microsoft.com/subscriptions/downloads/
After you have access to Subscriber Downloads.
1. Select the What's Hot Link.
2. Select the Managed Newsgroup link.
3. Create your no-spam alias.
</info>
Using given address (http://msdn.microsoft.com/subscriptions/downloads/), and
the link "MSDN Managed Newsgroups" leading to http://msdn.microsoft.com/newsgroups/managed/
one gets to a page with "Register" link, which is:
http://support.microsoft.com/default...am.asp&SD=msdn
so we end up circling around.
Only way to register is _not_ to follow the first instruction, and use "here"
link (https://msdn.one.microsoft.com/Subsc...NewsGroups.asp)
on "What's Hot" (in Managed Newsgroups section). Sometimes one has to use this
link twice before getting to the proper webpage.
Hilarion

> Well, this is an MSDN managed newsgroup so if you are an MSDN subscriber and had posted from a registered anti-spam posting alias,
> you would have gotten a response from someone on the MSDN managed newsgroups team. However, hilarion@.noemail.nospam doesn't appear
> to be registered. If you are an MSDN subscriber, please start at
> http://support.microsoft.com/default...am.asp&SD=msdn and follow the directions to register your
> anti-spam alias. It may take a little while to get into the system, but once it is you'll start getting those responses.
|||> Oh, and I'm not a file recovery specialist so I didn't address your original question in my response, but that type of recovery
> isn't something that they handle via the newsgroups anyway. If they can do anything, it really does require that you open a case.
OK. That is the answer I was waiting for. Maybe not very helpful, but concrete
and at least a bit official.
Hilarion

Damaged backup file?

Hello
When I try to restore database from a specific backup file
using Enterprise Manager I get this error message:
+---+
| Microsoft SQL-DMO (ODBC SQLState: HY000) |
+---+
| Location: recbase.cpp:1378 |
| Expression: m_offBeginVar < m_SizeRec |
| SPID: 51 |
| Process ID: 1544 |
| |
| [ OK ] |
| |
+---+
The backup file contains 2 files: data file and a log file.
Restoring only data file is successful, but leaves the DB
in "Loading" state. Restoring only a log file to this partialy
restored DB is unsuccessful - generates the same error as in
full restore, and leaves DB in "Loading/Suspect" state.
Backup verification (used in SQL Query Analyzer):
RESTORE VERIFYONLY
FROM DISK = '...'
returns "The backup set is valid."
Restoring with SQL Suery Analyzer using statement:
RESTORE DATABASE [db_name]
FROM DISK = '...'
gives:
Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
Processed 1 pages for database 'mf_test', file 'xxx_Log' on file 1.
Server: Msg 3624, Level 20, State 1, Line 1
Location: R:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 52
Process ID: 1544
Connection Broken
Is there any way to recover any data from this apparently damaged backup
file? Maybe there's a way to ignore the log file and have the DB in
usable state...
Hilarion> Backup verification (used in SQL Query Analyzer):
> RESTORE VERIFYONLY
> FROM DISK = '...'
> returns "The backup set is valid."
VERIFYONLY doesn't verify internal consistency of the backup. Essentially only that the file is
readable and that it is a SQL Server backup.
> Is there any way to recover any data from this apparently damaged backup
> file? Maybe there's a way to ignore the log file and have the DB in
> usable state...
Yes, do only one restore, from the database backup and specify WITH RECOVERY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hilarion" <hilarion@.noemail.nospam> wrote in message news:OkWDob9nEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hello
> When I try to restore database from a specific backup file
> using Enterprise Manager I get this error message:
> +---+
> | Microsoft SQL-DMO (ODBC SQLState: HY000) |
> +---+
> | Location: recbase.cpp:1378 |
> | Expression: m_offBeginVar < m_SizeRec |
> | SPID: 51 |
> | Process ID: 1544 |
> | |
> | [ OK ] |
> | |
> +---+
> The backup file contains 2 files: data file and a log file.
> Restoring only data file is successful, but leaves the DB
> in "Loading" state. Restoring only a log file to this partialy
> restored DB is unsuccessful - generates the same error as in
> full restore, and leaves DB in "Loading/Suspect" state.
> Backup verification (used in SQL Query Analyzer):
> RESTORE VERIFYONLY
> FROM DISK = '...'
> returns "The backup set is valid."
> Restoring with SQL Suery Analyzer using statement:
> RESTORE DATABASE [db_name]
> FROM DISK = '...'
> gives:
> Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
> Processed 1 pages for database 'mf_test', file 'xxx_Log' on file 1.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: R:\sql\ntdbms\storeng\drs\include\record.inl:1447
> Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
> SPID: 52
> Process ID: 1544
> Connection Broken
>
> Is there any way to recover any data from this apparently damaged backup
> file? Maybe there's a way to ignore the log file and have the DB in
> usable state...
> Hilarion
>|||> > Is there any way to recover any data from this apparently damaged backup
> > file? Maybe there's a way to ignore the log file and have the DB in
> > usable state...
> Yes, do only one restore, from the database backup and specify WITH RECOVERY.
Could you supply correct RESTORE statement? Cause:
RESTORE DATABASE [db_name]
FROM DISK = '...'
WITH RECOVERY
gives:
Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
Processed 1 pages for database 'db_name', file 'xxx_Log' on file 1.
Server: Msg 3624, Level 20, State 1, Line 1
Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 52
Process ID: 3652
Connection Broken
RESTORE DATABASE [db_name]
FILEGROUP = 'PRIMARY'
FROM DISK = '...'
WITH RECOVERY
gives:
Server: Msg 3032, Level 16, State 2, Line 1
One or more of the options (recovery) are not supported for this statement.
Review the documentation for supported options.
RESTORE DATABASE [db_name]
FILEGROUP = 'PRIMARY'
FROM DISK = '...'
WITH PARTIAL, RECOVERY
gives:
Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
Processed 1 pages for database 'db_name', file 'xxx_Log' on file 1.
Server: Msg 3624, Level 20, State 1, Line 1
Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 52
Process ID: 3652
Connection Broken
Tried also some other statements. Seems like "RECOVERY" forces restore
of all files (also log file, which is damaged).
I do not know how to specify WITH RECOVERY in Enterprise Manager, and
how to specify single file ('xxx_Data' data file) to be restored in
RESTORE WITH RECOVERY statement. "SQL Server Books Online" aren't very
helpful in this case.
Hilarion|||Seems you indeed have some corruption in your database backup (the log records therein). And SQL
server need those log records to give you a consistent state of the database. I suggest you open a
case with MS Support for this, they *might* have ways to get the database back...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hilarion" <hilarion@.noemail.nospam> wrote in message
news:%23PcYxrKoEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > > Is there any way to recover any data from this apparently damaged backup
> > > file? Maybe there's a way to ignore the log file and have the DB in
> > > usable state...
> > Yes, do only one restore, from the database backup and specify WITH RECOVERY.
>
> Could you supply correct RESTORE statement? Cause:
> RESTORE DATABASE [db_name]
> FROM DISK = '...'
> WITH RECOVERY
> gives:
> Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
> Processed 1 pages for database 'db_name', file 'xxx_Log' on file 1.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: recbase.cpp:1378
> Expression: m_offBeginVar < m_SizeRec
> SPID: 52
> Process ID: 3652
> Connection Broken
> RESTORE DATABASE [db_name]
> FILEGROUP = 'PRIMARY'
> FROM DISK = '...'
> WITH RECOVERY
> gives:
> Server: Msg 3032, Level 16, State 2, Line 1
> One or more of the options (recovery) are not supported for this statement.
> Review the documentation for supported options.
>
> RESTORE DATABASE [db_name]
> FILEGROUP = 'PRIMARY'
> FROM DISK = '...'
> WITH PARTIAL, RECOVERY
> gives:
> Processed 2088 pages for database 'db_name', file 'xxx_Data' on file 1.
> Processed 1 pages for database 'db_name', file 'xxx_Log' on file 1.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: recbase.cpp:1378
> Expression: m_offBeginVar < m_SizeRec
> SPID: 52
> Process ID: 3652
> Connection Broken
>
> Tried also some other statements. Seems like "RECOVERY" forces restore
> of all files (also log file, which is damaged).
>
> I do not know how to specify WITH RECOVERY in Enterprise Manager, and
> how to specify single file ('xxx_Data' data file) to be restored in
> RESTORE WITH RECOVERY statement. "SQL Server Books Online" aren't very
> helpful in this case.
> Hilarion
>|||> Seems you indeed have some corruption in your database
> backup (the log records therein). And SQL server need
> those log records to give you a consistent state of the
> database. I suggest you open a case with MS Support for
> this, they *might* have ways to get the database back...
I hoped some Microsoft specialist will answer my posts
here, but thanx anyway.
Hilarion|||> I hoped some Microsoft specialist will answer my posts
> here
What happened to:
MSDN managed newsgroups provide:
- Unlimited on-line technical support - keep your PSS incidents
- A commitment to respond to your post within >>two business days<<
- Over 200 newsgroups to choose from
- Spam protection for your e-mail address when posting items|||Well, this is an MSDN managed newsgroup so if you are an MSDN subscriber and
had posted from a registered anti-spam posting alias, you would have gotten
a response from someone on the MSDN managed newsgroups team. However,
hilarion@.noemail.nospam doesn't appear to be registered. If you are an MSDN
subscriber, please start at
http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.asp&SD=msdn
and follow the directions to register your anti-spam alias. It may take a
little while to get into the system, but once it is you'll start getting
those responses.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hilarion" <hilarion@.noemail.nospam> wrote in message
news:uO21hyVoEHA.2068@.TK2MSFTNGP09.phx.gbl...
>> I hoped some Microsoft specialist will answer my posts
>> here
> What happened to:
>
> MSDN managed newsgroups provide:
> - Unlimited on-line technical support - keep your PSS incidents
> - A commitment to respond to your post within >>two business days<<
> - Over 200 newsgroups to choose from
> - Spam protection for your e-mail address when posting items
>|||Oh, and I'm not a file recovery specialist so I didn't address your original
question in my response, but that type of recovery isn't something that they
handle via the newsgroups anyway. If they can do anything, it really does
require that you open a case.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:e%231S%23cZoEHA.3792@.TK2MSFTNGP11.phx.gbl...
> Well, this is an MSDN managed newsgroup so if you are an MSDN subscriber
> and had posted from a registered anti-spam posting alias, you would have
> gotten a response from someone on the MSDN managed newsgroups team.
> However, hilarion@.noemail.nospam doesn't appear to be registered. If you
> are an MSDN subscriber, please start at
> http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.asp&SD=msdn
> and follow the directions to register your anti-spam alias. It may take a
> little while to get into the system, but once it is you'll start getting
> those responses.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Hilarion" <hilarion@.noemail.nospam> wrote in message
> news:uO21hyVoEHA.2068@.TK2MSFTNGP09.phx.gbl...
>> I hoped some Microsoft specialist will answer my posts
>> here
>> What happened to:
>>
>> MSDN managed newsgroups provide:
>> - Unlimited on-line technical support - keep your PSS incidents
>> - A commitment to respond to your post within >>two business days<<
>> - Over 200 newsgroups to choose from
>> - Spam protection for your e-mail address when posting items
>|||Hello
I did that (a day or something about) before posting the original post (and
re-registered few minutes ago getting "Please chose a more unique Nickname and
submit again" which means that this alias _is_ registered), so I do not know what
else should I do.
The link you gave is not entirely valid. Under this address:
http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.asp&SD=msdn
one can only find this information:
<info>
For security purposes, we have moved the registration page to the MSDN Subscriber
Downloads site located at:
http://msdn.microsoft.com/subscriptions/downloads/
After you have access to Subscriber Downloads.
1. Select the What's Hot Link.
2. Select the Managed Newsgroup link.
3. Create your no-spam alias.
</info>
Using given address (http://msdn.microsoft.com/subscriptions/downloads/), and
the link "MSDN Managed Newsgroups" leading to http://msdn.microsoft.com/newsgroups/managed/
one gets to a page with "Register" link, which is:
http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.asp&SD=msdn
so we end up circling around.
Only way to register is _not_ to follow the first instruction, and use "here"
link (https://msdn.one.microsoft.com/Subscriber/1033/ManagedNewsGroups.asp)
on "What's Hot" (in Managed Newsgroups section). Sometimes one has to use this
link twice before getting to the proper webpage.
Hilarion
> Well, this is an MSDN managed newsgroup so if you are an MSDN subscriber and had posted from a registered anti-spam posting alias,
> you would have gotten a response from someone on the MSDN managed newsgroups team. However, hilarion@.noemail.nospam doesn't appear
> to be registered. If you are an MSDN subscriber, please start at
> http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.asp&SD=msdn and follow the directions to register your
> anti-spam alias. It may take a little while to get into the system, but once it is you'll start getting those responses.|||> Oh, and I'm not a file recovery specialist so I didn't address your original question in my response, but that type of recovery
> isn't something that they handle via the newsgroups anyway. If they can do anything, it really does require that you open a case.
OK. That is the answer I was waiting for. Maybe not very helpful, but concrete
and at least a bit official.
Hilarion

Daily backup problem

My SQL 2000 server's daily (full) backup started failing due to a lack of
disk space. This daily backup has been running for quite a while so im
surprised. The file system was full but it seems the old backup are not
being deleted even though the maintenance plan is set to ""Remove files older
than 1 day". Any ideas why this would happen? Solutions?
PaulOld fulls, or old t-log backups? If t-log make sure every db in the plan is
set to full recovery model. If you have databases in Simple recovery, make
a separate plan for them without the t-log backup step
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
> My SQL 2000 server's daily (full) backup started failing due to a lack of
> disk space. This daily backup has been running for quite a while so im
> surprised. The file system was full but it seems the old backup are not
> being deleted even though the maintenance plan is set to ""Remove files
> older
> than 1 day". Any ideas why this would happen? Solutions?
>
> Paul
>|||Old fulls.
I only do full backups.
"Kevin3NF" wrote:
> Old fulls, or old t-log backups? If t-log make sure every db in the plan is
> set to full recovery model. If you have databases in Simple recovery, make
> a separate plan for them without the t-log backup step
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >
> >
> > My SQL 2000 server's daily (full) backup started failing due to a lack of
> > disk space. This daily backup has been running for quite a while so im
> > surprised. The file system was full but it seems the old backup are not
> > being deleted even though the maintenance plan is set to ""Remove files
> > older
> > than 1 day". Any ideas why this would happen? Solutions?
> >
> >
> >
> > Paul
> >
>
>|||Paul,
The delete happens after the backup is taken. If you do not have enough
space for the backup it will not delete the old backup. Could this be your
problem?
Chris
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
>> Old fulls, or old t-log backups? If t-log make sure every db in the plan
>> is
>> set to full recovery model. If you have databases in Simple recovery,
>> make
>> a separate plan for them without the t-log backup step
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >
>> >
>> > My SQL 2000 server's daily (full) backup started failing due to a lack
>> > of
>> > disk space. This daily backup has been running for quite a while so im
>> > surprised. The file system was full but it seems the old backup are
>> > not
>> > being deleted even though the maintenance plan is set to ""Remove files
>> > older
>> > than 1 day". Any ideas why this would happen? Solutions?
>> >
>> >
>> >
>> > Paul
>> >
>>|||Check to see if the account running the SQL Server Agent has permissions to
delete those fulls. Look for the maintenance plan logs in the \LOG
directory (same location as the ERRORLOG file(s) )
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
>> Old fulls, or old t-log backups? If t-log make sure every db in the plan
>> is
>> set to full recovery model. If you have databases in Simple recovery,
>> make
>> a separate plan for them without the t-log backup step
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >
>> >
>> > My SQL 2000 server's daily (full) backup started failing due to a lack
>> > of
>> > disk space. This daily backup has been running for quite a while so im
>> > surprised. The file system was full but it seems the old backup are
>> > not
>> > being deleted even though the maintenance plan is set to ""Remove files
>> > older
>> > than 1 day". Any ideas why this would happen? Solutions?
>> >
>> >
>> >
>> > Paul
>> >
>>|||No there is enough space for 3 days of backups
"Chris Wood" wrote:
> Paul,
> The delete happens after the backup is taken. If you do not have enough
> space for the backup it will not delete the old backup. Could this be your
> problem?
> Chris
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||The account has full admin permission so that not a problem. Like I said this
has not been a problem for months. I don't see a log specifacally for
maintenance logs.
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||The account have the permissions to delete these file. No problem there. I
don't see a maintenance plan specific log file.
Paul
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||Add a "text file" option in the Reporting tab of the Database maintenance
GUI (SQL 2000)
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:8670769E-BF6B-4C1F-BE67-84C830DBF144@.microsoft.com...
> The account have the permissions to delete these file. No problem there. I
> don't see a maintenance plan specific log file.
> Paul
> "Kevin3NF" wrote:
>> Check to see if the account running the SQL Server Agent has permissions
>> to
>> delete those fulls. Look for the maintenance plan logs in the \LOG
>> directory (same location as the ERRORLOG file(s) )
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>> > Old fulls.
>> > I only do full backups.
>> >
>> > "Kevin3NF" wrote:
>> >
>> >> Old fulls, or old t-log backups? If t-log make sure every db in the
>> >> plan
>> >> is
>> >> set to full recovery model. If you have databases in Simple recovery,
>> >> make
>> >> a separate plan for them without the t-log backup step
>> >>
>> >> --
>> >>
>> >> Kevin3NF
>> >> SQL Server dude
>> >>
>> >> You want fries with that?
>> >> http://kevin3nf.blogspot.com/
>> >>
>> >> I only check the newsgroups during work hours, M-F.
>> >> Hit my blog and the contact links if necessary...I may be available.
>> >>
>> >>
>> >>
>> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >> >
>> >> >
>> >> > My SQL 2000 server's daily (full) backup started failing due to a
>> >> > lack
>> >> > of
>> >> > disk space. This daily backup has been running for quite a while so
>> >> > im
>> >> > surprised. The file system was full but it seems the old backup are
>> >> > not
>> >> > being deleted even though the maintenance plan is set to ""Remove
>> >> > files
>> >> > older
>> >> > than 1 day". Any ideas why this would happen? Solutions?
>> >> >
>> >> >
>> >> >
>> >> > Paul
>> >> >
>> >>
>> >>
>> >>
>>

Daily Backup of System Databases

I would like to know for daily backup of the system databases, is it suffice
if I just backup both Master and MSDB databases ?
For weekly backup, I have included the Master, MSDB and Model one.
ThanksI think it would be easier and simpler to include also model on the daily
backup. Usually model is very small.
Ben Nevarez, MCDBA, OCP
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:ODldhbk8FHA.3416@.TK2MSFTNGP15.phx.gbl...
>I would like to know for daily backup of the system databases, is it
>suffice if I just backup both Master and MSDB databases ?
> For weekly backup, I have included the Master, MSDB and Model one.
> Thanks
>|||Thank you for your advice.
I would like to know when do we change / customize the model database ?
Thanks
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:evUfWnk8FHA.3388@.TK2MSFTNGP11.phx.gbl...
> I think it would be easier and simpler to include also model on the daily
> backup. Usually model is very small.
> Ben Nevarez, MCDBA, OCP
>
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:ODldhbk8FHA.3416@.TK2MSFTNGP15.phx.gbl...
>

Daily Backup of System Databases

I would like to know for daily backup of the system databases, is it suffice
if I just backup both Master and MSDB databases ?
For weekly backup, I have included the Master, MSDB and Model one.
Thanks
I think it would be easier and simpler to include also model on the daily
backup. Usually model is very small.
Ben Nevarez, MCDBA, OCP
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:ODldhbk8FHA.3416@.TK2MSFTNGP15.phx.gbl...
>I would like to know for daily backup of the system databases, is it
>suffice if I just backup both Master and MSDB databases ?
> For weekly backup, I have included the Master, MSDB and Model one.
> Thanks
>
|||Thank you for your advice.
I would like to know when do we change / customize the model database ?
Thanks
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:evUfWnk8FHA.3388@.TK2MSFTNGP11.phx.gbl...
> I think it would be easier and simpler to include also model on the daily
> backup. Usually model is very small.
> Ben Nevarez, MCDBA, OCP
>
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:ODldhbk8FHA.3416@.TK2MSFTNGP15.phx.gbl...
>

Daily Backup of System Databases

daily backup and log shipping

Hi,
I am in the process of setting up log shipping for a large database ~ 1GB over VPN and slow WAN link.
I intend to setup and sync the servers on main office and ship after that, the warm standby db to remote site.
My question is how the daily full backups of database will afect my log shipping.
To keep this servers in sync I intend to use only daily transaction logs backups/restores. I do not want to copy a full backup of 1GB daily over the WAN. However at local site I still want to perform a daily full backup.
As I know when a full backup run it will also truncate the transaction logs, so if these large daily full backups will not be copied and restored over the WAN, the warm standby server will run out of sync. This is because a part of transaction log that will be truncated when full backup is done will not be restored to remote site.
Is there any way to do full backups after initial sync without truncating the transaction logs? Has anyone an answer to my problem?
Thank you,
Zorba
Zorba,
A full database backup does not truncate the transaction log. When you start log shipping, you can make as many full backups of the primary database as you want - it will not affect log shipping. However, you cannot make both a full database backup and a transaction log backup of the same database at the same time.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Zorba" <nospam@.nonexistent> wrote in message news:OX0Z6LemEHA.3336@.TK2MSFTNGP10.phx.gbl...
Hi,
I am in the process of setting up log shipping for a large database ~ 1GB over VPN and slow WAN link.
I intend to setup and sync the servers on main office and ship after that, the warm standby db to remote site.
My question is how the daily full backups of database will afect my log shipping.
To keep this servers in sync I intend to use only daily transaction logs backups/restores. I do not want to copy a full backup of 1GB daily over the WAN. However at local site I still want to perform a daily full backup.
As I know when a full backup run it will also truncate the transaction logs, so if these large daily full backups will not be copied and restored over the WAN, the warm standby server will run out of sync. This is because a part of transaction log that will be truncated when full backup is done will not be restored to remote site.
Is there any way to do full backups after initial sync without truncating the transaction logs? Has anyone an answer to my problem?
Thank you,
Zorba

Thursday, March 29, 2012

Data files & Transaction log recovery

Tuesday, March 27, 2012

Data File Offline

Data File Offline

Data file backups and ArcServe

Thursday, March 8, 2012

Data change during backup.

Data change during backup.

Data change during backup.

Wednesday, March 7, 2012

Data Base Backup & Restore

Data Backup to CDROM

Data BackUp and Restore

data and log files won't copy

Friday, February 24, 2012

damaged data file

Sunday, February 19, 2012

Damaged backup file?

Damaged backup file?

Daily backup problem

Daily backup problem

Daily Backup of System Databases

Daily Backup of System Databases

Daily Backup of System Databases

daily backup and log shipping

Data Flow Task Problem

Blog Archive

About Me