Create SQL Script to create SQL Script to backup databases and to restore databases.

- Fix owner user : 'sa'

- Shrink databases

Tip: You can use a shared folder;

Tip: You can disconnect all users before to restore a database;

Tip: You can add this SQL Script to protect executing on the wrong SQL Server;

IF (NOT(@@servername = 'MyServerName'))
BEGIN
   PRINT 'You MUST execute over MyServerName';
   RETURN;
END

Lire la suite de Create SQL Script to Backup/Restore databases

go
-- Last SQL Server instance restart.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
go
-- Last SQL Agent Server restart.
SELECT login_time as 'sqlserver_agent_start_time' FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher';
go
-- Last DB windows server restart.
exec xp_cmdshell 'systeminfo | find "Time:"';
go

We can spawn a Windows command shell and passes in a string for execution. Any output is returned as rows of text. (msdn)
ex:

xp_cmdshell 'dir C:\'

Set configuration

-- --------------------------------
-- Set Configuration
-- --------------------------------
--

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
--

but, we can do more...

Lire la suite de xp_cmdshell

This article is nice ( we have a little bug ) , and the SQL script is not execute-safe (if you run twice, you have errors ).

It is for a specific database, when you need to save all SQL Server, you must run for each database.

Lire la suite de How to Script User and Role Object Permissions in SQL Server for Database

Script to create script to copy data from two datatable when tables are equal. This code is very useful when partially we save data.We can delete data after some date but we must have a column date.

Lire la suite de Script to create script to copy datatable between two database