Seeking advice here.
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduce
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
Tnt
You need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one in
> Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers that
> connect to this database (it won't do any good if the IIS is still pointing
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt
|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
[vbcol=seagreen]
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log will
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup? If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...[vbcol=seagreen]
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
Showing posts with label mdf. Show all posts
Showing posts with label mdf. Show all posts
Friday, March 9, 2012
Need advice about setting file limit, file grow
Seeking advice here.
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduc
e
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
TntYou need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one i
n
> Feb and recently Nov). Running the transaction backup doesn't seem to red
uce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers tha
t
> connect to this database (it won't do any good if the IIS is still pointin
g
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
[vbcol=seagreen]
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log wil
l
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
>|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup' If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...[vbcol=seagreen]
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
>
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduc
e
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
TntYou need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one i
n
> Feb and recently Nov). Running the transaction backup doesn't seem to red
uce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers tha
t
> connect to this database (it won't do any good if the IIS is still pointin
g
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
[vbcol=seagreen]
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log wil
l
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
>|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup' If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...[vbcol=seagreen]
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
>
Need advice about setting file limit, file grow
Seeking advice here.
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduce
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
TntYou need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one in
> Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers that
> connect to this database (it won't do any good if the IIS is still pointing
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log will
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
> > Seeking advice here.
> >
> > We have a database that consists of about 440 mb (mdf) and the logs of the
> > database amount to almost 14 GB (ldf).
> >
> >
> > We schedule to run the transaction log every night (I used to GUI when we
> > set up).
> >
> >
> > During the last year, we have seen the logs spike up to 42 GB twice (one in
> > Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> > the size. The only time we could get it to reduce size is running the
> > command below, but the problem is we have to shutdown both IIS servers that
> > connect to this database (it won't do any good if the IIS is still pointing
> > to it).
> >
> >
> > backup log bbnc to disk='E:\bbnc_log3.log'
> >
> > dbcc shrinkfile (Portal_log, 2)
> >
> > We are considering setting a limit for the file size. Right now it is set
> > to "Automatic grow file", by 10 percent. Maximum file growth to
> > "Unrestricted file growth". What are the cons of setting a limit for the
> > size?
> >
> > What is the best setting for us?
> >
> >
> > Thanks in advance.
> >
> > Tnt|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup' If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
>> You need to contain your run-away transactions. If you run a very large
>> transaction that uses up a lot of transaction log space, your tran log
>> will
>> grow large no matter how often you back up the tran log.
>> Linchi
>> "tnt" wrote:
>> > Seeking advice here.
>> >
>> > We have a database that consists of about 440 mb (mdf) and the logs of
>> > the
>> > database amount to almost 14 GB (ldf).
>> >
>> >
>> > We schedule to run the transaction log every night (I used to GUI when
>> > we
>> > set up).
>> >
>> >
>> > During the last year, we have seen the logs spike up to 42 GB twice
>> > (one in
>> > Feb and recently Nov). Running the transaction backup doesn't seem to
>> > reduce
>> > the size. The only time we could get it to reduce size is running the
>> > command below, but the problem is we have to shutdown both IIS servers
>> > that
>> > connect to this database (it won't do any good if the IIS is still
>> > pointing
>> > to it).
>> >
>> >
>> > backup log bbnc to disk='E:\bbnc_log3.log'
>> >
>> > dbcc shrinkfile (Portal_log, 2)
>> >
>> > We are considering setting a limit for the file size. Right now it is
>> > set
>> > to "Automatic grow file", by 10 percent. Maximum file growth to
>> > "Unrestricted file growth". What are the cons of setting a limit for
>> > the
>> > size?
>> >
>> > What is the best setting for us?
>> >
>> >
>> > Thanks in advance.
>> >
>> > Tnt
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduce
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
TntYou need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one in
> Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers that
> connect to this database (it won't do any good if the IIS is still pointing
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log will
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
> > Seeking advice here.
> >
> > We have a database that consists of about 440 mb (mdf) and the logs of the
> > database amount to almost 14 GB (ldf).
> >
> >
> > We schedule to run the transaction log every night (I used to GUI when we
> > set up).
> >
> >
> > During the last year, we have seen the logs spike up to 42 GB twice (one in
> > Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> > the size. The only time we could get it to reduce size is running the
> > command below, but the problem is we have to shutdown both IIS servers that
> > connect to this database (it won't do any good if the IIS is still pointing
> > to it).
> >
> >
> > backup log bbnc to disk='E:\bbnc_log3.log'
> >
> > dbcc shrinkfile (Portal_log, 2)
> >
> > We are considering setting a limit for the file size. Right now it is set
> > to "Automatic grow file", by 10 percent. Maximum file growth to
> > "Unrestricted file growth". What are the cons of setting a limit for the
> > size?
> >
> > What is the best setting for us?
> >
> >
> > Thanks in advance.
> >
> > Tnt|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup' If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
>> You need to contain your run-away transactions. If you run a very large
>> transaction that uses up a lot of transaction log space, your tran log
>> will
>> grow large no matter how often you back up the tran log.
>> Linchi
>> "tnt" wrote:
>> > Seeking advice here.
>> >
>> > We have a database that consists of about 440 mb (mdf) and the logs of
>> > the
>> > database amount to almost 14 GB (ldf).
>> >
>> >
>> > We schedule to run the transaction log every night (I used to GUI when
>> > we
>> > set up).
>> >
>> >
>> > During the last year, we have seen the logs spike up to 42 GB twice
>> > (one in
>> > Feb and recently Nov). Running the transaction backup doesn't seem to
>> > reduce
>> > the size. The only time we could get it to reduce size is running the
>> > command below, but the problem is we have to shutdown both IIS servers
>> > that
>> > connect to this database (it won't do any good if the IIS is still
>> > pointing
>> > to it).
>> >
>> >
>> > backup log bbnc to disk='E:\bbnc_log3.log'
>> >
>> > dbcc shrinkfile (Portal_log, 2)
>> >
>> > We are considering setting a limit for the file size. Right now it is
>> > set
>> > to "Automatic grow file", by 10 percent. Maximum file growth to
>> > "Unrestricted file growth". What are the cons of setting a limit for
>> > the
>> > size?
>> >
>> > What is the best setting for us?
>> >
>> >
>> > Thanks in advance.
>> >
>> > Tnt
Saturday, February 25, 2012
Nebie connection problems with SQL Server 2005 Express and VS 2005
I downloaded SQL Server 2005 Express and some sample databases (incl. Adventureworks_data.mdf), but when I try to Add Connection in VS 2005 to point to this db and use the Test Connection button, it gives the error:
Microsoft Visual Studio
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I've tried setting the data source as either "Microsoft SQL Server Database File" or "Microsoft SQL Server", but get the same error.
Any tips on verifying SQL Srv Express is installed correctly? Any ideas on how to solve this problem?
Thanks in advance.
What happens if you set the source=(local)?Monday, February 20, 2012
NDFs into MDF
Hi,
One simple question. I have a DB with MDF+NDFs files and I want to restore/c
reate
its backup to a DB with only one MDF file.
How?
Thanks,
PS: My INET does not allow me any domains but MS. So, if your solution point
s to
links outside MS, please copy the content in the message.A restored database is exactly like the original. The number and size of
the files will be the same after the restore.
You can consolidate the data files after the restore by executing DBCC
SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
from the secondary data file to the primary data file. You can remove the
secondary file afterward with ALTER DATABASE REMOVE FILE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
restore/create
points to
got multiple filegroups and objects in those filegroups then i think the onl
y
option is to go into each object and move it to the primary filegroup. once
the secondary filegroup is emptied, then the file(s) for that filegroup can
be
deleted.
Dan Guzman wrote:
[QUOTE]
> A restored database is exactly like the original. The number and size of
> the files will be the same after the restore.
> You can consolidate the data files after the restore by executing DBCC
> SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
> from the secondary data file to the primary data file. You can remove the
> secondary file afterward with ALTER DATABASE REMOVE FILE.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
> news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
> restore/create
> points to
One simple question. I have a DB with MDF+NDFs files and I want to restore/c
reate
its backup to a DB with only one MDF file.
How?
Thanks,
PS: My INET does not allow me any domains but MS. So, if your solution point
s to
links outside MS, please copy the content in the message.A restored database is exactly like the original. The number and size of
the files will be the same after the restore.
You can consolidate the data files after the restore by executing DBCC
SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
from the secondary data file to the primary data file. You can remove the
secondary file afterward with ALTER DATABASE REMOVE FILE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
quote:
> Hi,
> One simple question. I have a DB with MDF+NDFs files and I want to
restore/create
quote:
> its backup to a DB with only one MDF file.
> How?
> Thanks,
> PS: My INET does not allow me any domains but MS. So, if your solution
points to
quote:|||Dan's answer assumes he's got multiple files in the same filegroup. if he's
> links outside MS, please copy the content in the message.
got multiple filegroups and objects in those filegroups then i think the onl
y
option is to go into each object and move it to the primary filegroup. once
the secondary filegroup is emptied, then the file(s) for that filegroup can
be
deleted.
Dan Guzman wrote:
[QUOTE]
> A restored database is exactly like the original. The number and size of
> the files will be the same after the restore.
> You can consolidate the data files after the restore by executing DBCC
> SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
> from the secondary data file to the primary data file. You can remove the
> secondary file afterward with ALTER DATABASE REMOVE FILE.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
> news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
> restore/create
> points to
NDFs into MDF
Hi,
One simple question. I have a DB with MDF+NDFs files and I want to restore/create
its backup to a DB with only one MDF file.
How?
Thanks,
PS: My INET does not allow me any domains but MS. So, if your solution points to
links outside MS, please copy the content in the message.A restored database is exactly like the original. The number and size of
the files will be the same after the restore.
You can consolidate the data files after the restore by executing DBCC
SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
from the secondary data file to the primary data file. You can remove the
secondary file afterward with ALTER DATABASE REMOVE FILE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
> Hi,
> One simple question. I have a DB with MDF+NDFs files and I want to
restore/create
> its backup to a DB with only one MDF file.
> How?
> Thanks,
> PS: My INET does not allow me any domains but MS. So, if your solution
points to
> links outside MS, please copy the content in the message.|||Dan's answer assumes he's got multiple files in the same filegroup. if he's
got multiple filegroups and objects in those filegroups then i think the only
option is to go into each object and move it to the primary filegroup. once
the secondary filegroup is emptied, then the file(s) for that filegroup can be
deleted.
Dan Guzman wrote:
> A restored database is exactly like the original. The number and size of
> the files will be the same after the restore.
> You can consolidate the data files after the restore by executing DBCC
> SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
> from the secondary data file to the primary data file. You can remove the
> secondary file afterward with ALTER DATABASE REMOVE FILE.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
> news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
> > Hi,
> > One simple question. I have a DB with MDF+NDFs files and I want to
> restore/create
> > its backup to a DB with only one MDF file.
> > How?
> > Thanks,
> > PS: My INET does not allow me any domains but MS. So, if your solution
> points to
> > links outside MS, please copy the content in the message.
One simple question. I have a DB with MDF+NDFs files and I want to restore/create
its backup to a DB with only one MDF file.
How?
Thanks,
PS: My INET does not allow me any domains but MS. So, if your solution points to
links outside MS, please copy the content in the message.A restored database is exactly like the original. The number and size of
the files will be the same after the restore.
You can consolidate the data files after the restore by executing DBCC
SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
from the secondary data file to the primary data file. You can remove the
secondary file afterward with ALTER DATABASE REMOVE FILE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
> Hi,
> One simple question. I have a DB with MDF+NDFs files and I want to
restore/create
> its backup to a DB with only one MDF file.
> How?
> Thanks,
> PS: My INET does not allow me any domains but MS. So, if your solution
points to
> links outside MS, please copy the content in the message.|||Dan's answer assumes he's got multiple files in the same filegroup. if he's
got multiple filegroups and objects in those filegroups then i think the only
option is to go into each object and move it to the primary filegroup. once
the secondary filegroup is emptied, then the file(s) for that filegroup can be
deleted.
Dan Guzman wrote:
> A restored database is exactly like the original. The number and size of
> the files will be the same after the restore.
> You can consolidate the data files after the restore by executing DBCC
> SHRINKFILE ... EMPTYFILE on the 'ndf' file. This will migrate data pages
> from the secondary data file to the primary data file. You can remove the
> secondary file afterward with ALTER DATABASE REMOVE FILE.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ian Hughes" <anonymous@.discussions.microsoft.com> wrote in message
> news:27B711FB-6AF6-4D26-9333-9C68CACF41D6@.microsoft.com...
> > Hi,
> > One simple question. I have a DB with MDF+NDFs files and I want to
> restore/create
> > its backup to a DB with only one MDF file.
> > How?
> > Thanks,
> > PS: My INET does not allow me any domains but MS. So, if your solution
> points to
> > links outside MS, please copy the content in the message.
NDF File in a Secondary File group was lost
Hi people,
I have a big problem here. I have one database with the following
configuration:
1- Primary FileGroup - teste.mdf
2- Secondary FileGroup - teste.ndf
My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
don´t need to recover my data in teste.ndf, but my database was in Suspect
State and I need to change to Online State.
What Should I do?
thanksclaudio
Do you have a last backup of your database?
Please refer to BOL for more details about how to perfom restore filegroups
and files.
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> Hi people,
> I have a big problem here. I have one database with the following
> configuration:
> 1- Primary FileGroup - teste.mdf
> 2- Secondary FileGroup - teste.ndf
> My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> don´t need to recover my data in teste.ndf, but my database was in
Suspect
> State and I need to change to Online State.
> What Should I do?
> thanks
>|||Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
question is: I need the put the database online without a secondary
filegroup. I know that SQL SERVER retain that information about the data
files in a header page in *.mdf file and in the master database. Hence my
doubt is how I do for the change that information in a header page in *.mdf?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> claudio
> Do you have a last backup of your database?
> Please refer to BOL for more details about how to perfom restore
filegroups
> and files.
>
> "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > Hi people,
> > I have a big problem here. I have one database with the following
> > configuration:
> > 1- Primary FileGroup - teste.mdf
> > 2- Secondary FileGroup - teste.ndf
> >
> > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > don´t need to recover my data in teste.ndf, but my database was in
> Suspect
> > State and I need to change to Online State.
> > What Should I do?
> >
> > thanks
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>
I have a big problem here. I have one database with the following
configuration:
1- Primary FileGroup - teste.mdf
2- Secondary FileGroup - teste.ndf
My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
don´t need to recover my data in teste.ndf, but my database was in Suspect
State and I need to change to Online State.
What Should I do?
thanksclaudio
Do you have a last backup of your database?
Please refer to BOL for more details about how to perfom restore filegroups
and files.
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> Hi people,
> I have a big problem here. I have one database with the following
> configuration:
> 1- Primary FileGroup - teste.mdf
> 2- Secondary FileGroup - teste.ndf
> My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> don´t need to recover my data in teste.ndf, but my database was in
Suspect
> State and I need to change to Online State.
> What Should I do?
> thanks
>|||Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
question is: I need the put the database online without a secondary
filegroup. I know that SQL SERVER retain that information about the data
files in a header page in *.mdf file and in the master database. Hence my
doubt is how I do for the change that information in a header page in *.mdf?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> claudio
> Do you have a last backup of your database?
> Please refer to BOL for more details about how to perfom restore
filegroups
> and files.
>
> "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > Hi people,
> > I have a big problem here. I have one database with the following
> > configuration:
> > 1- Primary FileGroup - teste.mdf
> > 2- Secondary FileGroup - teste.ndf
> >
> > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > don´t need to recover my data in teste.ndf, but my database was in
> Suspect
> > State and I need to change to Online State.
> > What Should I do?
> >
> > thanks
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>|||You probably cannot do that, at least there's no documented, supported way. Opening a case with MS
Support *might* help. Another option can be to set the db in emergency mode (search google etc for
how to do it and more info) to be able to get to your data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
news:OcDN7xBiDHA.2460@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri for your reply, but I dont have backup and It doesn´t matter. My
> question is: I need the put the database online without a secondary
> filegroup. I know that SQL SERVER retain that information about the data
> files in a header page in *.mdf file and in the master database. Hence my
> doubt is how I do for the change that information in a header page in *.mdf?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukbO2yAiDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > claudio
> > Do you have a last backup of your database?
> > Please refer to BOL for more details about how to perfom restore
> filegroups
> > and files.
> >
> >
> > "claudio" <claudio.lucio&SPAM@.coresynesis.com.br> wrote in message
> > news:eMYwYb3hDHA.336@.tk2msftngp13.phx.gbl...
> > > Hi people,
> > > I have a big problem here. I have one database with the following
> > > configuration:
> > > 1- Primary FileGroup - teste.mdf
> > > 2- Secondary FileGroup - teste.ndf
> > >
> > > My trouble is: I lost my datafile that belongs to Secondary FileGroup. I
> > > don´t need to recover my data in teste.ndf, but my database was in
> > Suspect
> > > State and I need to change to Online State.
> > > What Should I do?
> > >
> > > thanks
> > >
> > >
> >
> >
>
NDF file
I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.
Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegro ups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>
|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegro ups.com...
>
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,
|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A
|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...
|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
Aaron Bertrand
SQL Server MVP
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.
Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegro ups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>
|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegro ups.com...
>
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,
|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A
|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...
|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
Aaron Bertrand
SQL Server MVP
NDF file
I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.1. Create a filegroup a put the NDF file in it.
2. Create/Alter the tables and point them to the new filegroup.
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
> >I have a database and trying to put couple of the tables into a ndf
> > file instead of mdf file, any ideas how can I achieve it?
> > Thanks a lot.
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > Is there a way in the enterprise manager I can manually specify tables
> > into secondary ndf filegroups?
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
--
Aaron Bertrand
SQL Server MVP
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.1. Create a filegroup a put the NDF file in it.
2. Create/Alter the tables and point them to the new filegroup.
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
> >I have a database and trying to put couple of the tables into a ndf
> > file instead of mdf file, any ideas how can I achieve it?
> > Thanks a lot.
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > Is there a way in the enterprise manager I can manually specify tables
> > into secondary ndf filegroups?
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
--
Aaron Bertrand
SQL Server MVP
NDF file
I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.1. Create a filegroup a put the NDF file in it.
2. Create/Alter the tables and point them to the new filegroup.
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>
>
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE IND
EX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
Aaron Bertrand
SQL Server MVP
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.1. Create a filegroup a put the NDF file in it.
2. Create/Alter the tables and point them to the new filegroup.
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>
>
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE IND
EX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
Aaron Bertrand
SQL Server MVP
NDF and MDF data backup
Our server crashed. I was able to boot up in safe mode and copy the data
onto an external USB harddrive, all except our MDF file - i was able to
copy the 9 other NDF files and the LDF file. Our MDF file has very
little of our data, but i know it has SQL information in it.
What are my options on a restore? Can i create a new DB on the new HD
(which will create all NDF, LDF, and MDF), and then stop SQL, copy the
NDF and LDF from our USB drive, and then start up SQL again, and will
that work?
Remember, the files are just copied, not any kind of SQL backup.
TIA
Darin
*** Sent via Developersdex http://www.codecomments.com ***As far as I know you have any luck to get your database back using only ldf
and ndf files.
Are you sure you do not have a backup of your database anywhere in the
cabinets in your room, hmm?
You didn't mention what kind of a crash it is, however have you tried some
kind of File Recovery software to try to recover that mdf file?
I hope another friend of mine here has to say something to recover it...
Ekrem nsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:%23YFrJRiIIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Our server crashed. I was able to boot up in safe mode and copy the data
> onto an external USB harddrive, all except our MDF file - i was able to
> copy the 9 other NDF files and the LDF file. Our MDF file has very
> little of our data, but i know it has SQL information in it.
> What are my options on a restore? Can i create a new DB on the new HD
> (which will create all NDF, LDF, and MDF), and then stop SQL, copy the
> NDF and LDF from our USB drive, and then start up SQL again, and will
> that work?
> Remember, the files are just copied, not any kind of SQL backup.
> TIA
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||we do have a backup, but it is to a DAT drive that this other server
doesn't have a drive on. I am getting a new HD to put in the original
server, where that HD crashed, just trying to get up and running now.
I do have a backup of older data (MDF, NDF, and LDF) on my machine and i
tried to do what i wanted and couldn't get it to work. After creating
the new database, and then coping over the old MDF, when i restarted
MSSQLSERVER, the enterprise manager showed the database, but there isn't
a + sign next to it, so there are tables, views, etc. strange.
Darin
*** Sent via Developersdex http://www.codecomments.com ***|||>. After creating
> the new database, and then coping over the old MDF, when i restarted
> MSSQLSERVER, the enterprise manager showed the database, but there isn't
> a + sign next to it, so there are tables, views, etc. strange.
If you have the mdf, ndf and ldf files, you can try to do an "attach". More
info: http://msdn2.microsoft.com/en-us/library/ms190209.aspx
/Sjang|||that is my problem, i don't have the MDF. I created a new DB and tried
to copy just the NDF and LDF and keep the new MDF and attach the DB and
it didn't work.
I have a new HD and will hopefully be able to restore from the tape
backup.
Darin
*** Sent via Developersdex http://www.codecomments.com ***|||My guess is that the master db is in your .mdf file. Without that, you're
kinda toast.
"Darin" <darin_nospam@.nospamever> wrote in message
news:usJ7KgkIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> that is my problem, i don't have the MDF. I created a new DB and tried
> to copy just the NDF and LDF and keep the new MDF and attach the DB and
> it didn't work.
> I have a new HD and will hopefully be able to restore from the tape
> backup.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||Yes, the mdf is the primary database file, the "root" file for the database.
It contains references
to all other database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23$bwV7kIIHA.588@.TK2MSFTNGP05.phx.gbl...[vb
col=seagreen]
> My guess is that the master db is in your .mdf file. Without that, you're
kinda toast.
> "Darin" <darin_nospam@.nospamever> wrote in message news:usJ7KgkIIHA.4296@.T
K2MSFTNGP04.phx.gbl...
>[/vbcol]|||In a message of yours you have MDF file of your database and in another you
say you do not have it.
Creating a database from scratch and copying your other databases NDF and
LDF files and using the new database's MDF file would not work. It doesn't
work like that. You need the MDF file of your original database. If not,
then you have to use your backup to restore your database.
Ekrem nsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:usJ7KgkIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> that is my problem, i don't have the MDF. I created a new DB and tried
> to copy just the NDF and LDF and keep the new MDF and attach the DB and
> it didn't work.
> I have a new HD and will hopefully be able to restore from the tape
> backup.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
onto an external USB harddrive, all except our MDF file - i was able to
copy the 9 other NDF files and the LDF file. Our MDF file has very
little of our data, but i know it has SQL information in it.
What are my options on a restore? Can i create a new DB on the new HD
(which will create all NDF, LDF, and MDF), and then stop SQL, copy the
NDF and LDF from our USB drive, and then start up SQL again, and will
that work?
Remember, the files are just copied, not any kind of SQL backup.
TIA
Darin
*** Sent via Developersdex http://www.codecomments.com ***As far as I know you have any luck to get your database back using only ldf
and ndf files.
Are you sure you do not have a backup of your database anywhere in the
cabinets in your room, hmm?
You didn't mention what kind of a crash it is, however have you tried some
kind of File Recovery software to try to recover that mdf file?
I hope another friend of mine here has to say something to recover it...
Ekrem nsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:%23YFrJRiIIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Our server crashed. I was able to boot up in safe mode and copy the data
> onto an external USB harddrive, all except our MDF file - i was able to
> copy the 9 other NDF files and the LDF file. Our MDF file has very
> little of our data, but i know it has SQL information in it.
> What are my options on a restore? Can i create a new DB on the new HD
> (which will create all NDF, LDF, and MDF), and then stop SQL, copy the
> NDF and LDF from our USB drive, and then start up SQL again, and will
> that work?
> Remember, the files are just copied, not any kind of SQL backup.
> TIA
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||we do have a backup, but it is to a DAT drive that this other server
doesn't have a drive on. I am getting a new HD to put in the original
server, where that HD crashed, just trying to get up and running now.
I do have a backup of older data (MDF, NDF, and LDF) on my machine and i
tried to do what i wanted and couldn't get it to work. After creating
the new database, and then coping over the old MDF, when i restarted
MSSQLSERVER, the enterprise manager showed the database, but there isn't
a + sign next to it, so there are tables, views, etc. strange.
Darin
*** Sent via Developersdex http://www.codecomments.com ***|||>. After creating
> the new database, and then coping over the old MDF, when i restarted
> MSSQLSERVER, the enterprise manager showed the database, but there isn't
> a + sign next to it, so there are tables, views, etc. strange.
If you have the mdf, ndf and ldf files, you can try to do an "attach". More
info: http://msdn2.microsoft.com/en-us/library/ms190209.aspx
/Sjang|||that is my problem, i don't have the MDF. I created a new DB and tried
to copy just the NDF and LDF and keep the new MDF and attach the DB and
it didn't work.
I have a new HD and will hopefully be able to restore from the tape
backup.
Darin
*** Sent via Developersdex http://www.codecomments.com ***|||My guess is that the master db is in your .mdf file. Without that, you're
kinda toast.
"Darin" <darin_nospam@.nospamever> wrote in message
news:usJ7KgkIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> that is my problem, i don't have the MDF. I created a new DB and tried
> to copy just the NDF and LDF and keep the new MDF and attach the DB and
> it didn't work.
> I have a new HD and will hopefully be able to restore from the tape
> backup.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||Yes, the mdf is the primary database file, the "root" file for the database.
It contains references
to all other database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23$bwV7kIIHA.588@.TK2MSFTNGP05.phx.gbl...[vb
col=seagreen]
> My guess is that the master db is in your .mdf file. Without that, you're
kinda toast.
> "Darin" <darin_nospam@.nospamever> wrote in message news:usJ7KgkIIHA.4296@.T
K2MSFTNGP04.phx.gbl...
>[/vbcol]|||In a message of yours you have MDF file of your database and in another you
say you do not have it.
Creating a database from scratch and copying your other databases NDF and
LDF files and using the new database's MDF file would not work. It doesn't
work like that. You need the MDF file of your original database. If not,
then you have to use your backup to restore your database.
Ekrem nsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:usJ7KgkIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> that is my problem, i don't have the MDF. I created a new DB and tried
> to copy just the NDF and LDF and keep the new MDF and attach the DB and
> it didn't work.
> I have a new HD and will hopefully be able to restore from the tape
> backup.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
NDF and MDF data backup
Our server crashed. I was able to boot up in safe mode and copy the data
onto an external USB harddrive, all except our MDF file - i was able to
copy the 9 other NDF files and the LDF file. Our MDF file has very
little of our data, but i know it has SQL information in it.
What are my options on a restore? Can i create a new DB on the new HD
(which will create all NDF, LDF, and MDF), and then stop SQL, copy the
NDF and LDF from our USB drive, and then start up SQL again, and will
that work?
Remember, the files are just copied, not any kind of SQL backup.
TIA
Darin
*** Sent via Developersdex http://www.developersdex.com ***As far as I know you have any luck to get your database back using only ldf
and ndf files.
Are you sure you do not have a backup of your database anywhere in the
cabinets in your room, hmm?
You didn't mention what kind of a crash it is, however have you tried some
kind of File Recovery software to try to recover that mdf file?
I hope another friend of mine here has to say something to recover it...
--
Ekrem Önsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:%23YFrJRiIIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Our server crashed. I was able to boot up in safe mode and copy the data
> onto an external USB harddrive, all except our MDF file - i was able to
> copy the 9 other NDF files and the LDF file. Our MDF file has very
> little of our data, but i know it has SQL information in it.
> What are my options on a restore? Can i create a new DB on the new HD
> (which will create all NDF, LDF, and MDF), and then stop SQL, copy the
> NDF and LDF from our USB drive, and then start up SQL again, and will
> that work?
> Remember, the files are just copied, not any kind of SQL backup.
> TIA
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***
onto an external USB harddrive, all except our MDF file - i was able to
copy the 9 other NDF files and the LDF file. Our MDF file has very
little of our data, but i know it has SQL information in it.
What are my options on a restore? Can i create a new DB on the new HD
(which will create all NDF, LDF, and MDF), and then stop SQL, copy the
NDF and LDF from our USB drive, and then start up SQL again, and will
that work?
Remember, the files are just copied, not any kind of SQL backup.
TIA
Darin
*** Sent via Developersdex http://www.developersdex.com ***As far as I know you have any luck to get your database back using only ldf
and ndf files.
Are you sure you do not have a backup of your database anywhere in the
cabinets in your room, hmm?
You didn't mention what kind of a crash it is, however have you tried some
kind of File Recovery software to try to recover that mdf file?
I hope another friend of mine here has to say something to recover it...
--
Ekrem Önsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:%23YFrJRiIIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Our server crashed. I was able to boot up in safe mode and copy the data
> onto an external USB harddrive, all except our MDF file - i was able to
> copy the 9 other NDF files and the LDF file. Our MDF file has very
> little of our data, but i know it has SQL information in it.
> What are my options on a restore? Can i create a new DB on the new HD
> (which will create all NDF, LDF, and MDF), and then stop SQL, copy the
> NDF and LDF from our USB drive, and then start up SQL again, and will
> that work?
> Remember, the files are just copied, not any kind of SQL backup.
> TIA
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***
NDF and MDF data backup
that is my problem, i don't have the MDF. I created a new DB and tried
to copy just the NDF and LDF and keep the new MDF and attach the DB and
it didn't work.
I have a new HD and will hopefully be able to restore from the tape
backup.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
My guess is that the master db is in your .mdf file. Without that, you're
kinda toast.
"Darin" <darin_nospam@.nospamever> wrote in message
news:usJ7KgkIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> that is my problem, i don't have the MDF. I created a new DB and tried
> to copy just the NDF and LDF and keep the new MDF and attach the DB and
> it didn't work.
> I have a new HD and will hopefully be able to restore from the tape
> backup.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||In a message of yours you have MDF file of your database and in another you
say you do not have it.
Creating a database from scratch and copying your other databases NDF and
LDF files and using the new database's MDF file would not work. It doesn't
work like that. You need the MDF file of your original database. If not,
then you have to use your backup to restore your database.
Ekrem nsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:usJ7KgkIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> that is my problem, i don't have the MDF. I created a new DB and tried
> to copy just the NDF and LDF and keep the new MDF and attach the DB and
> it didn't work.
> I have a new HD and will hopefully be able to restore from the tape
> backup.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
to copy just the NDF and LDF and keep the new MDF and attach the DB and
it didn't work.
I have a new HD and will hopefully be able to restore from the tape
backup.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
My guess is that the master db is in your .mdf file. Without that, you're
kinda toast.
"Darin" <darin_nospam@.nospamever> wrote in message
news:usJ7KgkIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> that is my problem, i don't have the MDF. I created a new DB and tried
> to copy just the NDF and LDF and keep the new MDF and attach the DB and
> it didn't work.
> I have a new HD and will hopefully be able to restore from the tape
> backup.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||In a message of yours you have MDF file of your database and in another you
say you do not have it.
Creating a database from scratch and copying your other databases NDF and
LDF files and using the new database's MDF file would not work. It doesn't
work like that. You need the MDF file of your original database. If not,
then you have to use your backup to restore your database.
Ekrem nsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:usJ7KgkIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> that is my problem, i don't have the MDF. I created a new DB and tried
> to copy just the NDF and LDF and keep the new MDF and attach the DB and
> it didn't work.
> I have a new HD and will hopefully be able to restore from the tape
> backup.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
NDF and MDF data backup
Our server crashed. I was able to boot up in safe mode and copy the data
onto an external USB harddrive, all except our MDF file - i was able to
copy the 9 other NDF files and the LDF file. Our MDF file has very
little of our data, but i know it has SQL information in it.
What are my options on a restore? Can i create a new DB on the new HD
(which will create all NDF, LDF, and MDF), and then stop SQL, copy the
NDF and LDF from our USB drive, and then start up SQL again, and will
that work?
Remember, the files are just copied, not any kind of SQL backup.
TIA
Darin
*** Sent via Developersdex http://www.codecomments.com ***
As far as I know you have any luck to get your database back using only ldf
and ndf files.
Are you sure you do not have a backup of your database anywhere in the
cabinets in your room, hmm?
You didn't mention what kind of a crash it is, however have you tried some
kind of File Recovery software to try to recover that mdf file?
I hope another friend of mine here has to say something to recover it...
Ekrem nsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:%23YFrJRiIIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Our server crashed. I was able to boot up in safe mode and copy the data
> onto an external USB harddrive, all except our MDF file - i was able to
> copy the 9 other NDF files and the LDF file. Our MDF file has very
> little of our data, but i know it has SQL information in it.
> What are my options on a restore? Can i create a new DB on the new HD
> (which will create all NDF, LDF, and MDF), and then stop SQL, copy the
> NDF and LDF from our USB drive, and then start up SQL again, and will
> that work?
> Remember, the files are just copied, not any kind of SQL backup.
> TIA
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||we do have a backup, but it is to a DAT drive that this other server
doesn't have a drive on. I am getting a new HD to put in the original
server, where that HD crashed, just trying to get up and running now.
I do have a backup of older data (MDF, NDF, and LDF) on my machine and i
tried to do what i wanted and couldn't get it to work. After creating
the new database, and then coping over the old MDF, when i restarted
MSSQLSERVER, the enterprise manager showed the database, but there isn't
a + sign next to it, so there are tables, views, etc. strange.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
onto an external USB harddrive, all except our MDF file - i was able to
copy the 9 other NDF files and the LDF file. Our MDF file has very
little of our data, but i know it has SQL information in it.
What are my options on a restore? Can i create a new DB on the new HD
(which will create all NDF, LDF, and MDF), and then stop SQL, copy the
NDF and LDF from our USB drive, and then start up SQL again, and will
that work?
Remember, the files are just copied, not any kind of SQL backup.
TIA
Darin
*** Sent via Developersdex http://www.codecomments.com ***
As far as I know you have any luck to get your database back using only ldf
and ndf files.
Are you sure you do not have a backup of your database anywhere in the
cabinets in your room, hmm?
You didn't mention what kind of a crash it is, however have you tried some
kind of File Recovery software to try to recover that mdf file?
I hope another friend of mine here has to say something to recover it...
Ekrem nsoy
"Darin" <darin_nospam@.nospamever> wrote in message
news:%23YFrJRiIIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Our server crashed. I was able to boot up in safe mode and copy the data
> onto an external USB harddrive, all except our MDF file - i was able to
> copy the 9 other NDF files and the LDF file. Our MDF file has very
> little of our data, but i know it has SQL information in it.
> What are my options on a restore? Can i create a new DB on the new HD
> (which will create all NDF, LDF, and MDF), and then stop SQL, copy the
> NDF and LDF from our USB drive, and then start up SQL again, and will
> that work?
> Remember, the files are just copied, not any kind of SQL backup.
> TIA
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||we do have a backup, but it is to a DAT drive that this other server
doesn't have a drive on. I am getting a new HD to put in the original
server, where that HD crashed, just trying to get up and running now.
I do have a backup of older data (MDF, NDF, and LDF) on my machine and i
tried to do what i wanted and couldn't get it to work. After creating
the new database, and then coping over the old MDF, when i restarted
MSSQLSERVER, the enterprise manager showed the database, but there isn't
a + sign next to it, so there are tables, views, etc. strange.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Subscribe to:
Posts (Atom)