Solved

MSSQL Dump

Posted on 2014-02-27
6
576 Views
Last Modified: 2014-03-04
Hi Experts

Does MS SQL have a dump all databases, similar to the following in MySQL:

mysqldump --opt --all-databases -r c:\mysql_dump.txt -u root -p123

Open in new window


Thank you
0
Comment
Question by:APD_Toronto
  • 4
  • 2
6 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39893496
0
 

Author Comment

by:APD_Toronto
ID: 39893510
But, here you specify the db name.  My goal is for ALL databases (present & future)
0
 

Accepted Solution

by:
APD_Toronto earned 0 total points
ID: 39893532
I'm trying the following, it ran without errors, but the .bak files do not exist

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
 
-- specify database backup directory
SET @path = 'D:\DBs_Backups'  
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  
 
       FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:APD_Toronto
ID: 39893539
got it, was missing my \ in my path.

Final step... I saved the above as backup.sql, now how can I execute it from a batch file? I think I need to use sqlcmd but how do I specify the script file, user, password?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39893561
I don't know.  Click on "Request Attention" above to get more people to look at your question.
0
 

Author Closing Comment

by:APD_Toronto
ID: 39902820
figured out o my own.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CREATE DATABASE ENCRYPTION KEY 1 44
php image upload 3 27
sqlseverexpress 2008 agent xps question 1 11
SQL Maintenance Plan 3 16
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now