CREATE Procedure [dbo].[Test_Daily_Backup]
As
DECLARE @name VARCHAR(50); -- Database name
DECLARE @path VARCHAR(256); -- Path for backup files
DECLARE @fileName VARCHAR(256); -- Filename for backup
DECLARE @fileDate VARCHAR(20); -- Used for file name
DECLARE @DeleteDate DATETIME = DATEADD(day, -3, GETDATE()); -- Cutoff date
-- Path to backups.
SET @path = 'C:\Temp\SQLBackup\';
-- Get date to include in file name.
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);
-- Dynamically get each database on the server.
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.sys.databases WHERE name IN ('test');
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
-- Loop through the list to backup each database.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build the path and file name.
SET @fileName = @path + @name + '_' + @fileDate + '.bak';
-- Backup the database.
BACKUP DATABASE @name TO DISK = @fileName WITH INIT;
-- Loop to the next database.
FETCH NEXT FROM db_cursor INTO @name;
END
-- Purge old backup files from disk.
EXEC master.sys.xp_delete_file 0,@path,'bak',@DeleteDate,0;
/*
0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
@path -- folder path (trailing slash)
'bak' -- file extension which needs to be deleted (no dot)
@DeleteDate -- date prior which to delete
0 -- subfolder flag (1 = include files in first subfolder level, 0 = not)
*/
-- Clean up.
CLOSE db_cursor;
DEALLOCATE db_cursor;
CREATE Procedure [dbo].[TestSync_Daily_Restore]
As
CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150), request_id INT)
INSERT INTO #TmpWho
EXEC sp_who
DECLARE @spid INT
DECLARE @tString varchar(15)
DECLARE @getspid CURSOR
SET @getspid = CURSOR FOR
SELECT spid
FROM #TmpWho
WHERE dbname IN ('test') OPEN @getspid
FETCH NEXT FROM @getspid INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))
EXEC(@tString)
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid
DROP TABLE #TmpWho
declare @filename varchar(256)
set @filename='C:\Temp\SQLBackup\test_'+ CONVERT(VARCHAR(20),GETDATE(),112) + '.bak';
RESTORE DATABASE TestSync FROM DISK = @filename
WITH File=1,MOVE N'test' TO N'C:\Temp\SQLRestore\TestSync.mdf', MOVE N'test_log' TO N'C:\Temp\SQLRestore\TestSync.ldf', NOUNLOAD, REPLACE, STATS = 1
--REPLACE, STATS = 10
