[T-SQL] Backup large database to multiple files |
- Backup large database to multiple files
- Custom sp_who
- Column update based on expiration criteria
- SQL Query Help using XML
| Backup large database to multiple files Posted: 15 Mar 2013 06:13 AM PDT Hi everyone,I manage 100+ SQL servers and some of them contain over 120 – 800 GB + databases. I googled for backup script that backs up database depending on the size and strips the .bak file to multiple files. So far no luck. The closest one I got to is this script. Is there a way I can backup smaller databases in one .bak file and 60 + GB databases to multiple .bak (stripped) files dynamically in the same backup script? This way I can standardize it across all the servers. Thanks in advance or your help.Hi everyone,I manage 100+ SQL servers and some of them contain over 120 – 500 GB + databases. I googled for backup script that backs up database depending on the size and strips the .bak file to multiple files. So far no luck. The closest one I got to is this script. Is there a way I can backup smaller databases in one .bak file and 60 + GB databases to multiple .bak (stripped) files? Thanks in advance or your help.DECLARE @Baksql VARCHAR(8000) DECLARE @BackupFolder VARCHAR(100) DECLARE @BackupFile VARCHAR(100) DECLARE @BAK_PATH VARCHAR(4000) DEclare @BackupDate varchar(100) DEclare @Size varchar(100)-- Setting value of backup date and folder of the backup SET @BackupDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551 SET @BackupFolder = 'C:\temp\' SET @Baksql = '' -- Declaring cursor DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR SELECT NAME FROM SYS.DATABASES WHERE state_desc = 'ONLINE' -- Consider databases which are online AND database_id > 4 -- Exluding system databases -- Opening and fetching next values from sursor OPEN c_bakup FETCH NEXT FROM c_bakup INTO @BackupFile WHILE @@FETCH_STATUS = 0 BEGIN SET @BAK_PATH = @BackupFolder + @BackupFile -- Creating dynamic script for every databases backup SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'' WITH INIT;' -- Executing dynamic query PRINT (@Baksql) EXEC(@Baksql) -- Opening and fetching next values from sursor FETCH NEXT FROM c_bakup INTO @BackupFile END -- Closing and Deallocating cursor CLOSE c_bakup DEALLOCATE c_bakup |
| Posted: 15 Mar 2013 08:09 AM PDT Hello All,Couldn't find anything that addresses this specifically, so here goes.I have a group that I want to allow to view process on their dev server, but I don't want to grant view server state permissions and all that it exposes, so I'm trying to write a proc that will execute sp_who as me (the DBA) for users, but it still only returns one row instaed of all of them; what am I missing? MS's docs say this should work.USE [master]GO/****** Object: StoredProcedure [dbo].[usp_who] Script Date: 03/14/2013 14:57:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================-- Author: -- Created: 03/14/13-- Desc: sp_who for users-- =============================ALTER PROCEDURE [usp_who]with execute as selfASBEGIN SET NOCOUNT ON; exec sp_who;END |
| Column update based on expiration criteria Posted: 15 Mar 2013 06:52 AM PDT I am trying to get a homegrown database to automatically update a 'IsActive' column. I have a merge statement for the update, but I have no idea how to get this statement to run when the record is suppose to go from active to inactive and vice versa.My merge statement uses a view like this.[code="sql"]SELECT T1.SerialNumber, CASE WHEN (DATEADD(MONTH, T2.Schedule, T2.Date1) >= CAST(CURRENT_TIMESTAMP AS DATE)) THEN 1 WHEN (T2.Extension = 1 AND T2.DateExtended >= CAST(CURRENT_TIMESTAMP AS DATE)) THEN 1 WHEN (DATEADD(MONTH, T2.Schedule, T2.Date1) < CAST(CURRENT_TIMESTAMP AS DATE) AND (T2.DateExtended IS NULL OR T2.DateExtended < CAST(CURRENT_TIMESTAMP AS DATE))) THEN 0 ELSE CAST(NULL AS TINYINT) END AS DetermineIsActive, CAST (CURRENT_TIMESTAMP AS DATE) AS CurrentTimestampFROM dbo.Table1 AS T1 INNER JOIN dbo.Table2 AS T2 ON T1.SerialNumber = T2.SerialNumber[/code]My merge statement is[code="sql"] MERGE dbo.Table1 T1 USING (SELECT SerialNumber, DetermineIsActive, CurrentTimestamp FROM vIsActiveUpdate) AS vT2 ON T1.SerialNumber = vT2.SerialNumber WHEN MATCHED THEN UPDATE SET T1.IsActive = vT2.DetermineIsActive;[/code]This does the update I want, but I have no idea how to get it to execute when, for example T2.DateExtended < CURRENT_TIMESTAMP. I looked at triggers, but I don't think I want to do that. Everyone says not to use triggers, plus I can't figure out how the trigger would know when to execute. The criteria isn't really updating, it is expiring.Hopefully I was able to explain this clearly enough.Thanks |
| Posted: 15 Mar 2013 03:18 AM PDT Looking for SQL Query help on following.Have a key value pair, as below.declare @t table (id int, [key] varchar(10), value varchar(10))insert @t values (1, 'ColA', 'ABC123')insert @t values (1, 'colB', 'DEF456')insert @t values (2, 'colA', '2ColABC')insert @t values (2, 'colC', '2ColDef')insert @t values (2, 'colE', '2Colxyz')Need to bundle the Key-value combination into XML for each ID row. The expected results are1, ' |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment