Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Database archiving strategy

A
Avante last edited by
I work on a payment system (a bit regulated area with requirements to store really old data for audits & customer requests).

We have SQL Server 2016 (but will move to 2019 soon).

System is 16 years old, and for its functioning old data is not needed (but needed for ocasional analytics queries done by close-to-business analysts on readonly replica).

When i first thought about some kind of cold storage of old data i though about backups. But backups have 2 drawbacks:
1. No backup will contain all data (so if i need data that is 3 yr old & 10 yr old i probably would need to restore two backups)
2. They are hard to use, since they need to be restored and it takes some time.
Our business guys are okay to remove old data from main DB Server but really want to have old data be easily accessible with single SQL query (e.g. slow machine with all data for all years).

Is this even possible? Some kind of a replication that ignores huge data deletions (not alll DELETE operations but specific DELETEs that clean up all data), or any other approach that would be easily mainteinable in long run

Thanks!

P.S. Already use SSD & legally can't use Strech Database / public cloud
Reply Quote 0
1 Reply Last reply
K
Kadyn last edited by

"Some kind of a replication that ignores huge data deletions".

You could use transactional replication and modify the replication properties of specific articles (tables) to ignores deletes, that way you can replicate and keep everything to a reporting or archive database.

There are lots of other things to consider with replication of course, but by design it can do that.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

A

Backup policy for Oracle 11g database in non-archive mode
SQL, Database Testing • backup • • Avante

2

0
Votes

2
Posts

0
Views

J

A partly online backup is certainly possible but switching to and back from archivelog mode needs downtime. As an alternative you could run in regular archivelog mode and trash the generated archived log files, until you run a backup. For the backup to be recoverable it is important that it has ALL archived log files that were generated during the backup. End the backup with a log switch and also include those archives in the backup. You must see this kind of backup as partially online. It does allow you to restore to the point in time where the backup completed, not to any other point in time. Very similar to regular offline backups, only, taken online. It saves downtime. Why do you want this? This is a lot of effort just to safe maybe 10G of archive storage per day. It also introduces an extra error source and many dba's won't be able to work with this and expect to have all archives. My advice would be to keep it regular online backups, with regular archived log backups and keep things simple.
Python - Best database for exponentially growing database?
SQL, Database Testing • python database rec languageomme • • Mystic

2

0
Votes

2
Posts

0
Views

D

Based on your comment of only storing a few thousand rows, you should be fine with almost any database product. Most relational databases only start to have trouble when they're many orders of magnitude bigger, like TB's of data. Keep in mind some free offerings, like SQL Server Express Edition do have size limits, but as you have outlined, you won't hit those. SQL Server Express Edition has a 10GB max data size, LINK: https://www.microsoft.com/en-us/sql-server/sql-server-2019-comparison#CP_StickyNav_1. Found the limits on your chosen platform, https://www.sqlite.org/limits.html Maximum Database Size Every database consists of one or more "pages". Within a single database, every page is the same size, but different database can have page sizes that are powers of two between 512 and 65536, inclusive. The maximum size of a database file is 2147483646 pages. At the maximum page size of 65536 bytes, this translates into a maximum database size of approximately 1.4e+14 bytes (140 terabytes, or 128 tebibytes, or 140,000 gigabytes or 128,000 gibibytes). This particular upper bound is untested since the developers do not have access to hardware capable of reaching this limit. However, tests do verify that SQLite behaves correctly and sanely when a database reaches the maximum file size of the underlying filesystem (which is usually much less than the maximum theoretical database size) and when a database is unable to grow due to disk space exhaustion. You should be just fine in terms of size. The stuff about dynamic data typing is certainly interesting, and something you should be aware of.
S

SQL Database size doesn't match with the total table sizes in the database
SQL, Database Testing • sql server 2005 size • • Saumya

2

0
Votes

2
Posts

0
Views

S

