{"id":503,"date":"2020-11-07T19:06:36","date_gmt":"2020-11-07T18:06:36","guid":{"rendered":"https:\/\/www.delixirpro.com\/blog\/?p=503"},"modified":"2025-12-15T10:47:22","modified_gmt":"2025-12-15T09:47:22","slug":"script-de-sauvegarde-des-bases-sql-server-express-en-ligne-de-commande","status":"publish","type":"post","link":"https:\/\/www.delixirpro.com\/blog\/2020\/11\/07\/script-de-sauvegarde-des-bases-sql-server-express-en-ligne-de-commande\/","title":{"rendered":"Script de sauvegarde des bases SQL Server\/Express en ligne de commande"},"content":{"rendered":"\n<p>Si vous avez besoin de sauvegarder des bases SQL Express ou SQL Server en dehors de SQL Server Management Studio (SSMS), vous pouvez utiliser ce script. A planifier pour un lancement journalis\u00e9.<\/p>\n\n\n\n<p>A configurer :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Instance \u00e0 sauvegarder (variable SRV)<\/li>\n\n\n\n<li>Dossier de sauvegarde de destination (variable BCKFOLDER)<\/li>\n\n\n\n<li>Nombre de jour de r\u00e9tention (copie \u00e0 conserver localement) (variable RETENTION)<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>@ECHO OFF\nSETLOCAL\n\nSET SRV=localhost\\EBP\nSET BCKFOLDER=C:\\Backup\\SQL\nSET RETENTION=5\n\nFOR \/F \"tokens=1,2,3,4 delims=\/ \" %%A IN ('Date \/T') DO SET NowDate=%%A-%%B-%%C\n\necho Build a list of databases to backup\nSET DBList=%SystemDrive%SQLDBList.txt\nSqlCmd -E -S %SRV% -h-1 -W -Q \"SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE &#91;Name] NOT IN ('master','model','msdb','tempdb')\" &gt; \"%DBList%\"\n\necho Backup each database, prepending the date to the filename\nFOR \/F \"tokens=*\" %%I IN (%DBList%) DO (\nECHO Backing up database: %%I\nSqlCmd -E -S %SRV% -Q \"BACKUP DATABASE &#91;%%I] TO Disk='%BCKFOLDER%\\%NowDate%_%%I.bak'\"\nECHO.\n)\n\necho Clean up the temp file\nIF EXIST \"%DBList%\" DEL \/F \/Q \"%DBList%\"\n\necho Clean old backup files\nForFiles \/p \"%BCKFOLDER%\" \/s \/d -%RETENTION% \/c \"cmd \/c del @file\"\n\nENDLOCAL\n<\/code><\/pre>\n\n\n\n<p>Si vous rencontrez des soucis, assurez vous que votre utilisateur soit bien sysadmin :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SqlCmd -E -S &lt;nomduserveur> -Q \u00ab\u00a0SELECT SUSER_SNAME() as LoginActuel\u00a0\u00bb\n<ul class=\"wp-block-list\">\n<li>Vous permet de savoir que est l&rsquo;utilisateur utilis\u00e9<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>SqlCmd -E -S &lt;nomduserveur> -Q \u00ab\u00a0SELECT IS_SRVROLEMEMBER(&lsquo;sysadmin)\u00a0\u00bb\n<ul class=\"wp-block-list\">\n<li>Vous permet de savoir si l&rsquo;utilisateur actuel est bien sysadmin (1 = oui, 0 = non)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Si vous avez besoin de sauvegarder des bases SQL Express ou SQL Server en dehors de SQL Server Management Studio (SSMS), vous pouvez utiliser ce script. A planifier pour un lancement journalis\u00e9. A configurer&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":504,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24,8],"tags":[25,42],"class_list":["post-503","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sauvegarde","category-tech-tips","tag-backup","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/posts\/503","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/comments?post=503"}],"version-history":[{"count":3,"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/posts\/503\/revisions"}],"predecessor-version":[{"id":1970,"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/posts\/503\/revisions\/1970"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/media\/504"}],"wp:attachment":[{"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/media?parent=503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/categories?post=503"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.delixirpro.com\/blog\/wp-json\/wp\/v2\/tags?post=503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}