Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
http://groups.google.com/groups?q=pa...TNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillan
Paul,
I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msftngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
http://groups.google.com/groups?q=pa...TNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>
|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul
Showing posts with label containing. Show all posts
Showing posts with label containing. Show all posts
Friday, March 30, 2012
PARTIAL SQL Server RESTORE's
Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msftngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msftngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul
PARTIAL SQL Server RESTORE's
Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
&start=20&
hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as w
ell. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). D
id you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different na
me
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log
),
-- and all files in the restored filegroup (in this example, mywind_data_2 i
s the
-- only file in sales) are moved to a new location. The log is then recover
ed:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msft
ngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups includi
ng
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get t
he
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirm
s
> my suspisions from someone with a microsoft.com email address
>
&start=20&hl=en&lr=&ie=UTF
-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a fu
ll
> database backup. Partial database restore of file backups is not supported
."
> What it says is "Partial database restore of file backups is not supported
"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
&start=20&
hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as w
ell. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). D
id you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different na
me
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log
),
-- and all files in the restored filegroup (in this example, mywind_data_2 i
s the
-- only file in sales) are moved to a new location. The log is then recover
ed:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msft
ngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups includi
ng
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get t
he
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirm
s
> my suspisions from someone with a microsoft.com email address
>
&start=20&hl=en&lr=&ie=UTF
-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a fu
ll
> database backup. Partial database restore of file backups is not supported
."
> What it says is "Partial database restore of file backups is not supported
"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul
Wednesday, March 28, 2012
Parse A column containing SYntax
Has anyone tried to parse a column containing sql syntax to obtain the actual column names used in the syntax field. So if the field had acctno =1001 then it would return acctno.http://www.dbforums.com/showthread.php?t=1196943|||I haven't seen a script to do this, and it would be quite a challenge.
Wednesday, March 21, 2012
Partitioning
Hello
I have a table containing 100,000 record for each year, and every year a new 100,000 record are inserted,
I need to know how to make partion this table by year
I need to know the syntax
Thank you
Any Ideas??
|||Hi JRICE,
what do you mean by "make partion this table"? A simple "order by year" will sort your table based on the "year" column, but i'm not sure if it is what you want.
Also, could you please elaborate more information on the structure of your table? thanks
|||Im talking about horizantal partitioning and i need some help in the syntax
Thank you
|||100k records per year is really not big of a number that requires partitioning. SQL Server 2005 has some advanced options for partitioning and gives move flexibility. In 2000 its more of a simulated partitioning. You'd have to have a view UNIONing the partitions. Basically its a bit painful. Check this white paper on partitioning by Kimberley Trip:http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
|||Hi JRICE,
Which verison of sql server are you using? Based on my understanding, only enterprise edition support partition. If you have got one, you can try the following example:
CREATE PARTITION FUNCTION myRangePF1 (int)AS RANGE LEFT FOR VALUES (1, 100, 1000) ;GOCREATE PARTITION SCHEME myRangePS1AS PARTITION myRangePF1TO (test1fg, test2fg, test3fg, test4fg) ;GOALTER TABLE PartitionTable (col1 int, col2 char(10))ON myRangePS1 (col1) ;GO
This is one code example from msdn document, and it should work (i've only got a standard edition installed so i cannot test it on my side. sorry)
Hope my suggestion helps
Tuesday, March 20, 2012
parametrised servername and catalog name
Hi,
I intend to use four-part table names to select data from a Linked Server into local tables. There will be stored procedures containing Insert-Select statements.
While I'm developing, I'm pointing to a development version of the remote server. In production, the remote server will be different. There will be many other situations, where I will need to link to one remote server or another. But I don't want to recompile my stored procs every time.
The question is: can I use variables for the first two parts of a four-part table name. Something like:
declare @.svr varchar(20)
declare @.dsn varchar(20)
select @.svr = 'Pervasive_Test', @.dsn = 'D_drive'
SELECT * from @.svr.@.dsn..remote_table
Would the above work ?
I have to go through the ODBC Provider. The only way my query works is when I also specify the DSN as "catalog" in the second position of the table name. I definitely do not want the name of the DSN to be hardwired into my stored procs.
Andrewyour example wont work, though you can use
sp_executesql (see BOL) to run commands with variable object names,
eg.
set @.statement = N'select user_id from users
where cc_number = '+''''+ltrim(rtrim(@.cc_number))+''''
insert into #master_id (user_id)
exec sp_executesql @.statement
though for the sort of thing you are doing, I usually create a batch file which uses a tool like SED to replace each token (defined earlier on object names) with the desired values , and then use the relevant modified script for each installation...just have the bat file receive the server/tablname as params
I intend to use four-part table names to select data from a Linked Server into local tables. There will be stored procedures containing Insert-Select statements.
While I'm developing, I'm pointing to a development version of the remote server. In production, the remote server will be different. There will be many other situations, where I will need to link to one remote server or another. But I don't want to recompile my stored procs every time.
The question is: can I use variables for the first two parts of a four-part table name. Something like:
declare @.svr varchar(20)
declare @.dsn varchar(20)
select @.svr = 'Pervasive_Test', @.dsn = 'D_drive'
SELECT * from @.svr.@.dsn..remote_table
Would the above work ?
I have to go through the ODBC Provider. The only way my query works is when I also specify the DSN as "catalog" in the second position of the table name. I definitely do not want the name of the DSN to be hardwired into my stored procs.
Andrewyour example wont work, though you can use
sp_executesql (see BOL) to run commands with variable object names,
eg.
set @.statement = N'select user_id from users
where cc_number = '+''''+ltrim(rtrim(@.cc_number))+''''
insert into #master_id (user_id)
exec sp_executesql @.statement
though for the sort of thing you are doing, I usually create a batch file which uses a tool like SED to replace each token (defined earlier on object names) with the desired values , and then use the relevant modified script for each installation...just have the bat file receive the server/tablname as params
Subscribe to:
Posts (Atom)