Use the following to check the current range for the partition. Example: Extend existing partition from 2018 to 2019. Add additional file group first, then modify partition schema and function. No need to modify partition table itself if the table is already setup to use partition. Step 1: Add Additional File Group Step 2: Add... » read more
REPLACE Option Impact REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set,... » read more
A Non-clustered index is built using the same 8K-page B-tree structure that is used to build a Clustered index, except that the data and the Non-clustered index are stored separately. A Non-clustered index is different from a Clustered index in that, the underlying table rows will not be stored and sorted based on the Non-clustered... » read more
SELECT DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1) Note: Always use date type for partition column. Make sure the following are created for partition… Partition File Group (Folder structure has been created as well) Partition Function Partition Schema Partition Table Note: If the database is part of Always On, make sure the folder structure has been created on the secondary servers... » read more
sp_refreshview Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends. Run the following SP to refresh all views in the database, after there was database object modifications. Sources: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refreshview-transact-sql?view=sql-server-ver15
Go to … SSMS -> Specific Database Server -> SQL Server Agent -> Jobs -> Specific Job -> Script Job as Note: Make sure to change the schedule quid id if you replicate from a previous job.
Tips for moving large backup file. Create multiple backup files instead of one large backup file. Use file compression programs like WinZip to compress backup file. Consider trimming the data in the database before creating the backup file. Create database and data from scripts. 7zip You can also use software like 7zip to compress and... » read more