Depending on the RDBMS being used, it is possible that the large file size as compared to summed-up table sizes is caused by any or more than one of the following: Last database cleanup (in terms of table deletion, record deletion, etc) did not affect any disk reclamation by the OS/RDBMS Last database cleanup does not normally affect disk space reclamation by OS/RDBMS Indexes/constraints are being stored in other files. You won't see these when checking just table sizes. No shrinking has been performed, though that may also possibly end up as first and/or second in this list A possible miscalculation of table size retrieval (I just need to add this because it happens to some) and so the numbers don't tally And maybe a few others.. Hopefully the stackers can add I'm not saying these are definite items that impact your situation. I just mean that these may occur, and not just in your situation.. One other thing. It can also depend on the database settings for new disk allocation sizes. Say for example one allocation for a certain DB is 1GB. Once the usage exceeds that, another 1GB is added, regardless of whether or not you consumed all of it -- at the moment more allocation is required, it gets set.
R

Setting up cloud database for syncing to local SQLite database
SQL, Database Testing • sql server sqlite sql server 2014 amazon rds data sync languagehroniza • • Rossere

2

0
Votes

2
Posts

0
Views

S

While CDC is one useful methodology in SQL Server, it can also be a little performance heavy too depending on how much data and how frequently it changes. You might want to consider a 3rd party tool to manage your synchronization between SQL Server and SQLite, which can help minimize the performance and maintenance overhead of a homebrew solution. For example SQLiteSync by Amplifier is supposed to work well for this kind of use case (note I haven't used it myself though). https://ampliapps.com/sqlite-sync/
Assign db_owner to all users at once
SQL, Database Testing • sql server users • • Bogopo

2

0
Votes

2
Posts

0
Views

J

I would use two cursors. The first runs through all databases - except sys dbs. For each database the logins will be fetched and added to the db_owner role. If you have hundreds of dbs/ logins, I would write this as a db with some transaction handling and/or logging that if something crashes while executing you can continue on the point of crash. i couldn't test the code below, this should be an approach how to develop your own proc Use master GO DECLARE @dbname VARCHAR(50) DECLARE @SQL_statement varchar(max) DECLARE @chng_role NVARCHAR(MAX) DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL_statement = 'Declare users_cursor CURSOR FOR SELECT name FROM '+ @dbname +'.sys.database_principals where (type='''S''' or type = '''U''')' EXEC sp_executesql @sqlstatement OPEN users_cursor FETCH NEXT FROM users_cursor INTO @UserName WHILE @@FETCH_STATUS = 0 BEGIN SET @chng_role = 'use [' + @dbname + ']; exec sp_addrolemember @rolename = ''db_owner'', @membername = ''' + @userName + '''' EXECUTE sp_executesql @chng_role FETCH NEXT FROM users_cursor INTO @UserName END CLOSE users_cursor DEALLOCATE users_cursor FETCH NEXT FROM db_cursor INTO @dbname END CLOSE db_cursor DEALLOCATE db_cursor
Z

How to check values for records from different rows
SQL, Database Testing • sql server • • zymir

2

0
Votes

2
Posts

0
Views

D

I guess that what you would like is not exactly what you are asking for... so, correct me if I missinterpreted you. Assuming this is your data (I represent it with a table, but it can be a view): CREATE TABLE v ( ID varchar(9), LETTER_ID int, MEDICAL_ID int, SICKNESS_ID int, PRO_ID int, EDU_ID int, TRAINING_ID int, RECORD_ID int, NID int ) ; INSERT INTO v (ID, LETTER_ID, MEDICAL_ID, SICKNESS_ID, PRO_ID, EDU_ID, TRAINING_ID, RECORD_ID, NID) VALUES ('MI1390454', NULL, NULL, NULL, NULL, NULL, 47584, NULL, NULL), ('MI1390454', NULL, NULL, NULL, 4673735, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, 105419, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), ('MI1390033', 299080, NULL, NULL, NULL, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, 659652, NULL, NULL, NULL), ('MI1390033', NULL, 309609, NULL, NULL, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, 659650, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, 659654, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 161077), ('MI1390033', NULL, 309608, NULL, NULL, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, 659651, NULL, NULL, NULL), ('MI1390454', NULL, NULL, NULL, NULL, NULL, NULL, 154745, NULL), ('MI1390033', NULL, NULL, NULL, NULL, 659653, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), ('MI1390033', NULL, NULL, NULL, NULL, NULL, NULL, 94272, NULL), ('MI1390033', NULL, NULL, NULL, NULL, NULL, 44917, NULL, NULL), ('MI1390033', NULL, NULL, NULL, 105418, NULL, NULL, NULL, NULL) ; What you actually want is to flatten these data first. That is, GROUP all rows with the same ID, and take the only non null value of the other columns (or, if there are more than one, the one value that actually is of interest to you). I have chosen to do this using the MAX aggregate function. You may want another one, such as MIN, or a MIN || MAX or something like a string_agg or group_concat (which MS-SQL Server doesn't yet provide): SELECT ID, max(LETTER_ID) AS LETTER_ID, max(MEDICAL_ID) AS MEDICAL_ID, max(SICKNESS_ID) AS SICKNESS_ID, max(PRO_ID) AS PRO_ID, max(EDU_ID) AS EDU_ID, max(TRAINING_ID) AS TRAINING_ID, max(RECORD_ID) AS RECORD_ID, max(NID) AS NID FROM v GROUP BY ID ; This would be the intermediate result: ID | LETTER_ID | MEDICAL_ID | SICKNESS_ID | PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID | NID :-------- | --------: | ---------: | ----------: | ------: | -----: | ----------: | --------: | -----: MI1390033 | 299080 | 309609 | null | 105419 | 659654 | 44917 | 94272 | 161077 MI1390454 | null | null | null | 4673735 | null | 47584 | 154745 | null At this point, you can select only the rows with (Letter, pro, record, education) not being null: SELECT * FROM (SELECT ID, max(LETTER_ID) AS LETTER_ID, max(MEDICAL_ID) AS MEDICAL_ID, max(SICKNESS_ID) AS SICKNESS_ID, max(PRO_ID) AS PRO_ID, max(EDU_ID) AS EDU_ID, max(TRAINING_ID) AS TRAINING_ID, max(RECORD_ID) AS RECORD_ID, max(NID) AS NID FROM v GROUP BY ID ) AS flatten WHERE LETTER_ID is not null and PRO_ID is not null and RECORD_ID is not null and EDU_ID is not null ; You will get: ID | LETTER_ID | MEDICAL_ID | SICKNESS_ID | PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID | NID :-------- | --------: | ---------: | ----------: | -----: | -----: | ----------: | --------: | -----: MI1390033 | 299080 | 309609 | null | 105419 | 659654 | 44917 | 94272 | 161077 dbfiddle here If you're only interested in the ID value, but not the rest, you can use a different approach: SELECT DISTINCT ID FROM v AS v0 WHERE EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND LETTER_ID is not null) AND EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND PRO_ID is not null) AND EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND RECORD_ID is not null) AND EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND EDU_ID is not null) ; dbfiddle here
Can I try SQL Server BIG Data Cluster for free?
SQL, Database Testing • sql server azure clustering big data • • Demir

