I used a temporary table in a stored procedure.Whenever this stored procedure is executed with the desired output it is giving the warning message-
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?Originally posted by subhasishray
I used a temporary table in a stored procedure.Whenever this stored procedure is executed with the desired output it is giving the warning message-
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?
The best way is to reduce the data size of the table to less than 8060 bytes.
other way .. i believe might be to set the option
set ansi_warnings off|||I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish
Clearly ... you are not paying heed to the warning message .. An insert into your table will fail in case the data is more than 8060 bytes . You should take that case into consideration.
Am searching for the other answer .. will get back as soon as i find it|||There is no "other" answer, you've said it all. the only other thing to do would be to split the table into 2 with 1-to-1 relationship.|||I was searching for a server property where i could control the severity level of warnings and errors as is availible in osql with the -m flag.|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish
Whether you know it or not...that comes across with some attitude..
If you can't be flexible...then I don't know who will...
good luck...
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Restrict Database Viewing
One of our programmers is going to write a program that involves some
sensitive data stored in the SQL server, using visual basic. Is there a way
to restrict him from viewing the data in the database, yet allowing him to d
o
his programming and debugging?Rather than developing against the live production database, consider
developing the application against a test database with identical schema.
You can use test data or obfuscated production data for testing.
Hope this helps.
Dan Guzman
SQL Server MVP
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:5118B0AD-451A-406E-81F9-F6EFA426B00E@.microsoft.com...
> One of our programmers is going to write a program that involves some
> sensitive data stored in the SQL server, using visual basic. Is there a
> way
> to restrict him from viewing the data in the database, yet allowing him to
> do
> his programming and debugging?|||I spend a lot of time writing apps against sensitive data, and often have to
explain to clients that valid data is needed to test against.
If you want to limit access to certain tables just create a user with
disabled access to sensitive table and allow only access to tables they will
use.
If the tables that they need, have actual sensitive data in them, then
create a duplicate database with different user and change some data.
i.e.. if its salary then directly edit the duplicate database and change all
the names and passwords to random data.
My suggestion is to have a third reliable party to backup database change
the sensitive data and return with another database name.
Then just hand that database on cd to your programmer.
As a second thought if its only reading i.e.. reports then create required
tables and fields as views.
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:5118B0AD-451A-406E-81F9-F6EFA426B00E@.microsoft.com...
> One of our programmers is going to write a program that involves some
> sensitive data stored in the SQL server, using visual basic. Is there a
> way
> to restrict him from viewing the data in the database, yet allowing him to
> do
> his programming and debugging?
sensitive data stored in the SQL server, using visual basic. Is there a way
to restrict him from viewing the data in the database, yet allowing him to d
o
his programming and debugging?Rather than developing against the live production database, consider
developing the application against a test database with identical schema.
You can use test data or obfuscated production data for testing.
Hope this helps.
Dan Guzman
SQL Server MVP
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:5118B0AD-451A-406E-81F9-F6EFA426B00E@.microsoft.com...
> One of our programmers is going to write a program that involves some
> sensitive data stored in the SQL server, using visual basic. Is there a
> way
> to restrict him from viewing the data in the database, yet allowing him to
> do
> his programming and debugging?|||I spend a lot of time writing apps against sensitive data, and often have to
explain to clients that valid data is needed to test against.
If you want to limit access to certain tables just create a user with
disabled access to sensitive table and allow only access to tables they will
use.
If the tables that they need, have actual sensitive data in them, then
create a duplicate database with different user and change some data.
i.e.. if its salary then directly edit the duplicate database and change all
the names and passwords to random data.
My suggestion is to have a third reliable party to backup database change
the sensitive data and return with another database name.
Then just hand that database on cd to your programmer.
As a second thought if its only reading i.e.. reports then create required
tables and fields as views.
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:5118B0AD-451A-406E-81F9-F6EFA426B00E@.microsoft.com...
> One of our programmers is going to write a program that involves some
> sensitive data stored in the SQL server, using visual basic. Is there a
> way
> to restrict him from viewing the data in the database, yet allowing him to
> do
> his programming and debugging?
Monday, March 26, 2012
Restoring the database using stored procedure
hey guyz...
i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"
i have included the stored procedure below. is the stored procedure correct?
if it is.. how can i sovle this problem?
CREATE Procedure spRestoreDatabase
@.Path VARCHAR(100)
AS
Restore Database Test From Disk = @.Path
GOA database restore can only be done when you've got exclusive use of the database. No other users can be using the database besides the one doing the restore.
The procedure can't be in the database, because then the spid trying to restore the datbase would need to be executing code in the database which would prevent the restore.
An SQL Agent job would be a better choice than a stored procedure. You may need to think about using ALTER DATABASE to force the other users out of the database, but think that through carefully before you try to use it because it can cause other problems.
-PatP|||erm... so what can i do now?|||Put your stored procedure in a different database and run it from there.
Gotta ask why you need a stored proc to restore your database in the first place. Surely this isn't going to be an automated task?|||erm... so what can i do now?As blindman pointed out, your first step ought to be to rethink what you are doing. Try to determine if there is some better way than an automated restore.
If you really, truly need to automate the restore, I'd do it as a SQL Agent Job myself. You can actually get pretty tricky with jobs, and do nearly anything you can do with a stored procedure, plus a whole lot more.
Before you get too busy coding, think hard about what you are doing, why you are doing it, and whether there are any alternatives. You are headed into the area that cartographers of olde used to label "Here be dragons"
-PatPsql
i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"
i have included the stored procedure below. is the stored procedure correct?
if it is.. how can i sovle this problem?
CREATE Procedure spRestoreDatabase
@.Path VARCHAR(100)
AS
Restore Database Test From Disk = @.Path
GOA database restore can only be done when you've got exclusive use of the database. No other users can be using the database besides the one doing the restore.
The procedure can't be in the database, because then the spid trying to restore the datbase would need to be executing code in the database which would prevent the restore.
An SQL Agent job would be a better choice than a stored procedure. You may need to think about using ALTER DATABASE to force the other users out of the database, but think that through carefully before you try to use it because it can cause other problems.
-PatP|||erm... so what can i do now?|||Put your stored procedure in a different database and run it from there.
Gotta ask why you need a stored proc to restore your database in the first place. Surely this isn't going to be an automated task?|||erm... so what can i do now?As blindman pointed out, your first step ought to be to rethink what you are doing. Try to determine if there is some better way than an automated restore.
If you really, truly need to automate the restore, I'd do it as a SQL Agent Job myself. You can actually get pretty tricky with jobs, and do nearly anything you can do with a stored procedure, plus a whole lot more.
Before you get too busy coding, think hard about what you are doing, why you are doing it, and whether there are any alternatives. You are headed into the area that cartographers of olde used to label "Here be dragons"
-PatPsql
Restoring stored procedure HELP!
Someone accidently deleted some of a stored procedure. Is it possible to
restore just that one whole procedure, or do we have to restore DB and copy
that one store procedure?
Hi
Restore the DB as a different DB on the server and copy it over.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:4A8E8BD3-461B-4175-93B2-37F22B9F6B12@.microsoft.com...
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and
> copy
> that one store procedure?
|||You can't selectively restore objects. Restore the database elsewhere and
then copy the proc.
It's definitely good practice to keep a separate copy of your SQL source
code, just as you would for any other code in C# or VB. Use a source control
system such as Microsoft SourceSafe.
David Portas
SQL Server MVP
|||restore primary filegroup and copy sp
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mike" wrote:
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and copy
> that one store procedure?
restore just that one whole procedure, or do we have to restore DB and copy
that one store procedure?
Hi
Restore the DB as a different DB on the server and copy it over.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:4A8E8BD3-461B-4175-93B2-37F22B9F6B12@.microsoft.com...
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and
> copy
> that one store procedure?
|||You can't selectively restore objects. Restore the database elsewhere and
then copy the proc.
It's definitely good practice to keep a separate copy of your SQL source
code, just as you would for any other code in C# or VB. Use a source control
system such as Microsoft SourceSafe.
David Portas
SQL Server MVP
|||restore primary filegroup and copy sp
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mike" wrote:
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and copy
> that one store procedure?
Restoring stored procedure HELP!
Someone accidently deleted some of a stored procedure. Is it possible to
restore just that one whole procedure, or do we have to restore DB and copy
that one store procedure?Hi
Restore the DB as a different DB on the server and copy it over.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:4A8E8BD3-461B-4175-93B2-37F22B9F6B12@.microsoft.com...
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and
> copy
> that one store procedure?|||You can't selectively restore objects. Restore the database elsewhere and
then copy the proc.
It's definitely good practice to keep a separate copy of your SQL source
code, just as you would for any other code in C# or VB. Use a source control
system such as Microsoft SourceSafe.
David Portas
SQL Server MVP
--|||restore primary filegroup and copy sp
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mike" wrote:
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and cop
y
> that one store procedure?sql
restore just that one whole procedure, or do we have to restore DB and copy
that one store procedure?Hi
Restore the DB as a different DB on the server and copy it over.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:4A8E8BD3-461B-4175-93B2-37F22B9F6B12@.microsoft.com...
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and
> copy
> that one store procedure?|||You can't selectively restore objects. Restore the database elsewhere and
then copy the proc.
It's definitely good practice to keep a separate copy of your SQL source
code, just as you would for any other code in C# or VB. Use a source control
system such as Microsoft SourceSafe.
David Portas
SQL Server MVP
--|||restore primary filegroup and copy sp
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mike" wrote:
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and cop
y
> that one store procedure?sql
Restoring stored procedure HELP!
Someone accidently deleted some of a stored procedure. Is it possible to
restore just that one whole procedure, or do we have to restore DB and copy
that one store procedure?You can't selectively restore objects. Restore the database elsewhere and
then copy the proc.
It's definitely good practice to keep a separate copy of your SQL source
code, just as you would for any other code in C# or VB. Use a source control
system such as Microsoft SourceSafe.
--
David Portas
SQL Server MVP
--|||Hi
Restore the DB as a different DB on the server and copy it over.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:4A8E8BD3-461B-4175-93B2-37F22B9F6B12@.microsoft.com...
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and
> copy
> that one store procedure?|||restore primary filegroup and copy sp
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mike" wrote:
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and copy
> that one store procedure?
restore just that one whole procedure, or do we have to restore DB and copy
that one store procedure?You can't selectively restore objects. Restore the database elsewhere and
then copy the proc.
It's definitely good practice to keep a separate copy of your SQL source
code, just as you would for any other code in C# or VB. Use a source control
system such as Microsoft SourceSafe.
--
David Portas
SQL Server MVP
--|||Hi
Restore the DB as a different DB on the server and copy it over.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:4A8E8BD3-461B-4175-93B2-37F22B9F6B12@.microsoft.com...
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and
> copy
> that one store procedure?|||restore primary filegroup and copy sp
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mike" wrote:
> Someone accidently deleted some of a stored procedure. Is it possible to
> restore just that one whole procedure, or do we have to restore DB and copy
> that one store procedure?
Wednesday, March 7, 2012
Restoring FileGroups...
We are currently in the process of designing a Consolidated System (data for multiple organizations stored in one database server) using SQL Server 2005. One of the design goal is to refresh individual company data from Production to "User Acceptance Server" without affecting other companies.
So, we thought of implementing this via Partitioning by Org. We need the "Proof of concept" that this can be done with a simple example. Since our DBA's are also new to SQL 2005, we are working together in achieving the goal. Ok, here is what we are trying to achieve:
1. Create a Database (Test1) with two file Groups (FileGroup1, FileGroup2) with two files in each file group.
2. Create two tables on this database, one on each FileGroup
Table 1 on FileGroup1, Table 2 on FileGroup2.
3. Populate data on these tables.
4. Create an exact copy of this database with the above architecture and call it Test2.
5. Populate Test2, with a different set of data.
6. Backup FileGroup2 on Test2 and restore it to Test1 database.
What we did:
1. I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file with:I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file using :
BACKUP LOG PGM TO DISK='D:\Test2_Log.bak' WITH NORECOVERY
2. Restore the database filegroup using:
RESTORE DATABASE Test1
FILEGROUP = 'FileGroup2'
FROM DISK ='D:\Test2.bak' WITH
MOVE 'FileName2_A' TO 'G:\Data\File2_A.ndf',
MOVE 'FileName2_B' TO 'G:\Data\File2_B.ndf'
3. . RESTORE LOG PGM
FROM DISK = 'D:\Test2_Log.bak'
WITH RECOVERY
After restoring the backup filegroup to Test1 database, I'm not able to query the table that resides in the filegroup that was restored. Error returned is:
Msg 8653, Level 16, State 1, Line 2
The query processor is unable to produce a plan for the table or view 'Junk2' because the table resides in a filegroup which is not online.
We know, we are missing some pieces while creating the backup or restoring that is causing this problem. If somebody can point it out, or provide us a "Working Example"...
Thanks for your help.
Take a full backup
BACKUP DATABASE _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
GO
Or take File group backups
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'PRIMARY'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_PRIMARY.bak'
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_A'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_A.bak'
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_B'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_B.bak'
You must take a log backup
BACKUP LOG _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'
On the target database you have to first restore the Primary FG
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'PRIMARY'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE '_BIMAL_FG_TEST' to 'O:\SQLData\_BIMAL_FG_TEST_COPY.mdf'
, MOVE '_BIMAL_FG_TEST_log' to 'P:\SQLLog\_BIMAL_FG_TEST_COPY_log.LDF'
,STATS=20
,NORECOVERY, REPLACE, PARTIAL
Then Restore the FileGroup you want
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_A'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_1' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_1.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_2' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_2.ndf'
,STATS=20
,REPLACE
,NORECOVERY
And then restore the Log backup to complete the recovery
RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY
And if you want to restore another file group just followt the last two steps.
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_B'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_3' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_3.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_4' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_4.ndf'
,STATS=20
,NORECOVERY
RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'OTest_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY
This may not solve your problem but this will give you a working example you can play to see whether what you need is possible or not.
Restoring FileGroups...
We are currently in the process of designing a Consolidated System (data for multiple organizations stored in one database server) using SQL Server 2005. One of the design goal is to refresh individual company data from Production to "User Acceptance Server" without affecting other companies.
So, we thought of implementing this via Partitioning by Org. We need the "Proof of concept" that this can be done with a simple example. Since our DBA's are also new to SQL 2005, we are working together in achieving the goal. Ok, here is what we are trying to achieve:
1. Create a Database (Test1) with two file Groups (FileGroup1, FileGroup2) with two files in each file group.
2. Create two tables on this database, one on each FileGroup
Table 1 on FileGroup1, Table 2 on FileGroup2.
3. Populate data on these tables.
4. Create an exact copy of this database with the above architecture and call it Test2.
5. Populate Test2, with a different set of data.
6. Backup FileGroup2 on Test2 and restore it to Test1 database.
What we did:
1. I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file with:I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file using :
BACKUP LOG PGM TO DISK='D:\Test2_Log.bak' WITH NORECOVERY
2. Restore the database filegroup using:
RESTORE DATABASE Test1
FILEGROUP = 'FileGroup2'
FROM DISK ='D:\Test2.bak' WITH
MOVE 'FileName2_A' TO 'G:\Data\File2_A.ndf',
MOVE 'FileName2_B' TO 'G:\Data\File2_B.ndf'
3. . RESTORE LOG PGM
FROM DISK = 'D:\Test2_Log.bak'
WITH RECOVERY
After restoring the backup filegroup to Test1 database, I'm not able to query the table that resides in the filegroup that was restored. Error returned is:
Msg 8653, Level 16, State 1, Line 2
The query processor is unable to produce a plan for the table or view 'Junk2' because the table resides in a filegroup which is not online.
We know, we are missing some pieces while creating the backup or restoring that is causing this problem. If somebody can point it out, or provide us a "Working Example"...
Thanks for your help.
Take a full backup
BACKUP DATABASE _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
GO
Or take File group backups
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'PRIMARY'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_PRIMARY.bak'
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_A'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_A.bak'
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_B'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_B.bak'
You must take a log backup
BACKUP LOG _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'
On the target database you have to first restore the Primary FG
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'PRIMARY'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE '_BIMAL_FG_TEST' to 'O:\SQLData\_BIMAL_FG_TEST_COPY.mdf'
, MOVE '_BIMAL_FG_TEST_log' to 'P:\SQLLog\_BIMAL_FG_TEST_COPY_log.LDF'
,STATS=20
,NORECOVERY, REPLACE, PARTIAL
Then Restore the FileGroup you want
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_A'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_1' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_1.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_2' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_2.ndf'
,STATS=20
,REPLACE
,NORECOVERY
And then restore the Log backup to complete the recovery
RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY
And if you want to restore another file group just followt the last two steps.
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_B'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_3' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_3.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_4' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_4.ndf'
,STATS=20
,NORECOVERY
RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'OTest_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY
This may not solve your problem but this will give you a working example you can play to see whether what you need is possible or not.
Subscribe to:
Posts (Atom)