SQL Server Performance Forum – Threads Archive

Selective Database backup in SQL Server

Hi : I need to know whether the following is possible and if so, then how do I go about doing it ? I need to take a backup of a SQL Server database, of all tables, but not the whole data of each table. I would like to give a query for retrieving selective rows for each table.
Ultimately I would need the backup as a normal .bak file so that I restore it to another SQL Server database.

Not possible with BACKUP statement, but possible with the help of DTS package.
Also if you can keep those selective tables on a filegroup, you can achieve by performing filegroup backups. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Even if you are able to back up selective data from all the tables, this won’t gaurantee that all your data will be consistent. So even if you are able to back it up as single .bak file (which can only be done by moving selected data to new database and taking its backup) you will always have the risk of having inconsistent data which does not gaurantee to follow all your constraints and business rules. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I think this doesn’t apply in case of using static tables and dealing with filegroup backups? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

quote:Originally posted by nilayinc Hi : I need to know whether the following is possible and if so, then how do I go about doing it ? I need to take a backup of a SQL Server database, of all tables, but not the whole data of each table. I would like to give a query for retrieving selective rows for each table.
Ultimately I would need the backup as a normal .bak file so that I restore it to another SQL Server database.

The comment came from the fact that nilayinc mentioned about being able to restore the backup to another database. I assumed this meant something like being able to work on partial data from one database. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I agree and to accomplish this task DTS would help anyway, though its not 100% agreeable to keep data consistent.[8D] _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

For consistency, no partial backup (or a DTS transfer of selected database objects) method would suffice or even be advised against. It all depends on the design of the database and the actual structure of the underlying data. So, and in agreement with the DTS strategy, create a package to transfer your data to a different database and then BACKUP that new database. You can schedule this package if you so wish. Nathan H.O.
Moderator
SQL-Server-Performance.com

]]>