2

0
Votes

2
Posts

1
Views

T

As per Microsoft documentation, under SQL Server Editions: SQL Server Developer edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server. SQL Server Developer is an ideal choice for people who build and test applications. SQL Server Developer Edition supports all the same functionality that Enterprise Edition does, which is supported as a master instance for a big data cluster. According to deployment guidance from Microsoft, Developer Edition is the default deployed edition for a Big Data Cluster, and you upgrade to Enterprise for production systems after initial deployment: Big Data Cluster edition is determined by the edition of SQL Server master instance. At deployment time Developer edition is deployed by default. You can change the edition after deployment. See Configure SQL Server master instance.
R

Database mirroring is limited to an original database
SQL, Database Testing • sql server 2008 • • rosemadder

2

0
Votes

2
Posts

0
Views

E

Yes, database mirroring works from one principle server to one mirror server. Only one mirror copy can be used. For multiple readable replicas, consider AlwaysOn Availability Groups, introduced in SQL Server 2012 Enterprise Edition.
A

How to grant permission to view database audit logs to non-sysadmin login
SQL, Database Testing • sql server permissions audit • • authera

2

0
Votes

2
Posts

0
Views

B

Reference: sys.fn_get_audit_file (Transact-SQL) View a SQL Server Audit Log Microsoft recommends viewing the audit log by using the Log File Viewer. However, if you are creating an automated monitoring system, the information in the audit file can be read directly by using the sys.fn_get_audit_file (Transact-SQL) function. Reading the file directly returns data in a slightly different (unprocessed) format. See sys.fn_get_audit_file for more information. In order to use sys.fn_get_audit_file users requires the CONTROL SERVER permission. You can deny privilege on a principle who has been assigned CONTROL SERVER permission which is not possible in case of SYSADMIN. More about this: Security Questions: Difference between sysadmin and CONTROL SERVER Permission by Jason Strate.
E

