4 added 10 characters in body
source | link

Not directly, but here's a work-around

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE
    FROM DISK = '\PATH\To\Diff_backup.bak'
    WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

But before you copy data back into the Production DB, take a full backup of that DB. Just in case.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 )
Select col1, col2, col3
From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started. The Identity_Insert statements allow you to insert into an Identity column. Just be sure to get your WHERE clause right, so you don't insert bad data.

Not directly, but here's a work-around

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE
    FROM DISK = '\PATH\To\Diff_backup.bak'
    WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 )
Select col1, col2, col3
From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started.

Not directly, but here's a work-around

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE
    FROM DISK = '\PATH\To\Diff_backup.bak'
    WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

But before you copy data back into the Production DB, take a full backup of that DB. Just in case.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 )
Select col1, col2, col3
From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started. The Identity_Insert statements allow you to insert into an Identity column. Just be sure to get your WHERE clause right, so you don't insert bad data.

3 added 10 characters in body
source | link

Not really, but sort of...

Not directly, but here's a work-around

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE
    FROM DISK = '\PATH\To\Diff_backup.bak'
    WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 )
Select col1, col2, col3
From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started.

Not really, but sort of...

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE
    FROM DISK = '\PATH\To\Diff_backup.bak'
    WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 )
Select col1, col2, col3
From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started.

Not directly, but here's a work-around

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE
    FROM DISK = '\PATH\To\Diff_backup.bak'
    WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 )
Select col1, col2, col3
From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started.

2 Formatting
source | link

Not really, but sort of...

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE FROM DISK = '\PATH\To\Diff_backup.bak' WITH NORECOVERY;

RESTORE DATABASE DB1_RESTORE
    FROM DISK = '\PATH\To\Diff_backup.bak'
    WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 )  
Select col1, col2, col3  
From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started.

Not really, but sort of...

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE FROM DISK = '\PATH\To\Diff_backup.bak' WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 ) Select col1, col2, col3 From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started.

Not really, but sort of...

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE
    FROM DISK = '\PATH\To\Diff_backup.bak'
    WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 ) 
Select col1, col2, col3 
From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started.

1
source | link