CODEWITHSUNDEEP
sql-server
ghiboz
ghiboz

Reputation: 7993

MSSQL Script to export entire database

It's possible create a script (to launch from a stored procedure) that export the entire database (schema + data) into a .sql file?

I need this to make a backup from a MSSQL 2019 and I need to restore to MSSQL 2017... Using Tasks->Generate Scripts from SSMS it's possible do what I need, but I wish schedule this and the best way I think is made a script to launch from a stored procedure.

Thanks in advance!

Upvotes: 0

Views: 149

Answers (1)

Vinoth .R
Vinoth .R

Reputation: 161

Here is the script that will allow you to backup each database within your instance of SQL Server

DECLARE @name NVARCHAR(256) -- database name  
DECLARE @path NVARCHAR(512) -- path for backup files  
DECLARE @fileName NVARCHAR(512) -- filename for backup  
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = 'C:\test\'  
 
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name = @name 
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.sql'  
   BACKUP DATABASE @name TO DISK = @fileName  
 
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

You have to change the @path to the appropriate backup directory.

NOTE: Make sure that you have read and write access to the DB

Upvotes: 1

Related Questions