Search This Blog

Loading...

Saturday, March 16, 2013

[T-SQL] Backup large database to multiple files

[T-SQL] Backup large database to multiple files


ABC123DEF456'2, '2ColABC2ColDef2Colxyz'Tried few combinations using FOR XML, but could not get the desired result at each row level.

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

Custom sp_who

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

SQL Query Help using XML

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, '

No comments:

Post a Comment