Show comments table
Software Programming • sql server sql • • emran

2

0
Votes

2
Posts

0
Views

S

There are some ways to get the extended properties of a table. One is through the function https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-listextendedproperty-transact-sql :-- código #1 SELECT sys.objects.name as [Nome da tabela], EP.name as [Nome do comentário], EP.value as [Descrição do comentário] from sys.objects cross apply fn_listextendedproperty(default, 'SCHEMA', schema_name(schema_id), 'TABLE', name, null, null) as EP where sys.objects.name not in ('sysdiagrams'); The above code lists all extended properties. If you want you can limit to a specific property, added the filter in the WHERE clause.In the topic https://social.technet.microsoft.com/Forums/sqlserver/pt-BR/8f4d4710-75fd-4803-9e64-7e91ddd5b29e/consulta-para-selecionar-a-descrio-de-uma-coluna?forum=520 it is also used, but to get the description of the columns.
C

Column size with datatype decimal
SQL, Database Testing • sql server sql server 2008 datatypes • • courtlanda

2

0
Votes

2
Posts

0
Views

S

Don't mix storage (5 or 9 bytes) and what you get back In a decimal(19,5) column it is always 9 bytes on disk. So zero will take nine bytes. Your 10999.99999 is merely a representation of that stored number that is then parsed as 10999.99999 by DATALENGTH Edit: DATALENGTH will return the number of bytes needed to store the expression given. It ignores datatype mostly. So 10999.99999 is treated as decimal(10,5) which can be stored in 5 bytes not 9 as Martin pointed out in a comment. So a decimal(19,5) column is always 9 bytes. DATALENGTH doesn't care. It looks at the expression 10999.99999 and decides it can be stored in 5 bytes. But of course it can't Personally, I've never used DATALENGTH except to find trailing spaces or such. Basically, don't use DATALENGTH on non string datatypes. Returns the number of bytes used to represent any expression. DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data. Note: LEN tells you how long the string representation is DECLARE @i decimal(19,5) SET @i ='10999.99999' SELECT @i,LEN(@i),DATALENGTH(@i) SET @i ='-90999.99999' SELECT @i,LEN(@i),DATALENGTH(@i)
T

Trying to check if a string contains a number exclusively
SQL, Database Testing • sql server t sql sql server 2005 substring pattern matc languagehing • • terrea

2

0
Votes

2
Posts

0
Views

