Geek asks and answers

How to back up a database with 100 rows in each table?

I want to back up a SQL Server database with all objects along with data, but the data in all tables should be limited, which is 100 rows for each table. I can do this in mysql very easily, but in SQL Server I do not know how to do this?

+5
source share
3 answers

You cannot use explicit BACKUP DATABASEfor this. However, you can do something similar, however keep in mind that, as in my commentary, this data will be limited if you rely on any kind of data integrity, since the ordering will be relatively arbitrary, and if all is not 1 : 1 and you get a magically convenient sorting for all requests, it will be just a massive data object:

CREATE DATABASE copy_of_original;
GO

USE original_db;
GO

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

-- this assumes all tables are in `dbo` schema:
SELECT @sql = @sql + CHAR(13) + CHAR(10)
  + 'SELECT TOP (100) * INTO copy_of_original.dbo.' 
  + QUOTENAME(name) + ' FROM dbo.' + QUOTENAME(name) + ';'
FROM sys.tables
WHERE schema_id = 1;

PRINT @sql;
-- EXEC sp_executesql @sql;

Once you do this, you can make a backup copy_of_original— but keep in mind that it will not have any indexes or restrictions present in the source database, and that it TOPwill select an arbitrary set of 100 rows from each table (or the whole table, for small tables with less than 100 rows).

+3
source

SQL Server "bcp"

: 100

0

  • , , 100 .

    sp_msforeachtable
    "ALTER TABLE? NOCHECK CONSTRAINT ALL ; [DUMMYTABLE] AS (SELECT RN = ROW_NUMBER() OVER (ORDER BY NEWID()), * FROM?)   [DUMMYTABLE] RN > 100  ALTER TABLE? '

  • .

: .

0

More articles:


All Articles