I need to full back up my center database. But I wanna a back up which does not include data of some tables. These tables should locate on back up but their data should not. If somebody could share sample query of this request, I would be grateful.
Asked
Active
Viewed 451 times
0
-
2This is not possible. SQL Server backup is on database level or on file-group level. There is no way you can back up (or exclude) a data file or specific tables. – FLICKER Dec 28 '18 at 07:20
-
How can I back up file group level by excluding a table. Could you write an example query sequence for me please. I have never file group backup so far. Appreciated, thanks a lot – guraym Dec 28 '18 at 07:46
-
1Possible duplicate of [MSSQL DataBase Backup without a specific table](https://stackoverflow.com/questions/33810481/mssql-database-backup-without-a-specific-table) – Richardissimo Dec 28 '18 at 07:56
-
am I gonna be able to have everything which is related with my original center database like indexes, triggers, stored procedures etc. in back up if I follow this method? I don't want to lose anything about my database. I need everything except data of specific 2 tables @Richardissimo – guraym Dec 28 '18 at 14:10
-
@guraym, you can move the tables you don't want to backup to a different file-group. this way when you backup file-group, those tables will not be included. to see how you can backup a file-group, see sql documentation. – FLICKER Dec 30 '18 at 04:53
2 Answers
0
You can't exclude a table from a backup. That is kind of defines a backup, the whole thing, not part of it. You can however find some ways around this. One way you could do this is by creating a second database and copying all the data from your database except that table to this database and backup the copy. You could use replication to copy your data and exclude the table and backup the replicated database. The problem is that either of these might actually take longer due to the quantity of data in the rest of the database.
Taken from here
Suraj Kumar
- 5,547
- 8
- 20
- 42
-
1Well done for finding the other question; but the correct thing to do is to mark this question as a duplicate of that other question, rather than offer the information from the other question as an answer. – Richardissimo Dec 28 '18 at 07:59
-
0
There is another alternative, you can create a second DB, let's call NonBCKDB and move the tables you don't need backup from MainDB to NonBCKDB. Then, you can create synonyms on MainDB to NonBCKDB tables:
CREATE SYNONYM tbl1 FOR NonBCKDB.dbo.tbl1;
Obviously you will only Back-Up MainDB.
Angel M.
- 1,360
- 8
- 17
-
If my suggestion did work, please, consider to mark it as "the reply". Thanks – Angel M. Dec 28 '18 at 20:44