Search

My notebook

Month

October 2016

Backup script for SQL express server

Create sql_backup.bat file in folder c:\scripts\sql_backup

(Scripts in folder c:\script\sql_backup\ backups in folder d:\sqlbackup\)

File text:

sqlcmd -i c:\scripts\sql_backup\sql_backup.sql >  D:\SQLBACKUPS\reports\backuplog.txt

Create sql_backup.sql files in folder c:\scripts\sql_backup

File text:

DECLARE @basePath varchar(300); 
SET @basePath = N'D:\SQLBACKUPS\'; 

DECLARE @db sysname;
DECLARE user_db_cursor CURSOR FOR 
 SELECT name FROM sys.databases WHERE name NOT IN 
('master', 'model', 'msdb', 'tempdb') 
OPEN user_db_cursor 
FETCH NEXT FROM user_db_cursor 
INTO @db 

WHILE @@FETCH_STATUS = 0 
BEGIN 
 DECLARE @backupPath varchar(1000); 
 SET @backupPath = @basePath + @db + '\'; 

 DECLARE @backupName varchar(1000); 
 DECLARE @time datetime2; 
 SET @time = SYSDATETIME(); 
 SET @backupName = @db + '_backup_' + 
REPLACE(REPLACE(REPLACE(CONVERT(varchar, @time, 20),'-','_'),':',''),' ','_') 
 + '_' + CAST(DATEPART(NANOSECOND, @time)/100 as varchar) 

 --Create sub-directory 
 EXECUTE master.dbo.xp_create_subdir @backupPath 
 
 DECLARE @backupFileName varchar(1000); 
 SET @backupFileName = @backupPath + @backupName + '.bak'; 
 
 --Backup database 
 BACKUP DATABASE @db TO DISK=@backupFileName 
 WITH NOFORMAT, NOINIT, NAME=@backupName , 
 SKIP, REWIND, NOUNLOAD, STATS=10 
 FETCH NEXT FROM user_db_cursor 
INTO @db 
END
CLOSE user_db_cursor 
DEALLOCATE user_db_cursor

Delete old backups:

Create

sqlcmd -i D:\SQLBACKUPS\Script\deleteold.sql > D:\SQLBACKUPS\reports\delete.txt
DECLARE @basePath varchar(300); 
SET @basePath = N'D:\SQLBACKUPS\'; 

DECLARE @time datetime; 
SET @time = DATEADD(DAY,-3, GETDATE() ); 

EXECUTE master.dbo.xp_delete_file 0,@basePath,N'bak',@time,1 

Export your IIS websites list to a xls (Excel)

run cmd as admin’

> %windir%\system32\inetsrv\appcmd list site > c:\sites.xls

Blog at WordPress.com.

Up ↑