I have a huge database and it takes ages to restore. If
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JBIf the database doesn't exists, then SQL Server basically performs CREATE DATABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part. It will just add back
the pages to the same location as they had when you performed the backup. (It is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an error from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same, then SQL Server will
not be able to put back all pages in the same file and page address as they had when the backup was
performed. In this case, you can use the REPLACE option, which essentially means that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB
Showing posts with label ages. Show all posts
Showing posts with label ages. Show all posts
Thursday, March 8, 2012
database restore takes ages....
I have a huge database and it takes ages to restore. If
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JB
If the database doesn't exists, then SQL Server basically performs CREATE DATABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part. It will just add back
the pages to the same location as they had when you performed the backup. (It is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an error from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same, then SQL Server will
not be able to put back all pages in the same file and page address as they had when the backup was
performed. In this case, you can use the REPLACE option, which essentially means that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JB
If the database doesn't exists, then SQL Server basically performs CREATE DATABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part. It will just add back
the pages to the same location as they had when you performed the backup. (It is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an error from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same, then SQL Server will
not be able to put back all pages in the same file and page address as they had when the backup was
performed. In this case, you can use the REPLACE option, which essentially means that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB
database restore takes ages....
I have a huge database and it takes ages to restore. If
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JBIf the database doesn't exists, then SQL Server basically performs CREATE DA
TABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took
backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part.
It will just add back
the pages to the same location as they had when you performed the backup. (I
t is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an er
ror from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same,
then SQL Server will
not be able to put back all pages in the same file and page address as they
had when the backup was
performed. In this case, you can use the REPLACE option, which essentially m
eans that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JBIf the database doesn't exists, then SQL Server basically performs CREATE DA
TABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took
backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part.
It will just add back
the pages to the same location as they had when you performed the backup. (I
t is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an er
ror from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same,
then SQL Server will
not be able to put back all pages in the same file and page address as they
had when the backup was
performed. In this case, you can use the REPLACE option, which essentially m
eans that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB
Saturday, February 25, 2012
Database reindex
Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks
Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor and
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fill
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:
> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks
Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor and
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fill
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:
> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks
Subscribe to:
Posts (Atom)