Perhaps you are over-complicating this by focusing too much on wanting a number. Take a step back for a moment. What you actually want is a substring without any digits on either side of it. The only way a number could be part of a larger number is to have at least 1 digit on either side of it, right? So as long as you only pass in numbers, then this definition should still produce numbers that do not have any digits on either side. With that in mind, we just need 3 PATINDEX predicates to cover the passed-in value being on the far left, on the far right, or in the middle. Try the following as it seems to work: GO CREATE PROCEDURE #TestFindRunner ( @Runner VARCHAR(10) ) AS SET NOCOUNT ON; DECLARE @Data TABLE ( [ID] INT NOT NULL PRIMARY KEY, [Runners] VARCHAR(50) NULL ); INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6'); INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5'); INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5'); INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45'); INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92'); INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6'); INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7'); INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63'); INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7'); INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL); INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6'); SELECT tmp.* FROM @Data tmp WHERE @Runner COLLATE Latin1_General_100_BIN2 = tmp.[Runners] OR PATINDEX('%[^0123456789]' + @Runner COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0 OR PATINDEX(@Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0 OR PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0 GO And then test with: EXEC #TestFindRunner 0; EXEC #TestFindRunner 2; EXEC #TestFindRunner 4; EXEC #TestFindRunner 8; EXEC #TestFindRunner 11; -- 0 rows EXEC #TestFindRunner 3; -- 4 EXEC #TestFindRunner 77; -- 3 EXEC #TestFindRunner 111; -- 5 -- 1 row EXEC #TestFindRunner 5; -- 2 and 3 -- 2 rows EXEC #TestFindRunner 1; -- 6, 7, 8, and 9 -- 4 rows EXEC #TestFindRunner 6; -- 1, 4, 6, 7, and 11 -- 5 rows EXEC #TestFindRunner 7; -- 1, 2, 6, 7, 8, and 9 -- 6 rows EXEC #TestFindRunner 9; -- 1, 2, 3, 6, 7, 8, and 9 -- 7 rows The reason for having 3 variations of the PATINDEX is that PATINDEX search patterns are not Regular Expressions (RegeEx), contrary to what many people say / think (same with LIKE patterns). PATINDEX and LIKE patterns do not have quantifiers, so it is not possible to specify that the [^0123456789] single character replacement should be "0 or more"; it is "one and only one; no more, no less". Forcing the binary collation (i.e. the COLLATE Latin1_General_100_BIN2 after each @Runner reference) ensures that we are only dealing with these 10 decimal digits and not any other character that might be considered equivalent To put the above logic into an Inline Table-Valued Function (TVF) so that it is easier to use (and more efficient than a similarly easy-to-use scalar UDF), try the following: USE [tempdb]; GO CREATE FUNCTION dbo.IsRunnerPresent ( @Runner VARCHAR(10), @Runners VARCHAR(8000) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT CONVERT(BIT, CASE WHEN @Runner COLLATE Latin1_General_100_BIN2 = @Runners OR PATINDEX('%[^0123456789]' + @Runner COLLATE Latin1_General_100_BIN2, @Runners) > 0 OR PATINDEX(@Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2, @Runners) > 0 OR PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2, @Runners) > 0 THEN 1 ELSE 0 END) AS [RunnerFound]; GO And then test with: DECLARE @Runner VARCHAR(10); SET @Runner = '6'; DECLARE @Data TABLE ( [ID] INT NOT NULL PRIMARY KEY, [Runners] VARCHAR(50) NULL ); INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6'); INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5'); INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5'); INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45'); INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92'); INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6'); INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7'); INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63'); INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7'); INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL); INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6'); SELECT tmp.[ID], tmp.[Runners], fnd.[RunnerFound] FROM @Data tmp CROSS APPLY dbo.IsRunnerPresentTVF(@Runner, tmp.[Runners]) fnd; Which returns: ID Runners RunnerFound 1 16+7+9+6 1 2 16+7+9+5 0 3 26+77+9+5 0 4 6+3+45 1 5 63,808,111,92 0 6 1-7-9,6 1 7 1-6-9,7 1 8 1-7-9,63 0 9 1-63-9,7 0 10 NULL 0 11 6 1
E

Restoring Database, creating new copy and use existing transaction logs for the original backup database to restore the NEW database to be current?
SQL, Database Testing • sql server restore • • emran

2

0
Votes

2
Posts

0
Views

