I am using SQL-2005 Beta. I created 7 filegroups and associate them to
a table "Myhour" through partition function and schema. It works fine.
Then I tried the following statements to backup and retore the first
filegroup:
BACKUP DATABASE Mydb
FILE='20050204' -- my first filegroup
TO DISK='C:\backup\20050204.bak'
GO
RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
GO
The execution is OK. However, after that, I could not access data from
Myhour table:
SELECT * from Myhour
GO
The error message is that '20050204' filegroup is offline. I tried
many ways. I could not figure out how to bring this filegroup online.
I event tried to remove the filegroup. I got the same offline message.
Any way to bring filegroup online?
David Chu
Information regarding filegroup restore is available in Books Online. Basically, you restored part
of the database to an earlier point in time. You don't want SQL Server to be all happy and let you
use this possibly inconsistent database? So you need to apply the transaction log backups since the
database backup was performed so that SQL Server can re-apply the work that has been performed for
that filegroup. New for SQL Server 2005 is that you can set that filegroup to read-only (before the
backup and not change it back to read write) and then restore of transaction logs are not necessary
as SQL Server would know that no data has been changed in the filegroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502090925.2966a624@.posting.google.c om...
>I am using SQL-2005 Beta. I created 7 filegroups and associate them to
> a table "Myhour" through partition function and schema. It works fine.
> Then I tried the following statements to backup and retore the first
> filegroup:
> BACKUP DATABASE Mydb
> FILE='20050204' -- my first filegroup
> TO DISK='C:\backup\20050204.bak'
> GO
> RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
> GO
> The execution is OK. However, after that, I could not access data from
> Myhour table:
> SELECT * from Myhour
> GO
> The error message is that '20050204' filegroup is offline. I tried
> many ways. I could not figure out how to bring this filegroup online.
> I event tried to remove the filegroup. I got the same offline message.
> Any way to bring filegroup online?
> David Chu
|||OK. I may do something not correctly to restore filegroup. For my
current position, is there way to bring my offline filegroup
(20050204) back on line? Without it back on line, I could not do
anything about my table Myhour because 20050204 is a partition of the
table.
|||Try backing up the transaction log, restore the file group and then restore the transaction log
backup. But check Books Online first. It was a while since I worked with filegroup backup and
restore, so I might be a bit rusty on the details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502100818.72e989f3@.posting.google.c om...
> OK. I may do something not correctly to restore filegroup. For my
> current position, is there way to bring my offline filegroup
> (20050204) back on line? Without it back on line, I could not do
> anything about my table Myhour because 20050204 is a partition of the
> table.
Showing posts with label offline. Show all posts
Showing posts with label offline. Show all posts
Monday, March 12, 2012
filegroup is offline after restore
I am using SQL-2005 Beta. I created 7 filegroups and associate them to
a table "Myhour" through partition function and schema. It works fine.
Then I tried the following statements to backup and retore the first
filegroup:
BACKUP DATABASE Mydb
FILE='20050204' -- my first filegroup
TO DISK='C:\backup\20050204.bak'
GO
RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
GO
The execution is OK. However, after that, I could not access data from
Myhour table:
SELECT * from Myhour
GO
The error message is that '20050204' filegroup is offline. I tried
many ways. I could not figure out how to bring this filegroup online.
I event tried to remove the filegroup. I got the same offline message.
Any way to bring filegroup online?
David ChuInformation regarding filegroup restore is available in Books Online. Basica
lly, you restored part
of the database to an earlier point in time. You don't want SQL Server to be
all happy and let you
use this possibly inconsistent database? So you need to apply the transactio
n log backups since the
database backup was performed so that SQL Server can re-apply the work that
has been performed for
that filegroup. New for SQL Server 2005 is that you can set that filegroup t
o read-only (before the
backup and not change it back to read write) and then restore of transaction
logs are not necessary
as SQL Server would know that no data has been changed in the filegroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502090925.2966a624@.posting.google.com...
>I am using SQL-2005 Beta. I created 7 filegroups and associate them to
> a table "Myhour" through partition function and schema. It works fine.
> Then I tried the following statements to backup and retore the first
> filegroup:
> BACKUP DATABASE Mydb
> FILE='20050204' -- my first filegroup
> TO DISK='C:\backup\20050204.bak'
> GO
> RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
> GO
> The execution is OK. However, after that, I could not access data from
> Myhour table:
> SELECT * from Myhour
> GO
> The error message is that '20050204' filegroup is offline. I tried
> many ways. I could not figure out how to bring this filegroup online.
> I event tried to remove the filegroup. I got the same offline message.
> Any way to bring filegroup online?
> David Chu|||OK. I may do something not correctly to restore filegroup. For my
current position, is there way to bring my offline filegroup
(20050204) back on line? Without it back on line, I could not do
anything about my table Myhour because 20050204 is a partition of the
table.|||Try backing up the transaction log, restore the file group and then restore
the transaction log
backup. But check Books Online first. It was a while since I worked with fil
egroup backup and
restore, so I might be a bit rusty on the details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502100818.72e989f3@.posting.google.com...
> OK. I may do something not correctly to restore filegroup. For my
> current position, is there way to bring my offline filegroup
> (20050204) back on line? Without it back on line, I could not do
> anything about my table Myhour because 20050204 is a partition of the
> table.
a table "Myhour" through partition function and schema. It works fine.
Then I tried the following statements to backup and retore the first
filegroup:
BACKUP DATABASE Mydb
FILE='20050204' -- my first filegroup
TO DISK='C:\backup\20050204.bak'
GO
RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
GO
The execution is OK. However, after that, I could not access data from
Myhour table:
SELECT * from Myhour
GO
The error message is that '20050204' filegroup is offline. I tried
many ways. I could not figure out how to bring this filegroup online.
I event tried to remove the filegroup. I got the same offline message.
Any way to bring filegroup online?
David ChuInformation regarding filegroup restore is available in Books Online. Basica
lly, you restored part
of the database to an earlier point in time. You don't want SQL Server to be
all happy and let you
use this possibly inconsistent database? So you need to apply the transactio
n log backups since the
database backup was performed so that SQL Server can re-apply the work that
has been performed for
that filegroup. New for SQL Server 2005 is that you can set that filegroup t
o read-only (before the
backup and not change it back to read write) and then restore of transaction
logs are not necessary
as SQL Server would know that no data has been changed in the filegroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502090925.2966a624@.posting.google.com...
>I am using SQL-2005 Beta. I created 7 filegroups and associate them to
> a table "Myhour" through partition function and schema. It works fine.
> Then I tried the following statements to backup and retore the first
> filegroup:
> BACKUP DATABASE Mydb
> FILE='20050204' -- my first filegroup
> TO DISK='C:\backup\20050204.bak'
> GO
> RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
> GO
> The execution is OK. However, after that, I could not access data from
> Myhour table:
> SELECT * from Myhour
> GO
> The error message is that '20050204' filegroup is offline. I tried
> many ways. I could not figure out how to bring this filegroup online.
> I event tried to remove the filegroup. I got the same offline message.
> Any way to bring filegroup online?
> David Chu|||OK. I may do something not correctly to restore filegroup. For my
current position, is there way to bring my offline filegroup
(20050204) back on line? Without it back on line, I could not do
anything about my table Myhour because 20050204 is a partition of the
table.|||Try backing up the transaction log, restore the file group and then restore
the transaction log
backup. But check Books Online first. It was a while since I worked with fil
egroup backup and
restore, so I might be a bit rusty on the details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502100818.72e989f3@.posting.google.com...
> OK. I may do something not correctly to restore filegroup. For my
> current position, is there way to bring my offline filegroup
> (20050204) back on line? Without it back on line, I could not do
> anything about my table Myhour because 20050204 is a partition of the
> table.
filegroup is offline after restore
I am using SQL-2005 Beta. I created 7 filegroups and associate them to
a table "Myhour" through partition function and schema. It works fine.
Then I tried the following statements to backup and retore the first
filegroup:
BACKUP DATABASE Mydb
FILE='20050204' -- my first filegroup
TO DISK='C:\backup\20050204.bak'
GO
RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
GO
The execution is OK. However, after that, I could not access data from
Myhour table:
SELECT * from Myhour
GO
The error message is that '20050204' filegroup is offline. I tried
many ways. I could not figure out how to bring this filegroup online.
I event tried to remove the filegroup. I got the same offline message.
Any way to bring filegroup online?
David ChuInformation regarding filegroup restore is available in Books Online. Basically, you restored part
of the database to an earlier point in time. You don't want SQL Server to be all happy and let you
use this possibly inconsistent database? So you need to apply the transaction log backups since the
database backup was performed so that SQL Server can re-apply the work that has been performed for
that filegroup. New for SQL Server 2005 is that you can set that filegroup to read-only (before the
backup and not change it back to read write) and then restore of transaction logs are not necessary
as SQL Server would know that no data has been changed in the filegroup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502090925.2966a624@.posting.google.com...
>I am using SQL-2005 Beta. I created 7 filegroups and associate them to
> a table "Myhour" through partition function and schema. It works fine.
> Then I tried the following statements to backup and retore the first
> filegroup:
> BACKUP DATABASE Mydb
> FILE='20050204' -- my first filegroup
> TO DISK='C:\backup\20050204.bak'
> GO
> RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
> GO
> The execution is OK. However, after that, I could not access data from
> Myhour table:
> SELECT * from Myhour
> GO
> The error message is that '20050204' filegroup is offline. I tried
> many ways. I could not figure out how to bring this filegroup online.
> I event tried to remove the filegroup. I got the same offline message.
> Any way to bring filegroup online?
> David Chu|||OK. I may do something not correctly to restore filegroup. For my
current position, is there way to bring my offline filegroup
(20050204) back on line? Without it back on line, I could not do
anything about my table Myhour because 20050204 is a partition of the
table.|||Try backing up the transaction log, restore the file group and then restore the transaction log
backup. But check Books Online first. It was a while since I worked with filegroup backup and
restore, so I might be a bit rusty on the details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502100818.72e989f3@.posting.google.com...
> OK. I may do something not correctly to restore filegroup. For my
> current position, is there way to bring my offline filegroup
> (20050204) back on line? Without it back on line, I could not do
> anything about my table Myhour because 20050204 is a partition of the
> table.
a table "Myhour" through partition function and schema. It works fine.
Then I tried the following statements to backup and retore the first
filegroup:
BACKUP DATABASE Mydb
FILE='20050204' -- my first filegroup
TO DISK='C:\backup\20050204.bak'
GO
RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
GO
The execution is OK. However, after that, I could not access data from
Myhour table:
SELECT * from Myhour
GO
The error message is that '20050204' filegroup is offline. I tried
many ways. I could not figure out how to bring this filegroup online.
I event tried to remove the filegroup. I got the same offline message.
Any way to bring filegroup online?
David ChuInformation regarding filegroup restore is available in Books Online. Basically, you restored part
of the database to an earlier point in time. You don't want SQL Server to be all happy and let you
use this possibly inconsistent database? So you need to apply the transaction log backups since the
database backup was performed so that SQL Server can re-apply the work that has been performed for
that filegroup. New for SQL Server 2005 is that you can set that filegroup to read-only (before the
backup and not change it back to read write) and then restore of transaction logs are not necessary
as SQL Server would know that no data has been changed in the filegroup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502090925.2966a624@.posting.google.com...
>I am using SQL-2005 Beta. I created 7 filegroups and associate them to
> a table "Myhour" through partition function and schema. It works fine.
> Then I tried the following statements to backup and retore the first
> filegroup:
> BACKUP DATABASE Mydb
> FILE='20050204' -- my first filegroup
> TO DISK='C:\backup\20050204.bak'
> GO
> RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
> GO
> The execution is OK. However, after that, I could not access data from
> Myhour table:
> SELECT * from Myhour
> GO
> The error message is that '20050204' filegroup is offline. I tried
> many ways. I could not figure out how to bring this filegroup online.
> I event tried to remove the filegroup. I got the same offline message.
> Any way to bring filegroup online?
> David Chu|||OK. I may do something not correctly to restore filegroup. For my
current position, is there way to bring my offline filegroup
(20050204) back on line? Without it back on line, I could not do
anything about my table Myhour because 20050204 is a partition of the
table.|||Try backing up the transaction log, restore the file group and then restore the transaction log
backup. But check Books Online first. It was a while since I worked with filegroup backup and
restore, so I might be a bit rusty on the details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502100818.72e989f3@.posting.google.com...
> OK. I may do something not correctly to restore filegroup. For my
> current position, is there way to bring my offline filegroup
> (20050204) back on line? Without it back on line, I could not do
> anything about my table Myhour because 20050204 is a partition of the
> table.
Sunday, February 26, 2012
File size limit / offline cache
Hi,
I have 2 questions:
- Is there any way of getting around the 128MB file size limit when creating and adding SSEv databases to VS2005? Currently I get the following error when trying to connect to a database:"The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only...". This after I altered the app.config file to "...Max Database Size=600;..." Have anyone tried to use SSEv to cache data with the use of the Smart Application Offline Building Block? Is there a provider I can use for doing this?
Thanks in advance!
A SQLEv database can be up to 4GB in size. You control the maximum size of the database through the connection string. See the System.Data.SqlServerCe.SqlCeConnection MSDN documentation for details on this and other connection string parameters that you'll want to know about when working with large databases (e.g. Autoshrink threshold comes to mind)
http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx
Have a look at the new Mobile Client Software Factory published on MSDN - it includes an off-line app block for use with SQL Mobile and should be directly applicable to SQLEv.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/mcsflp.asp
Darren
File size limit / offline cache
Hi,
I have 2 questions:
Is there any way of getting around the 128MB file size limit when creating and adding SSEv databases to VS2005? Currently I get the following error when trying to connect to a database:"The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only...". This after I altered the app.config file to "...Max Database Size=600;..."
Have anyone tried to use SSEv to cache data with the use of the Smart Application Offline Building Block? Is there a provider I can use for doing this?
Thanks in advance!
A SQLEv database can be up to 4GB in size. You control the maximum size of the database through the connection string. See the System.Data.SqlServerCe.SqlCeConnection MSDN documentation for details on this and other connection string parameters that you'll want to know about when working with large databases (e.g. Autoshrink threshold comes to mind)
http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx
Have a look at the new Mobile Client Software Factory published on MSDN - it includes an off-line app block for use with SQL Mobile and should be directly applicable to SQLEv.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/mcsflp.asp
Darren
Subscribe to:
Posts (Atom)