xst's post in DB Backup / Restore mit Datum im DB Namen wurde als beste Lösung markiert.
Hi
Hier mal meine Lösung dazu
/* SQL DB Restore DB_YearMonth-1 */
USE [master]
DECLARE @CurrentDate VARCHAR(30)
SET @CurrentDate = CONVERT(nvarchar(6), DATEADD(month, -1, GETDATE()), 112)
Declare @DBName VARCHAR(30)
SET @DBName = 'DB_'
Declare @RestoreName VARCHAR(30)
SET @RestoreName = @DBName + @CurrentDate
DECLARE @DATA NVARCHAR(MAX) = 'L:\MSSQL\' + @RestoreName + '.mdf'
DECLARE @LOG NVARCHAR(MAX) = 'H:\MSSQL\' + @RestoreName + '.ldf'
RESTORE DATABASE @RestoreName
FROM DISK = N'X:\Backup\DB.bak' WITH FILE = 1,
MOVE N'DB_' TO @DATA,
MOVE N'DB_log' TO @LOG,
NOUNLOAD, STATS = 5
GO
und hier noch der drop
/* SQl DB drop DB_YearMonth-5 */
USE [master]
GO
DECLARE @DropDate VARCHAR(30)
SET @DropDate = CONVERT(nvarchar(6), DATEADD(month, -5, GETDATE()), 112)
Declare @DBName VARCHAR(30)
SET @DBName = 'DB_'
Declare @DropName VARCHAR(30)
SET @DropName = @DBName + @DropDate
SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND database_id = DB_ID('@DropName')
EXEC ('DROP DATABASE ' + @DropName)
GO