That won't work. As soon as you recover the database after restore, that begins a new timeline for the database. If this is an on-going process, one possible solution is to get the team generating the backups to take a differential every day which will have fairly low impact on effort and system load. This is in addition to what they currently have. However, it will require some careful management of the files to ensure you have the right sequence of full backup, differential and tlogs. The benefit is, you will not have more than the 20 tlog files to apply if you ever need to restore the database to the latest version. You also don't lose existing PITR based on the 15-20 tlog backups taken daily (I'm assuming there is some PITR requirement given the number of log backups). If you want to further reduce the number of log files to apply, you can increase the number of differential backups per day but I'd wager the added complexity in managing the files far outweighs the benefits. If you script all your tasks (sounds like you are), execution and scrutinizing for issues is fairly easy to do. If you're using the SSMS GUI, well, you really shouldn't. ** To make it clear, this assumes you already reset the backup chain with a full database backup at some interval. Having just a single full DB backup and only taking differentials and tlog backups from there on is not a viable solution. In most situations, the differential will eventually grow to a large size, possibly bigger than the base (full DB) backup if you have lots of write activities. An example of a basic backup cycle might look like: - Full DB backup - every Sunday 2am - Differential DB backup - every day - 8pm Transaction log backup - every 15minutes This cycle has potential data loss of up to 15 minutes if there are no other protections (e.g. AlwaysOn) in place. If this is just a 1-time thing, i.e. you won't get any more tlog backups then you could reduce your recovery time when the goal is to get to the latest point-in-time available. Just restore the DB, differential and apply all the logs. Once done, take a backup of that and keep it along with all the other backup files you have. This buys you rapid recovery when you need to get to the latest version available since you only need to do a single DB restore. However, don't delete all the other backup files in case you need to restore to a specific point-in-time other than the most recent.
B

Migrate azure sql database to Azure SQL Database Managed Instance
SQL, Database Testing • azure sql database • • Burma

2

0
Votes

2
Posts

0
Views

S

Since the Managed Instance was in preview, the first option we have is to export your Azure SQL Database as a bacpac then import that bacpac to your Managed Instance. A better option we have now is using the Azure Data Migration service. It is not only faster than the method I described above, but it gives you the option to perform a data validation at end and it rebuilds all indexes on the database. Here you have a step-by-step tutorial.
Can we change the database code page after the database creation?
SQL, Database Testing • db2 db2 luw • • carriann

2

0
Votes

2
Posts

0
Views

You cannot change the database code set after creation.

Database archiving strategy

Suggested Topics

Backup policy for Oracle 11g database in non-archive mode SQL, Database Testing • backup • • Avante

Python - Best database for exponentially growing database? SQL, Database Testing • python database rec languageomme • • Mystic

SQL Database size doesn't match with the total table sizes in the database SQL, Database Testing • sql server 2005 size • • Saumya

Setting up cloud database for syncing to local SQLite database SQL, Database Testing • sql server sqlite sql server 2014 amazon rds data sync languagehroniza • • Rossere

Assign db_owner to all users at once SQL, Database Testing • sql server users • • Bogopo

How to check values for records from different rows SQL, Database Testing • sql server • • zymir

Can I try SQL Server BIG Data Cluster for free? SQL, Database Testing • sql server azure clustering big data • • Demir

Database mirroring is limited to an original database SQL, Database Testing • sql server 2008 • • rosemadder

How to grant permission to view database audit logs to non-sysadmin login SQL, Database Testing • sql server permissions audit • • authera

Show comments table Software Programming • sql server sql • • emran

Column size with datatype decimal SQL, Database Testing • sql server sql server 2008 datatypes • • courtlanda

Trying to check if a string contains a number exclusively SQL, Database Testing • sql server t sql sql server 2005 substring pattern matc languagehing • • terrea

Restoring Database, creating new copy and use existing transaction logs for the original backup database to restore the NEW database to be current? SQL, Database Testing • sql server restore • • emran

Migrate azure sql database to Azure SQL Database Managed Instance SQL, Database Testing • azure sql database • • Burma

Can we change the database code page after the database creation? SQL, Database Testing • db2 db2 luw • • carriann

Backup policy for Oracle 11g database in non-archive mode
SQL, Database Testing • backup • • Avante

Python - Best database for exponentially growing database?
SQL, Database Testing • python database rec languageomme • • Mystic

SQL Database size doesn't match with the total table sizes in the database
SQL, Database Testing • sql server 2005 size • • Saumya

Setting up cloud database for syncing to local SQLite database
SQL, Database Testing • sql server sqlite sql server 2014 amazon rds data sync languagehroniza • • Rossere

Assign db_owner to all users at once
SQL, Database Testing • sql server users • • Bogopo

How to check values for records from different rows
SQL, Database Testing • sql server • • zymir

Can I try SQL Server BIG Data Cluster for free?
SQL, Database Testing • sql server azure clustering big data • • Demir

Database mirroring is limited to an original database
SQL, Database Testing • sql server 2008 • • rosemadder

How to grant permission to view database audit logs to non-sysadmin login
SQL, Database Testing • sql server permissions audit • • authera

Show comments table
Software Programming • sql server sql • • emran

Column size with datatype decimal
SQL, Database Testing • sql server sql server 2008 datatypes • • courtlanda

Trying to check if a string contains a number exclusively
SQL, Database Testing • sql server t sql sql server 2005 substring pattern matc languagehing • • terrea

Restoring Database, creating new copy and use existing transaction logs for the original backup database to restore the NEW database to be current?
SQL, Database Testing • sql server restore • • emran

Migrate azure sql database to Azure SQL Database Managed Instance
SQL, Database Testing • azure sql database • • Burma

Can we change the database code page after the database creation?
SQL, Database Testing • db2 db2 luw • • carriann