This article shows how to create a folder for backup. The idea is to create folder with date part in its name, and then backup a specific database to that folder.
First we need to create a parameters to save our SQL command in it.
use master
declare @sql1 nvarchar(max)
declare @sql2 nvarchar(max)
declare @var1 varchar(100)
declare @var2 varchar(100)
–So first we create a folder, manually, called BACKUP. In this folder we will save our subfolders and backups.
set @var1 = N’"C:\BACKUP\SQLDB ‘+(select convert(varchar,getdate(),104))+’"’ –(CONVERT turns the date into dd.MM.yyyy format)
–save to @sql1 statement you wish to execute
set @sql1 = N’exec xp_create_subdir ‘+@var1
–next, we have to crate a variable which will contain path and name of a backup file.
set @var2 = N’C:\BACKUP\SQLDB ‘+(select convert(varchar,getdate(),104))+’\Database_’+convert(varchar,getdate(),112)+’.bkp’
–now, create @sql2 statement you wish to execute
set @sql2 =N’BACKUP DATABASE Database TO DISK = ‘ + QUOTENAME( @var2 , ”” )+’ WITH FORMAT, stats;’ –@var2 replaces path and filename of backup
Now execute both @sql1 and @sql2 statements
EXEC sp_executesql @sql1
EXEC sp_executesql @sql2
You can also export query result to file with small program called BCP.
To call BCP from SQL Query you need to use a function called xp_cmdshell. But to use this function, it must be enabled in SQL Server Advanced options.
The quickest way is to write TSQL for it
— Set advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
GO
— Update the change
RECONFIGURE
GO
— To enable the xp_cmdshell feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO
Now we can export it with the following statement:
exec master..xp_cmdshell ‘bcp "select top 500 * from sysobjects" queryout "c:\id\tempexportfile.txt" -c -t, -T -S ‘+@@servername
path to file – is referenced to server side path
– C use default code page for char, varchar or text columns
-t use TAB as field terminator
-T use trusted connection to server
-S use server –S servername