Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MSSQL Dump

Posted on 2014-02-27
6
612 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 83

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 83

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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