July 14, 2011 at 1:34 pm
Is there a way that in SSMS that I can generate a backup script that has the file names like databasename_Backup_year_month_day_time... just like the file name of the backup that generated through maintence plan.
Thanks
July 14, 2011 at 1:55 pm
you can use xp_cmdshell(not recommended on prod) and get the info through a query to build a script.
July 14, 2011 at 2:05 pm
If I understand what you are looking for correctly, eg "dbname_backup_yyyymmdd", then:
select 'dbname' + '_backup_' + replace(convert(varchar, GETDATE(),102),'.','')
If you want time as well, eg "dbname_backup_yyyymmddhhmmss", then:
select 'dbname' + '_backup_' + replace(convert(varchar, GETDATE(),102),'.','')+
REPLACE(convert(varchar, getdate(),108),':','')
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 14, 2011 at 2:10 pm
This should get you started
SET NOCOUNT ON
--Check if the temp table exists, if it does cleanup!
IF (object_id('tempdb..#sysdbs')) IS NOT NULL
BEGIN
DROP TABLE #sysdbs
END
--Create the temp table
CREATE TABLE #sysdbs(id INT, db SYSNAME, bac BIT DEFAULT 0)
--Insert the database details into the temp table
INSERT INTO #sysdbs (id, db)
SELECT database_id, name FROM sys.databases WHERE database_id <= 4 and name <> 'tempdb'
DECLARE @dbid int
DECLARE @sql NVARCHAR(max)
DECLARE @MINS NVARCHAR(2)
DECLARE @HRS NVARCHAR(2)
SET @MINS = CAST(DATEPART(MI, GETDATE()) AS NVARCHAR(2))
SET @HRS = CAST(DATEPART(HH, GETDATE()) AS NVARCHAR(2))
IF @MINS <= 9
BEGIN
SET @MINS = '0' + @MINS
END
IF @HRS <= 9
BEGIN
SET @HRS = '0' + @HRS
END
--Build and execute the backup statements
WHILE (SELECT count(*) FROM #sysdbs WHERE bac = 0) > 0
BEGIN
SELECT TOP 1 @dbid = id FROM #sysdbs WHERE bac = 0
SELECT @sql = 'BACKUP_DATABASE ' + QUOTENAME(DB_NAME(@dbid)) +
' TO DISK = N''M:\MSSQL\Backup\' + DB_NAME(@dbid) +
'\' + DB_NAME(@dbid) + '_' + CONVERT(NVARCHAR(20), GETDATE(), 112) + '_' + @HRS + @MINS +
'_Full.bak'' WITH INIT'
EXECUTE SP_EXECUTESQL @sql
UPDATE #sysdbs SET bac = 1 WHERE db = db_name(@dbid)
END
DROP TABLE #sysdbs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 14, 2011 at 2:29 pm
Thanks all. I will try the scripts.
I also am curious when bopeavy said to use 'you can use xp_cmdshell(not recommended on prod) and get the info through a query to build a script.
What does it mean, can you explain a little bit in detail how to do it? I can certainly try on my local devlopement box.
July 14, 2011 at 2:55 pm
Your best route are the scripts above!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 14, 2011 at 7:52 pm
I thought you were talking more about restore. I have worked some where before where we had to do some consistent restoring. So i used xp_cmdshell dir to make a list for restore and then use RESTORE VERIFYONLY to get names of files so that I had a dynamic way of restoring multiple dbs on what ever server.
July 15, 2011 at 10:12 am
bopeavy (7/14/2011)
I thought you were talking more about restore. I have worked some where before where we had to do some consistent restoring. So i used xp_cmdshell dir to make a list for restore and then use RESTORE VERIFYONLY to get names of files so that I had a dynamic way of restoring multiple dbs on what ever server.
You might have some luck looking at the MSDB table backupset. It contains the name and location of every backup file done. To the best of my knowledge you would have to use xp_cmdshell if you are trying to confirm that the file still exists on the drive however.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 15, 2011 at 10:16 am
Kenneth (7-15-2011)
You might have some luck looking at the MSDB table backupset. It contains the name and location of every backup file done. To the best of my knowledge you would have to use xp_cmdshell if you are trying to confirm that the file still exists on the drive however.
I understand that but since it was not on the same server that would have been useless.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply